PostgreSQL在JSONB阵列上加入

Question

我是JSONB的新手，我想知道，如果有一个查询可以使用以下内容：

我有很多看起来像这样的桌子：

ID (INT)  |  members (JSONB)

所有桌子只有一行。

2个表的示例

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"
  }
]

结果应该是这样的行：

计算机	标签	名称
12.12.12.12	狗	贝尼
1.1.1.1	猫	jone

谢谢！

Answer 1

使用然后加入它们（就像它们是关系表一样）。

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"

Answer 2

要从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'