CODEWITHSUNDEEP
sqlarraysjsonpostgresqllateral-join
Özenç B.
Özenç B.

Reputation: 1038

Iterate over each element in JSON array and join with rows

I am trying to write a SQL query that cross joins each row with its own JSON array elements. Let's say this is the data we have (I know, it doesn't make much sense):

| id | name | info                                            |
|----|------|-------------------------------------------------|
| 1  | john | [{score: 20, point: 10},{score: 25, point: 15}] |
| 2  | jane | [{score: 25, point: 15},{score: 35, point: 45}] |

What I am trying to get as an end result looks like this:

| id | name | score | point |
|----|------|-------|-------|
| 1  | john | 20    | 10    |
| 1  | john | 25    | 15    |
| 2  | jane | 25    | 15    |
| 2  | jane | 35    | 45    |

How can I write a query that does the job? I don't have the grants to create a new function so this has to be purely select statements, not plpgsql stuff.

Upvotes: 3

Views: 469

Answers (1)

GMB
GMB

Reputation: 222682

You can unnest with a lateral join and json[b]_array_elements:

select t.id, t.name, i.obj ->> 'score' as score, i.obj ->> 'point' as point
from mytable t
cross join lateral jsonb_array_elements(t.info) i(info)

Upvotes: 1

Related Questions