如何在PostgreSQL的键值对JSON字段中搜索符合条件的记录

你存储的是合法的JSON结构，筛选失败通常是取值路径错误或类型未转换导致的，核心问题如下：

• 你存储的JSON外层为动态数字键的对象结构，sync_at是二级嵌套字段，不能直接按顶层字段取值，需要先展开外层对象
• sync_at下的date字段是字符串类型，必须转换为PostgreSQL的时间类型才能做大小比较

正确查询示例 #

假设你的表名为posts，存储上述内容的JSONB字段名为wp_sync_info，需要筛选所有子对象中sync_at早于2021年4月1日的记录，查询语句如下：

SELECT DISTINCT p.*
FROM posts p,
-- 展开外层动态数字键的JSON对象
jsonb_each(p.wp_sync_info) AS item(item_key, item_value)
WHERE 
-- 提取date字符串转为带时区的时间类型，匹配存储的时区做比较
(item_value->'sync_at'->>'date')::timestamptz AT TIME ZONE 'Europe/Berlin' < '2021-04-01 00:00:00'::timestamptz;

优化建议 #

• 若该字段经常需要用于日期筛选，建议新增一个timestamptz类型的冗余字段存储最近同步时间，查询效率远高于每次解析JSON
• 若所有记录时区固定为Europe/Berlin，可以直接把sync_at存为标准时间字符串，无需嵌套时区相关字段，减少解析成本

内容的提问来源于stack exchange，提问作者Sébastien Grégoire