PostgreSQL如何检查JSONB指定路径下的数组是否包含值'v3'

场景说明 #

有一张表包含以下字段：

• c1：字符串类型
• c2：整数类型
• jsonb：JSONB类型，结构示例如下：

{
  "o1": "hi",
  "o2": "hello",
  "o3": {
    "o3a": {
      "o3aa": {
        "id1": ["v1", "v2", "v3"],
        "id2": ["v3"],
        "id99": ["v99", "v2", "v3"]
      }
    },
    "o3ab": true
  }
}

需求是：找出jsonb字段中o3->o3a->o3aa下所有值为数组且包含'v3'的键（如id1、id2、id99），或筛选表中存在这类键的行。

错误分析 #

你尝试的第二条SQL返回空结果，原因是$.o3.o3a.o3aa是JSON对象而非数组，$[*]是数组遍历语法，无法遍历对象的属性，因此匹配不到任何内容。

解决方案 #

1. 筛选表中符合条件的行 #

如果只需找出表中存在至少一个o3aa下的数组包含'v3'的行，使用JSON路径表达式：

SELECT * 
FROM your_table 
WHERE jsonb @? '$.o3.o3a.o3aa.* ? (@ contains "v3")';

• $.o3.o3a.o3aa.*：遍历o3aa下的所有属性值
• @ contains "v3"：判断当前属性值（数组）是否包含"v3"元素

2. 提取具体包含'v3'的键名 #

如果需要获取所有符合条件的键名（如id1、id2），可以用jsonb_each展开对象的键值对后筛选：

SELECT t.c1, t.c2, j.key AS id_name
FROM your_table t
JOIN jsonb_each(t.jsonb -> 'o3' -> 'o3a' -> 'o3aa') j ON true
WHERE j.value @> '"v3"'::jsonb;

• jsonb_each(...)：将o3aa对象拆分成多行，每行包含key（键名）和value（对应数组）
• j.value @> '"v3"'::jsonb：判断数组是否包含'v3'（需将字符串转为JSONB类型）

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