Create new document per value in multi-value field

[dwhansen-cbg]

I have 40 columns in a pipeline that all store multiple values per row. They will all have the same number of values per row. They are delimited like:

7{C ;C ;C ;C ;MD;MD;MD}.

My questions are: 1) how do I break the above string into single 7 separate documents and 2) how do I combine all 40 multi-value columns into a individual documents?

[Ganesh]

Hi,
Can you please elaborate the requirement or share an example input and expected output formats for better clarity.

[dwhansen-cbg · Accepted solution]

One way I figured out to do this is use UNNEST(STRING_TO_ARRAY()) in an execute SQL snap.

UNNEST(STRING_TO_ARRAY(nested_multi_value_field)) as unnested_multi_value_field