1

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
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45
user19131919
  • 13
  • 3
  • 1
    Please clarify what you need. It appears that you're saying the values need to be one set per row based on the row they came from in the JSON. If that's the case, you can group by SEQ, which is an auto-generated column in the FLATTEN function. – Greg Pavlik May 17 '22 at 02:36
  • 1
    Did you tried adding 'group by seq', if that is something you are looking for? – Pankaj May 17 '22 at 02:36

1 Answers1

1

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"}
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45