Postgres: How to join table with values from jsonb array-CodePudding

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"}   |
---------------------------

Now I wanted to get result as follows

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

Please help with the postgresql query to get this.

CodePudding user response：

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

CodePudding user response：

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