title: Snowflake - extract JSON array string object values into pipe separated values
body: <p>I have a nested JSON array which is a string object which has been stored into variant type stage table and I want to extract particular string object value and populate with pipe separated values if more than one object found. Can someone help me to achieve the desired output format please.</p>
  <p>Sample JSON data</p>
  <pre><code>    {&quot;issues&quot;: [
      {
      &quot;expand&quot;: &quot;&quot;,
      &quot;fields&quot;: {
          &quot;customfield_10010&quot;: [
          &quot;com.atlassian.xxx.yyy.yyyy.Sprint@xyz456[completeDate=2020-07-20T20:19:06.163Z,endDate=2020-07-17T21:48:00.000Z,goal=,id=1234,name=SPR-SPR 8,rapidViewId=239,sequence=1234,startDate=2020-06-27T21:48:00.000Z,state=CLOSED]&quot;,
          &quot;com.atlassian.xxx.yyy.yyyy.Sprint@abc123[completeDate=&lt;null&gt;,endDate=2020-08-07T20:33:00.000Z,goal=,id=1239,name=SPR-SPR 9,rapidViewId=239,sequence=1239,startDate=2020-07-20T20:33:26.364Z,state=ACTIVE]&quot;
          ],
          &quot;customfield_10011&quot;: &quot;obcd&quot;,
          &quot;customfield_10024&quot;: null,
          &quot;customfield_10034&quot;: null,
          &quot;customfield_10035&quot;: null,
          &quot;customfield_10037&quot;: null,
      },
      &quot;id&quot;: &quot;123456&quot;,
      &quot;key&quot;: &quot;SUE-1234&quot;,
      &quot;self&quot;: &quot;xyz&quot;
      }]}
  </code></pre>
  <p>I don't have any idea on how to separate the string objects inside an array with snowflake.
  By using the below query I can get whole string converted into pipe separated values.</p>
  <pre><code>select
      a.value:id::number as ISSUE_ID,
      a.value:key::varchar as ISSUE_KEY,
      array_to_string(a.value:fields.customfield_10010, '|') as CF_10010_Data
  from
      ABC.VARIANT_TABLE,
      lateral flatten( input =&gt; payload_json:issues) as a;
  </code></pre>
  <p>But I need to extract particular string object value. Say for example id value such as 1234 &amp; 1239 to be populated as pipe separated as shown below.</p>
  <pre><code>ISSUE_ID    ISSUE_KEY   SPRINT_ID
  123456      SUE-1234    1234|1239
  </code></pre>
  <p>Any idea on this to get desired result is much appreciated. Thanks..</p>