PostgreSQL在where子句中使用JSON子元素

Question

这可能是一个非常基础的问题，但我在网上找不到任何答案。

如果我创建了一个样例表：

 create table dummy ( id int not null, data json );

然后，如果我使用以下查询来查询表：

select * from dummy where data->'x' = 10;

现在由于表中还没有记录，并且任何记录中都没有名为“x”的属性，因此应该返回零结果。

但我收到了以下错误：

postgres=# select * from dummy where data->'x' = 10;
ERROR:  operator does not exist: json = integer
LINE 1: select * from dummy where data->'x' = 10;

然而，以下查询有效：

select * from dummy where cast(data->>'x' as integer) = 10;

我是否漏掉了什么，或者强制类型转换是我从JSON字段获取整数值的唯一方法吗？如果是这种情况，当数据变得非常大时，它不会影响性能吗？

- Mandeep Singh

---

即使如此，在等号右侧的值也必须是一个字符串，否则会出现错误。例如：postgres=# select * from dummy where data->>'x' = '10';id | data ----+----------- 1 | {"x": 10} (1 row)postgres=# select * from dummy where data->>'x' = 10; ERROR: operator does not exist: text = integer LINE 1: select * from dummy where data->>'x' = 10; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. - Mandeep Singh

为了阐述我想说的，如果我插入一个id = 10的新记录并在表上执行自连接“select * from dummy a, dummy b where a.id = b.data->>'x';”我仍然会得到相同的错误。实际上，必须进行类型转换才能比较整数值。 - Mandeep Singh

Answer 1

我是否理解有误，从json字段获取整数值只能使用类型转换？

你是正确的，类型转换是从json字段读取整数值的唯一方法。

如果是这样，当数据变得非常大时，它不会影响性能吗？

Postgres允许您索引包括转换的函数，因此下面的索引将使您能够快速检索所有数据->>x具有某个整数值的行

CREATE INDEX dummy_x_idx ON dummy(cast("data"->>'x' AS int))

- FuzzyTree

Answer 2

JSON运算符->>表示获取JSON数组元素（或对象字段）作为文本，因此需要进行类型转换。

您可以定义自己的JSON运算符，但这只会简化代码，而不会影响性能。

- klin