PostgreSQL 14.9中JSONB数组关联匹配查询的实现需求

你可以通过jsonb_path_query_first函数直接从additional (JSONB)数组中匹配对应op值的元素，无匹配时返回null，正好符合需求。完善后的SQL如下：

SELECT
    "Name (Txt)",
    elem AS "content (JSONB)",
    jsonb_path_query_first(
        "additional (JSONB)",
        '$[*] ? (@.op == $op_val)'::jsonpath,
        jsonb_build_object('op_val', elem->>'op')
    ) AS "additional (JSONB)"
FROM test_table,
     jsonb_array_elements("content (JSONB)") AS elem
WHERE elem->>'no' = '5';

逻辑说明 #

1. jsonb_array_elements("content (JSONB)") AS elem：将content数组展开为单行记录，每条记录对应一个数组元素。
2. WHERE elem->>'no' = '5'：筛选出content中no值为5的元素。
3. jsonb_path_query_first(...)：使用JSON路径查询，从additional数组中找到第一个op值与当前elem的op值匹配的对象；如果没有匹配项，返回null（对应结果中的空单元格）。

如果你偏好使用JOIN的方式实现，也可以采用以下SQL：

SELECT
    t."Name (Txt)",
    elem AS "content (JSONB)",
    add_elem AS "additional (JSONB)"
FROM test_table t
CROSS JOIN jsonb_array_elements(t."content (JSONB)") AS elem
LEFT JOIN jsonb_array_elements(t."additional (JSONB)") AS add_elem
    ON add_elem->>'op' = elem->>'op'
WHERE elem->>'no' = '5';

这种方式通过左连接展开后的additional数组，匹配op值，无匹配时add_elem为null，同样能得到目标结果。

内容的提问来源于stack exchange，提问作者booom pal