PostgreSQL 14.9中如何关联两列JSONB数组后查询指定结果？

AI 小北

2026-7-11

PostgreSQL 14.9 JSONB数组关联查询解决方案

表结构（a_table）

Name (Txt)	Detail (JSONB)	state (JSONB)
apple	`[{"code": "156", "color": "red"}, {"code": "156", "color": "blue"}]`	`[{"color": "blue", "op2": "bad"}]`
orange	`[{"code": "156", "color": "red"}, {"code": "235", "color": "blue"}]`	`[{"color": "blue", "op2": "best"}]`
lemon	`[{"code": "156", "color": "red"}, {"code": "156", "color": "blue"}]`	`[{"color": "red", "op2": "good"}]`

预期查询结果

Name (Txt)	Detail (JSONB)	state (JSONB)
apple	`{"code": "156", "color": "red"}`
apple	`{"code": "156", "color": "blue"}`	`{"color": "blue", "op2": "bad"}`
orange	`{"code": "156", "color": "red"}`
lemon	`{"code": "156", "color": "red"}`	`{"color": "red", "op2": "good"}`
lemon	`{"code": "156", "color": "blue"}`

错误尝试的SQL

SELECT
    "Name (Txt)"
 ,  jsonb_build_object('code', elem->>'code', 'color', elem->>'color')  AS "Detail (JSONB)"
 ,  state::JSONB "
FROM your_table,
     jsonb_array_elements("Detail (JSONB)") AS elem,
     state::JSONB
WHERE elem->>'code' = '156';

正确SQL语句

SELECT
    t."Name (Txt)",
    elem AS "Detail (JSONB)",
    state_elem AS "state (JSONB)"
FROM a_table t
CROSS JOIN jsonb_array_elements(t."Detail (JSONB)") AS elem
LEFT JOIN jsonb_array_elements(t."state (JSONB)") AS state_elem
    ON elem->>'color' = state_elem->>'color'
WHERE elem->>'code' = '156';