Postgresql连接jsonb数组

Question

我是JSONB的新手，我想知道，如果以下是可能的单个查询:

我有很多这样的表:

ID (INT)  |  members (JSONB)

所有的表只有一行。

两个表的例子

table1:

id: 1

数据:

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

表:

id: 1

数据:

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

结果应该是这样的行:

<表类><>电脑标记名称tbody><<tr>12.12.12.12狗贝尼省1.1.1.1猫众

使用jsonb_to_recordset函数将json转换为setof类型，然后将它们连接起来(就像它们是关系表一样)

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小提琴

要从jsonb对象数组中获取值，您必须以某种方式对它们进行爆炸。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