PostgreSQL如何遍历JSONField数组并关联表查询object_uuid？

你已经通过jsonb_to_recordset成功展开了table_b中tags数组的元素，接下来只需要将展开后的标签与table_a进行关联匹配，就能得到对应的object_uuid。以下是完整的查询语句：

SELECT
    b.id,
    b.object_uuid AS table_b_object_uuid,
    a.object_uuid AS table_a_matched_object_uuid,
    t.name AS tag_name,
    t.value AS tag_value
FROM table_b b
CROSS JOIN jsonb_to_recordset(b.tags) AS t(name TEXT, value TEXT)
JOIN table_a a 
    ON a.tag_name = t.name 
    AND (a.tag_value = t.value OR (a.tag_value IS NULL AND t.value IS NULL))

关键说明： #

• CROSS JOIN：替代你原来的隐式交叉连接，语义更清晰，用于将table_b的每一行与展开后的标签行一一对应
• 匹配条件中的OR (a.tag_value IS NULL AND t.value IS NULL)：处理table_a中tag_value为空的情况（比如key_one），因为PostgreSQL中NULL = NULL不成立，需要用这种方式匹配空值
• 如果需要保留table_b中没有匹配到标签的行，可以把JOIN改成LEFT JOIN

针对你的示例数据，执行该查询会得到如下结果： #

如果你只需要返回匹配到的object_uuid，可以简化查询：

SELECT DISTINCT
    a.object_uuid
FROM table_b b
CROSS JOIN jsonb_to_recordset(b.tags) AS t(name TEXT, value TEXT)
JOIN table_a a 
    ON a.tag_name = t.name 
    AND (a.tag_value = t.value OR (a.tag_value IS NULL AND t.value IS NULL))

补充提示： #

如果你的tags字段是JSON类型（不是jsonb），只需把jsonb_to_recordset换成json_to_recordset即可，Django的JSONField在PostgreSQL默认存储为jsonb，所以前者更高效。

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