I am new to JSON, in my PieCloudDB Database there is a json column as follows(example data):
{"A001": ["123","124","125"],"A002": ["234","235","236"]}
Now I want to output the result like this:
| my_key | my_value |
|---|---|
| A001 | 123 |
| A001 | 124 |
| A001 | 125 |
| A002 | 234 |
| A002 | 235 |
| A002 | 236 |
I tried this:
SELECT
json_object_keys(attributes) AS my_key,
attributes ->>json_object_keys(attributes) AS my_value
FROM
my_table;
But I got the result which is not what I expected:
| my_key | my_value |
|---|---|
| A001 | [“123″,”124″,”125”] |
| A002 | [“234″,”235″,”236”] |
I found functions that meets my requirements:
In PieCloudDB Database, I can use json_array_elements() to
split the elements in an array into rows and use json_each_text() to split key-value pairs into rows. Finally use trim() to remove double quotes.
SELECT
my_key,
trim(BOTH '"' FROM element::text) AS my_value
FROM
my_table,
json_each_text(attributes) AS key_value(my_key, my_value)
CROSS JOIN LATERAL
json_array_elements(key_value.my_value::json) AS element;