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