PostgreSQL 10中JSON格式文本字段的查询关联及数据提取问题求助

针对你遇到的两个JSON字段处理难题，我整理了直接可用的SQL方案，一步到位解决问题：

1. 提取date_kg_later的最大日期 #

对于object表configuration字段里的date_kg_later数组，我们可以用jsonb_array_elements把数组拆分成单行数据，转成日期类型后取最大值；如果数组为空，就返回null。

2. 关联code_table获取类型名称 #

code_table的records是JSON数组，每个元素包含code和title，我们需要把这个数组展开，和object表的object_type_cd匹配，拿到对应的title。

完整查询SQL #

SELECT
    o.id,
    o.name,
    ct.title AS object_type_name,
    -- 处理date_kg_later的最大日期
    (
        SELECT MAX(to_date(jsonb_array_elements_text(o.configuration->'date_kg_later'), 'YYYY-MM-DD'))
        WHERE jsonb_array_length(o.configuration->'date_kg_later') > 0
    ) AS date
FROM
    object o
LEFT JOIN LATERAL (
    -- 展开code_table的records数组，匹配code值
    SELECT records->>'title' AS title
    FROM code_table, jsonb_array_elements(code_table.records) AS records
    WHERE records->>'code' = o.object_type_cd
) ct ON true
ORDER BY o.id;

结果说明 #

这个查询完全符合你的预期输出：

• 当date_kg_later是空数组时，date字段返回null
• 当object_type_cd在code_table里找不到匹配项时，object_type_name返回null
• 正常匹配时会返回对应的类型名称和数组中的最大日期

比如你的示例数据会输出：

id    name        object_type_name    date
1000  Headphones  tech                2022-04-30
1001  Pencil      null                null

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