Postgresql join on jsonb array

Question

I'm new to JSONB and I am wondering, if the following would be possible with a single query:

I have a lot of tables that look like this:

ID (INT)  |  members (JSONB)

all the tables has only one row.

example for 2 tables

table1:

id: 1

data:

[
  {
    "computer": "12.12.12.12",
    "tag": "dog"
  },
  {
    "computer": "1.1.1.1",
    "tag": "cat"
  },
  {
    "computer": "2.2.2.2",
    "tag": "cow"
  }
]

table2:

id: 1

data:

[
  {
    "IP address": "12.12.12.12",
    "name": "Beni",
    "address": "Rome"
  },
  {
    "IP address": "1.1.1.1",
    "name": "Jone",
    "address": "Madrid"
  }
]

The result should be rows like this :

computer	tag	name
12.12.12.12	dog	Beni
1.1.1.1	cat	Jone

Thanks !

Tomáš Záluský · Accepted Answer · 2022-06-14 12:18:33Z

2

Convert jsons into setof types using jsonb_to_recordset function and then join them (like they were relational tables).

with table1 (id,members) as (
  values (1,'[{"computer": "12.12.12.12","tag": "dog"},{"computer": "1.1.1.1","tag": "cat"},{"computer": "2.2.2.2","tag": "cow"}]'::jsonb)
),   table2 (id,members) as (
  values (1,'[{"IP address": "12.12.12.12","name": "Beni", "address": "Rome"},{"IP address": "1.1.1.1","name": "Jone", "address": "Madrid"}]'::jsonb)
)
select t1.computer, t1.tag, t2.name
from jsonb_to_recordset((select members from table1 where id=1)) as t1(computer text,tag text)
join jsonb_to_recordset((select members from table2 where id=1)) as t2("IP address" text,name text)
  on t1.computer = t2."IP address"

db fiddle

answered Jun 14, 2022 at 12:18

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

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

Comments

FatFreddy · Accepted Answer · 2022-06-14 12:54:23Z

0

to get values out of a jsonb array of objects you somehow have to explode them. another way with jsonb_array_elements:

with _m as (
select
jsonb_array_elements(members.data) as data
from members
),
_m2 as (
select
jsonb_array_elements(members2.data) as data
from members2
)

select
_m.data->>'computer' as computer,
_m.data->>'tag' as tag,
_m2.data->>'name' as name
from _m
left join _m2 on _m2.data->>'IP address' = _m.data->>'computer'

https://www.db-fiddle.com/f/68iC5TzLKbzkLZ8gFWYiLz/0

answered Jun 14, 2022 at 12:54

FatFreddy

1,2101 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Postgresql join on jsonb array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related