Postgres jsonb子数组对象属性查询优化及语法错误排查

我的Postgres表中有如下结构的jsonb文档：

{
  "Id": "267f9e75-efb8-4331-8220-932b023b3a34",
  "Name": "Some File",
  "Tags": [
    {
      "Key": "supplier",
      "Value": "70074"
    },
    {
      "Key": "customer",
      "Value": "1008726"
    }
  ]
}

目前我用以下查询可以正常找到Tags.Key为'supplier'的文档：

SELECT * FROM docs
WHERE EXISTS(
  SELECT TRUE
  FROM jsonb_array_elements(data -> 'Tags') x
  WHERE x ->> 'Key' IN ('supplier')
)

我想简化写法，尝试了这条语句：

select * from docs where data->'Tags' @> '[{ "Key":"supplier"}]';

但出现了语法错误：

<operator>, AT, EXCEPT, FETCH, FOR, GROUP, HAVING, INTERSECT, ISNULL, LIMIT, NOTNULL, OFFSET, OPERATOR, ORDER, TIME, UNION, WINDOW, WITH or '[' expected, got '@'

我的问题：

• 是否存在更短的有效查询语句？
• 第二条查询语句出错的原因是什么？

错误原因 #

你的第二条语句有两个问题：

1. 类型不匹配：data->'Tags'返回的是jsonb类型，但右边的'[{ "Key":"supplier"}]'是普通字符串。@>操作符要求两边必须都是jsonb类型，必须给字符串加上::jsonb转换类型才能使用该操作符。
2. 匹配逻辑不符合需求：即使解决了类型问题，这个写法也查不到目标数据。@>是包含匹配，要求数组中至少有一个元素和你指定的对象完全一致。但你的Tags元素除了Key还有Value字段，只指定Key的话，目标元素有额外的键值对，匹配不会成立。

更简洁的有效查询 #

方法1：JSON路径查询（Postgres 12及以上版本支持） #

这是最简洁的写法，直接用JSON路径语法指定匹配规则：

SELECT * FROM docs
WHERE jsonb_path_exists(data, '$.Tags[*] ? (@.Key == "supplier")');

方法2：简化EXISTS子查询 #

保留原逻辑但精简代码：

SELECT * FROM docs
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(data->'Tags') 
  WHERE ->>'Key' = 'supplier'
);

方法3：使用ANY子查询 #

用ANY直接判断目标值是否在数组元素的Key集合中：

SELECT * FROM docs
WHERE 'supplier' = ANY (
  SELECT x->>'Key' FROM jsonb_array_elements(data->'Tags') x
);

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