How can I create an array of key,value pairs within a transformation?

Question

I have some source JSON files that contain {key:value} pairs, for example:

{firstName: "jason", lastName: "smith"}

I would like to take this JSON and create an array of key-value pairs as follows:

[{key: "firstName", value: "jason"},{key: "lastName", value: "smith"}]

I've seen the FROM_KEY_VALUE() function in the documentation, but what I want to do is the opposite of FROM_KEY_VALUE(). Do you have any ideas?

jhall-upsolver · Answer 1 · 2022-11-29T14:18:12.353

If the number of key value pairs are static, you can build two arrays, one of keys, and one of values. You can then ZIP_WITH_INDEX these arrays together to build your desired output. Using your example from the question. I've included some psuedo code below to show you how this works, in a real use case the keys_array[] and values_array[] would likely come from a staging table.

SELECT
   ZIP_WITH_INDEX('key,value',keys_array[],values_array[]) as output_array
FROM staging_table
LET keys_array[] = ['firstName','lastName'],
    values_array[] = ['jason','smith']
WHERE $commit_time BETWEEN RUN_START_TIME() and RUN_END_TIME();

The resulting array would be:

[{"index":0, "key":"firstName", "value":"jason"} ,{"index":1, "key":"lastName", "value":"smith"}]

If the fields in the two arrays are not static, you can use the ZIP function. The ZIP function will simply merge together any number of arrays, with automatically assigned field names.

SELECT
   ZIP(keys_array[],values_array[]) as output_array
FROM staging_table
LET keys_array[] = ['firstName','lastName'],
    values_array[] = ['jason','smith']
WHERE $commit_time BETWEEN RUN_START_TIME() and RUN_END_TIME();

The resulting array would look as follows:

[{"field0":"firstName","field1":"jason"} ,{"field0":"lastName","field1":"hall"}]

The field names could always be remapped if needed.

How can I create an array of key,value pairs within a transformation?

1 Answers1