2

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
  • 216,147
  • 25
  • 84
  • 135
Özenç B.
  • 928
  • 3
  • 8
  • 25

1 Answers1

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)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 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 – Özenç B. Dec 24 '20 at 14:28
  • 1
    @ÖzençB.: the [documentatiion](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-FROM) is a good place to start. See section "7.2.1.5. LATERAL Subqueries". – GMB Dec 24 '20 at 15:00