Not able to transform data in expected format in snowflake-CodePudding

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

CodePudding user response：

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"}