PostgreSQL中查询含嵌套对象与值数组的jsonb列的SQL实现

给定PostgreSQL表中存在一个jsonb列，数据结构示例如下：

{
    "rules": [
        {
            "type": "weekdays",
            "values": [3, 5]
        },
        {
            "type": "dates",
            "values": ["2024-01-01", "2024-01-02", "2024-01-03"]
        }
    ]
}

以下是满足需求的两种查询实现方式：

1. 筛选rules数组中存在type为"weekdays"且values包含3的记录 #

方式一：使用JSON路径查询（简洁写法） #

SELECT * 
FROM your_table 
WHERE jsonb_path_exists(
    jsonb_column,
    '$.rules[*] ? (@.type == "weekdays" && @.values[*] == 3)'
);

方式二：使用数组展开+EXISTS子查询（兼容性更好） #

SELECT * 
FROM your_table t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.jsonb_column->'rules') AS rule
    WHERE rule->>'type' = 'weekdays'
      AND (rule->'values') @> '3'::jsonb
);

2. 筛选rules数组中存在type为"dates"且values包含'2024-01-02'的记录 #

方式一：使用JSON路径查询 #

SELECT * 
FROM your_table 
WHERE jsonb_path_exists(
    jsonb_column,
    '$.rules[*] ? (@.type == "dates" && @.values[*] == "2024-01-02")'
);

方式二：使用数组展开+EXISTS子查询 #

SELECT * 
FROM your_table t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.jsonb_column->'rules') AS rule
    WHERE rule->>'type' = 'dates'
      AND (rule->'values') @> '"2024-01-02"'::jsonb
);

关键说明 #

• 不要硬编码数组索引（如->0），需求是匹配数组中任意一个符合条件的对象，而非固定位置的元素。
• jsonb_array_elements函数用于将jsonb数组拆分为单行记录，方便逐个判断数组元素。
• @>是jsonb包含操作符，用于判断目标数组是否包含指定值，注意值要转换为对应jsonb类型（数字直接转，字符串需加引号转）。

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