how can I use lateral join to flatten the jsonb in postgres

Question

I have data like this which I need to flatten for each Id with the corresponding key and size with two different columns.

So I was watching the tutorial on snowflake which has this function

select distinct json.key as column_name, 
from raw.public.table_name, 
     lateral flatten(input => table_name) json

I was trying to find something in postgres query

id | json_data
 
1  | {"KEY": "mekq1232314342134434", "size": 0}
2  | {"KEY": "meksaq12323143421344", "size": 2}
3  | {"KEY": "meksaq12323324421344", "size": 3}

So I need two things here first I need a distinct key from these jsonb columns,

2. I need to flatten the jsonb columns

id | kEY | size
1  | mekq1232314342134434 | 0

@a_horse_with_no_name I pull from an API that stores a key like that. — Siddhant Singh, Oct 20 '22 at 07:06

score 0 · Accepted Answer · answered Oct 20 '22 at 07:09

Another option, beside ->>, would be to use jsonb_to_record:

with
  sample_data (id, json) as (values
    (1, '{"KEY": "mekq1232314342134434", "size": 0}' :: jsonb),
    (2, '{"KEY": "meksaq12323143421344", "size": 2}' :: jsonb),
    (3, '{"KEY": "meksaq12323324421344", "size": 3}' :: jsonb)
  )

  select
    id, "KEY", size
  from
    sample_data,
    lateral jsonb_to_record(sample_data.json) as ("KEY" text, size int);
    -- `lateral` is optional in this case

┌────┬──────────────────────┬──────┐
│ id │         KEY          │ size │
├────┼──────────────────────┼──────┤
│  1 │ mekq1232314342134434 │    0 │
│  2 │ meksaq12323143421344 │    2 │
│  3 │ meksaq12323324421344 │    3 │
└────┴──────────────────────┴──────┘
(3 rows)

hey. What if I need only a distinct key from the jsonb? – Siddhant Singh Oct 20 '22 at 07:16 — Siddhant Singh, Oct 20 '22 at 07:16

how can I use lateral join to flatten the jsonb in postgres

1 Answers1