我是 JSONB 的新手，我想知道，如果單個查詢可以實作以下操作：
我有很多看起來像這樣的表：
ID (INT) | members (JSONB)
所有的表只有一行。
2張桌子的例子
表1：
編號：1
資料：
[
{
"computer": "12.12.12.12",
"tag": "dog"
},
{
"computer": "1.1.1.1",
"tag": "cat"
},
{
"computer": "2.2.2.2",
"tag": "cow"
}
]
表2：
編號：1
資料：
[
{
"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 | 貓 | 瓊斯 |
謝謝 ！
uj5u.com熱心網友回復：
使用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"
分貝小提琴
uj5u.com熱心網友回復：
要從 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/490931.html
標籤：json PostgreSQL jsonb jsonb 数组元素