arraysjsonsnowflake-cloud-data-platformflattenlateral

Not able to transform data in expected format in snowflake


I got data in rows for a column like this

[
  {
    "value": "A",
    "path": "nth-child(1)"
  },
  {
    "value": "K",
    "path": "nth-child(2)"
  },
  {
    "value": "C",
    "path": "nth-child(3)"
  }
]

Need help ..... Want to get data like this format in rows from that column

   {
     "A",
     "K",
     "C",   
  },

Have tried like this : but it combine all the rows of the table

SELECT LISTAGG(f.value:value::STRING, ',') AS col
FROM tablename
,LATERAL FLATTEN(input => parse_json(column_name)) f 

Solution

  • I have used a CTE just to provide fake data for the example:

    WITH data(json) as (
        select parse_json(column1) from values
        ('[{"value":"A","path":"nth-child(1)"},{"value":"K","path":"nth-child(2)"},{"value":"C","path":"nth-child(3)"}]'),
        ('[{"value":"B","path":"nth-child(1)"},{"value":"L","path":"nth-child(2)"},{"value":"D","path":"nth-child(3)"}]'),
        ('[{"value":"C","path":"nth-child(1)"},{"value":"M","path":"nth-child(2)"},{"value":"E","path":"nth-child(3)"}]')
    )
    SELECT LISTAGG(f.value:value::text,',') as l1
    from data as d
       ,table(flatten(input=>d.json)) f
    group by f.seq
    order by f.seq;
    

    gives:

    L1
    A,K,C
    B,L,D
    C,M,E

    Thus with some string concatenation via ||

    SELECT '{' || LISTAGG('"' ||f.value:value::text|| '"' , ',') || '}' as l1
    from data as d
       ,table(flatten(input=>d.json)) f
    group by f.seq
    order by f.seq;
    

    gives:

    L1
    {"A","K","C"}
    {"B","L","D"}
    {"C","M","E"}