PostgreSQL中如何筛选数组内所有元素字段值相同的JSON对象

需求说明 #

现有存储在PostgreSQL中的JSON数据（示例如下），需要筛选出attributes数组内所有元素的attribute_name字段均为"Some_name"的对象，比如示例中的Test_2。

示例数据：

[
  {
    "name": "Test_1",
    "attributes": [
      {
        "attribute_name" : "Some_name"
      },
      {
        "attribute_name" : "Some_name_2"
      }
    ],
    "phoneNumber" : "N"
  },
  {
    "name": "Test_2",
    "attributes": [
      {
        "attribute_name" : "Some_name"
      },
      {
        "attribute_name" : "Some_name",
        "attribute_phoneNumber": "N1"
      }
    ],
    "phoneNumber" : "N2"
  }
]

实现方法 #

方法1：结合json_array_elements与分组筛选 #

假设表名为test_table，JSON字段名为data，执行以下SQL：

SELECT t.data
FROM test_table t
LEFT JOIN LATERAL json_array_elements(t.data->'attributes') attr ON attr->>'attribute_name' != 'Some_name'
GROUP BY t.data
HAVING COUNT(attr) = 0;

逻辑：通过LEFT JOIN展开attributes数组，关联条件筛选出attribute_name不符合的元素；分组后若不符合项的计数为0，说明数组内所有元素都满足要求。

方法2：使用NOT EXISTS子查询（推荐） #

SELECT data
FROM test_table
WHERE NOT EXISTS (
  SELECT 1
  FROM json_array_elements(data->'attributes') attr
  WHERE attr->>'attribute_name' != 'Some_name'
);

逻辑：子查询检查当前行的attributes数组是否存在不符合条件的元素，NOT EXISTS确保没有此类元素，即全部匹配。

方法3：纯JSON函数判断（无需展开数组） #

SELECT data
FROM test_table
WHERE data @> '{"attributes": [{"attribute_name": "Some_name"}]}'
AND json_array_length(data->'attributes') = (
  SELECT COUNT(*)
  FROM json_array_elements(data->'attributes') attr
  WHERE attr->>'attribute_name' = 'Some_name'
);

逻辑：先用@>操作符确保数组至少包含一个符合条件的元素，再判断符合条件的元素数量等于数组总长度，以此确认所有元素都匹配。

关键JSON函数说明（PostgreSQL 9.5） #

• json_array_elements(json)：将JSON数组展开为行集合，每个数组元素对应一行记录。
• json_array_length(json)：返回JSON数组的元素总数。
• ->：提取JSON对象的指定字段，返回JSON类型；->>：提取JSON对象的指定字段并转为文本类型。
• @>：JSON包含操作符，判断左侧JSON是否包含右侧JSON的结构与值。
• LATERAL JOIN：允许子查询引用主查询的列，常用于逐行处理JSON数组。

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