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