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

Question

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.

Side note: The schema of the JSON looks pretty static to me. You should consider not to abuse JSON but use relational means like (lookup and/or linking) tables and columns instead. — sticky bit
– sticky bit, Commented Jan 18, 2022 at 0:58
There are more column, keys and values, i just reduced them for better understanding. @stickybit — Crazy Cat
– Crazy Cat, Commented Jan 18, 2022 at 4:03

Pooya · Accepted Answer · 2022-01-19 06:41:52Z

1

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

edited Jan 19, 2022 at 6:41

answered Jan 18, 2022 at 5:42

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

2 Comments

Crazy Cat Over a year ago

function jsonb_array_elements(json[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Pooya Over a year ago

I edited my answer for jsonb[] column. You should tell your data type structure clearly to get better answer

Barbaros Özhan · Accepted Answer · 2022-01-19 06:50:44Z

0

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()

edited Jan 19, 2022 at 6:50

answered Jan 18, 2022 at 6:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

7 Comments

Crazy Cat Over a year ago

what is value in the where condition?

Crazy Cat Over a year ago

function jsonb_array_elements(json[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Barbaros Özhan Over a year ago

then your column is of json type instead of jsonb. So, just replace JSONB_ARRAY_ELEMENTS with JSON_ARRAY_ELEMENTS @CrazyCat

TeachMe Over a year ago

Can you explain this more?

Barbaros Özhan Over a year ago

Hi @TeachMe , I don't know what kind of an explanation you need, but presumingly this demo would help to explain. Best wishes.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related