title: Snowflake - flatten multiple nested array values from json variant column
body: <p>I have a JSON data from which I wanted to extract the values of key 'text' with delimited in single row. Any help to achieve the desired output is much appreciated.</p>
  <p>Sample JSON data:</p>
  <pre><code>{
  &quot;expand&quot;: &quot;schema,names&quot;,
  &quot;issues&quot;: [
          {
              &quot;id&quot;: &quot;123456&quot;,
              &quot;key&quot;: &quot;XYZ-123&quot;,
              &quot;fields&quot;: {
                  &quot;customfield_10000&quot;: &quot;abcd&quot;,
                  &quot;customfield_10001&quot;: 7,
                  &quot;customfield_10002&quot;: null,
                  &quot;description&quot;: {
                              &quot;version&quot;: 1,
                              &quot;type&quot;: &quot;doc&quot;,
                              &quot;content&quot;: [
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 1&quot;
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 2&quot;
                                          },
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 3&quot;,
                                              &quot;marks&quot;: [
                                                  {
                                                      &quot;type&quot;: &quot;link&quot;,
                                                      &quot;attrs&quot;: {
                                                          &quot;href&quot;: &quot;some ref&quot;
                                                      }
                                                  }
                                              ]
                                          },
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 4&quot;
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;blockquote&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;paragraph&quot;,
                                              &quot;content&quot;: [
                                                  {
                                                      &quot;type&quot;: &quot;text&quot;,
                                                      &quot;text&quot;: &quot;some text value 5&quot;
                                                  }
                                              ]
                                          },
                                          {
                                              &quot;type&quot;: &quot;paragraph&quot;,
                                              &quot;content&quot;: [
                                                  {
                                                      &quot;type&quot;: &quot;inlineCard&quot;,
                                                      &quot;attrs&quot;: {
                                                          &quot;url&quot;: &quot;some url&quot;
                                                      }
                                                  },
                                                  {
                                                      &quot;type&quot;: &quot;text&quot;,
                                                      &quot;text&quot;: &quot;some text value 6&quot;
                                                  }
                                              ]
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 7&quot;
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 8&quot;
                                          },
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 9&quot;,
                                              &quot;marks&quot;: [
                                                  {
                                                      &quot;type&quot;: &quot;link&quot;,
                                                      &quot;attrs&quot;: {
                                                          &quot;href&quot;: &quot;some link&quot;
                                                      }
                                                  }
                                              ]
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 10&quot;
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 11&quot;
                                          }
                                      ]
                                  },
                                  {
                                      &quot;type&quot;: &quot;paragraph&quot;,
                                      &quot;content&quot;: [
                                          {
                                              &quot;type&quot;: &quot;text&quot;,
                                              &quot;text&quot;: &quot;some text value 12&quot;
                                          }
                                      ]
                              }
                          ]
                      }
                  }
              }
          ]
  }
  </code></pre>
  <p>Desired output:</p>
  <pre><code>ISSUE_ID ISSUE_KEY CF_10000 CF_10001 CF_10002   DESCRIPTION
  123456   XYZ-123   abcd     7        null       some text value 1|some text value 2|some text value 3.....
  </code></pre>
  <p>I'm using the below query to get the arrays values. However I wanted the key 'text' values from arrays to be populated as above desired format.</p>
  <pre><code>    select
         ISSUE.value:id::number as ISSUE_ID,
         ISSUE.value:key::varchar as ISSUE_KEY,
         ISSUE.value:fields.customfield_10000::varchar as CF_10000,
         ISSUE.value:fields.customfield_10001::number as CF_10001,
         ISSUE.value:fields.customfield_10002::varchar as CF_10002,
         ISSUE.value:fields.description.content::varchar as DESCRIPTION
      from
         VARIANT_TABLE,
         lateral flatten( input =&gt; payload_json:issues, outer =&gt; true) as ISSUE
  </code></pre>
  <p>I have an UDF created to extract JSON array object key value into string of array, but that doesn't helped me to get the desired output from above shared JSON as it has nested arrays inside objects.</p>
  <pre><code>create or replace function UDF_ARRAY_OBJECT_TO_STRING_ARRAY(a array, b varchar)
  returns array
  language javascript
  strict
  comment = 'UDF to extract JSON array object key value into string of array. A refers to input array and B refers to extract which key from the array object'
  as $$
  return A.map(function(d) {return d[B]});
  $$;
  </code></pre>