Data in JSON format to a table in POSTGRESQL

Question

Need help with json deserialization in postgresql. I would like this query

select data from panel

that returns:

                              Data
1 [{"type": "opened","user": "1"},{"type": "added","user":"2"}]
2 [{"type": "added","user": "3"}]
3 [{"type": "opened","user": "3"},{"type": "opened","user":"2"}]

instead returned a table:

type    user
opened  1
added   2
added   3
opened  3
opened  2

I don't quite understand how to represent the empty space between [ and {. I would appreciate any guidance as I have not found this particular example

user330315 · Accepted Answer · 2022-07-07 11:09:23Z

1

Use jsonb_array_elements to turn the arrays in to rows, then extract the keys:

select a.item ->> 'type' as type, 
       a.item ->> 'user' as "user"
from panel
  cross join jsonb_array_elements(data) as a(item);

This assumes that data is defined with the data type jsonb (which it should be). If it's not, you have to cast it: data::jsonb

answered Jul 7, 2022 at 11:09

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

Bergi Over a year ago

I'd prefer lateral instead of cross for clarity

user330315 Over a year ago

Well it is a cross join lateral

Bergi Over a year ago

Yes, implicitly, I'd just spell it out. And possibly use a second from item not a join at all, so from panel, lateral jsonb_array_elements(panel.data) as a(item)

user330315 Over a year ago

Well, I prefer to spell out the cross join explicitly rather than implicitly using the comma in the from clause (I never use a comma in the from clause)

Collectives™ on Stack Overflow

Data in JSON format to a table in POSTGRESQL

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related