first of all apology for using image. however I can't seem to post my question as it seems I have issue in the formatting so I can't post my question, but I can't figure out whats wrong..
I have table like this table source
I would like to convert it to be structured like below
user_id | key | value |
---|---|---|
user_1 | a | 3 |
user_1 | b | 1 |
user_1 | c | 3 |
user_2 | c | 2 |
user_2 | e | 1 |
user_3 | a | 1 |
user_3 | f | 2 |
I used query below currently. but it has limit based on listed wtl_key a to e, whereas I can't list all the possible key as I don't know all the possible key. I want to query to catch all json key which could be aa,bb,zz, etc
with wide_to_long_key AS (
select unnest(array['a','b','c','d','e']) as wtl_key
)
, data AS (
select
user_id
,details::json->'a' as a
,details::json->'b' as b
,details::json->'c' as c
,details::json->'d' as d
,details::json->'e' as e
)
select
user_id
,wtl_key
,case
when wtl_key='a' THEN a
when wtl_key='b' THEN b
when wtl_key='c' THEN c
when wtl_key='d' THEN d
when wtl_key='e' THEN e
when wtl_key='a' THEN a
end as value
from
data
CROSS JOIN
wide_to_long_key
If type of details column is json you can use json_each_text
or if is jsonb you can use jsonb_each_text
select
t.user_id,
e.*
from
test t
cross join json_each_text(t.details) e