PostgreSQL中如何按jsonb数组内的对象属性值过滤查询

注：你之前写的单层级非数组场景查询SQL有误，键名需要用引号包裹，正确写法为 select * from tbl where meta->'foo'->>'bar' = 'baz';

单层级数组过滤（当前foo数组场景） #

分为两种常见过滤需求：

• 需求1：匹配meta.foo数组中任意一个元素的bar属性等于目标值
  推荐使用JSONB包含操作符@>实现，性能更优，支持GIN索引加速：

  SELECT * FROM tbl 
  WHERE meta->'foo' @> '[{"bar": "baz 1"}]';
  

  也可以用拆分数组的写法适配更复杂的判断逻辑：

  SELECT * FROM tbl 
  WHERE EXISTS (
    SELECT 1 FROM jsonb_array_elements(meta->'foo') AS elem
    WHERE elem->>'bar' = 'baz 1'
  );
  

• 需求2：要求meta.foo数组中所有元素的bar属性都等于目标值
  用反向不存在的逻辑实现：

  SELECT * FROM tbl
  WHERE NOT EXISTS (
    SELECT 1 FROM jsonb_array_elements(meta->'foo') AS elem
    WHERE elem->>'bar' != '目标值'
  );
  

多层嵌套数组适配 #

如果需要匹配meta.foo.*.items.*.bar这类多层嵌套数组的属性，可通过多次拆分数组实现，示例结构如下：

{
  "foo": [
    {
      "items": [
        {"bar": "val1"},
        {"bar": "val2"}
      ]
    }
  ]
}

对应查询SQL：

SELECT * FROM tbl
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(meta->'foo') AS foo_elem,
                jsonb_array_elements(foo_elem->'items') AS item_elem
  WHERE item_elem->>'bar' = '目标值'
);

MikroORM 动态适配实现 #

你可以在ORM层封装通用的JSON数组匹配逻辑：

• 固定路径的简单查询，直接用内置的$contains操作符即可：

  const result = await em.find(Entity, {
    meta: {
      foo: { $contains: [{ bar: 'baz 1' }] }
    }
  });
  

• 动态多层级场景可封装自定义查询方法，将查询路径拆分为数组节点和属性节点，动态拼接EXISTS子查询逻辑即可。

内容的提问来源于stack exchange，提问作者Martin Adámek