CODEWITHSUNDEEP
sqlpostgresqljoinjsonb
Crazy Cat
Crazy Cat

Reputation: 316

Postgres: How to join table with values from jsonb[] column

I have two tables as follows

accounts
 ------------------------------------------
|  ID  |           LOCATIONS               |
|------------------------------------------|
|  1   |  [{ "id" : 1}, { "id" : 3 }]      |
|------------------------------------------|
|  2   |             []                    |
 ------------------------------------------

regions
 ----------------------------
|  ID  | DATA               |
|---------------------------|
|  1   | {"name": "South"}  |
|---------------------------|
|  2   | {"name": "West"}   |
|---------------------------|
|  3   | {"name": "North"}  |
|---------------------------|
|  4   | {"name": "East"}   |
---------------------------

locations is of type jsonb[]

Now I wanted to get result as follows

 ------
| NAME |
|------|
| South|
|------|
| North|
 ------

Please help with the postgresql query to get this.

Upvotes: 1

Views: 5056

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

One option would be using JSONB_ARRAY_ELEMENTS() along with cross joins such as

SELECT r.data->>'name' AS "Name"
  FROM accounts AS a,
       regions AS r,
       JSONB_ARRAY_ELEMENTS(a.locations) AS l
 WHERE (value->>'id')::INT = r.id

Demo PS. if the data type of locations is JSON rather than JSONB, then just replace the current function with JSON_ARRAY_ELEMENTS()

Upvotes: 0

Pooya
Pooya

Reputation: 3183

Edited for jsonb[] type:

Demo

select
  r.data ->> 'name' as name
from
  accounts a
  cross join unnest(a.locations) al
  inner join regions r on r.id = (al ->> 'id')::int

P.S: for jsonb type:

You can use jsonb_to_recordset function and CROSS JOIN to join JSON array record with table.

Demo

select
  r.data ->> 'name' as name
from
  accounts a
  cross join jsonb_to_recordset(a.locations) as al(id int)
  inner join regions r on r.id = al.id

Upvotes: 1

Related Questions