Iterate over each element in JSON array and join with rows

Question

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.

GMB · Accepted Answer · 2020-12-24 14:07:21Z

1

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)

answered Dec 24, 2020 at 14:07

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Özenç B. Over a year ago

This is incredible. How can I read more about this technique? The stuff I found for lateral joins is very complicated and I couldn't get any of them to work, this one is so simple. I really want to learn what some of these mean, especially i(info) notation

GMB Over a year ago

@ÖzençB.: the documentatiion is a good place to start. See section "7.2.1.5. LATERAL Subqueries".

Collectives™ on Stack Overflow

Iterate over each element in JSON array and join with rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related