查询整数成员的jsonb数组

Question

背景：我们使用书面记录来记录我们不断变化的模型的历史。现在我想查询一个属于某个客户的项目。PaperTrail可选择存储对象更改，我需要查询此字段以了解何时使用此ID创建了某些内容或更改为此ID。

我的表看起来简化如下：

item_type | object_changes
----------|----------------------------------------------------------
"Item"    | {"customer_id": [null, 5], "other": [null, "change"]}
"Item"    | {"customer_id": [4, 5], "other": ["unrelated", "change"]}
"Item"    | {"customer_id": [5, 6], "other": ["asht", "asht"]}

如何查询从或更改为ID 5的元素（因此上面的所有行）？我试过：

SELECT * FROM versions WHERE object_changes->'customer_id' ? 5;

这让我：

ERROR:  operator does not exist: jsonb ? integer
LINE 1: ...T * FROM versions WHERE object_changes->'customer_id' ? 5;
                                                                 ^
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

Answer 1

使用横向联接和jsonb\u array\u elements\u text函数处理每行的对象更改：

SELECT DISTINCT v.* FROM versions v
JOIN LATERAL jsonb_array_elements_text(v.object_changes->'customer_id') ids ON TRUE
WHERE ids.value::int = 5;

只有当您要查找的客户id可能在数组中出现多次时（如果更改了其他字段，但仍跟踪了客户id），才需要使用DISTINCT。

Answer 2

对于jsonb，contains操作符@

获取数字5是“customer\u id”数组元素的所有行：

SELECT *
FROM   versions 
WHERE  object_changes->'customer_id' @> '5';

<代码>@

• 没有函数匹配给定的名称和参数类型
• PostgreSQL错误：函数to_tsvector（字符变化，未知）不存在

不同的索引样式可以支持这一点。对于我上面建议的查询，使用表达式索引（专用、小型和快速）：

CREATE INDEX versions_object_changes_customer_id_gin_idx ON versions
USING gin ((object_changes->'customer_id'));

此备选查询也适用：

SELECT * FROM versions WHERE object_changes @> '{"customer_id": [5]}';

并且可以通过通用索引（更通用、更大、更慢）来支持：

CREATE INDEX versions_object_changes_gin_idx ON versions
USING gin (object_changes jsonb_path_ops);

相关：

• 用于在JSON数组中查找元素的索引

根据手册，操作员搜索JSON值中的任何顶级键。测试表明，数组中的字符串被视为“顶级键”，而数字则不是（键毕竟必须是字符串）。因此，尽管此查询可行：

SELECT * FROM versions WHERE object_changes->'other' ? 'asht';

在数组中查找数字的查询将不会（即使正确引用输入字符串文字）。它只能找到（引用！）分类为键的字符串，但不是分类为值的（无引号）数字。

旁白：标准JSON只知道4个原语：string、number、boolean和null。没有整数原语（即使我听说软件添加了它），整数只是数字的一个子集，在Postgres中实现为数字：

• https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE

因此，您的问题标题有点误导，因为严格来说，没有“整数”成员。