jsonpostgresqlplpgsqllongtable

how to convert json into long format table in postgresql/plpgsql?


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

Solution

  • If type of details column is json you can use json_each_text or if is jsonb you can use jsonb_each_text

    Demo

    select
      t.user_id,
      e.*
    from
      test t
      cross join json_each_text(t.details) e