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 !

Answer 1

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

Answer 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