In my PieCloudDB Database I have a json column like this:
| json_col |
|---|
| {“id”: 1,”data”: [{“col1”: “A”,”col2″: “B”,”col3″: “C”}, {“col1”: “D”,”col2″: “E”,”col3″: “F”}]} |
| ‘{“id”: 2,”data”: [{“col1”: “G”,”col2″: “H”,”col3″: “I”}, {“col1”: “J”,”col2″: “K”,”col3″: “L”}]} |
I want to output it in table form with id and colx as columns.
I tried the following:
SELECT
json_col->>'id' AS id,
json_data.col1,
json_data.col2,
json_data.col3
FROM
c,
json_to_recordset(json_col->'data') AS json_data
(
col1 text,
col2 text,
col3 text
)
and got this:
| id | col1 | col2 | col3 |
|---|---|---|---|
| 1 | A | B | C |
| 1 | D | E | F |
| 2 | G | H | I |
| 2 | J | K | L |
But I want to get a result like this, grouped by id:
| id | col1 | col2 | col3 |
|---|---|---|---|
| 1 | A,D | B,E | C,F |
| 2 | G,J | H,K | I,L |
I found a solution(Using string_agg in PieCloudDB Database):
SELECT
json_col->>'id' AS id,
string_agg((json_data->>'col1'), ',') AS col1,
string_agg((json_data->>'col2'), ',') AS col2,
string_agg((json_data->>'col3'), ',') AS col3
FROM
c,
json_array_elements(json_col->'data') AS json_data
GROUP BY
id
ORDER BY
id;