postgresql postgresql在where子句中使用json子元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24043688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql using json sub-element in where clause
提问by Mandeep Singh
This might be a very basic question but I am not able to find anything on this online.
这可能是一个非常基本的问题，但我无法在网上找到任何内容。
If I create a sample table :
如果我创建一个示例表：
create table dummy ( id int not null, data json );
Then, if I query the table using the following query:
然后，如果我使用以下查询查询表：
select * from dummy where data->'x' = 10;
Now since there are no records in the table yet and there is no such property as 'x' in any record, it should return zero results.
现在由于表中还没有记录，并且任何记录中都没有“x”这样的属性，它应该返回零结果。
But I get the following error:
但我收到以下错误：
postgres=# select * from dummy where data->'x' = 10;
ERROR: operator does not exist: json = integer
LINE 1: select * from dummy where data->'x' = 10;
However following query works:
但是以下查询有效：
select * from dummy where cast(data->>'x' as integer) = 10;
Am I missing something here or typecasting is the only way I can get an integer value from a json field ? If that's the case, does it not affect the performance when data becomes extremely large ?
我在这里遗漏了什么，或者类型转换是我从 json 字段中获取整数值的唯一方法？如果是这样，当数据变得非常大时，它不会影响性能吗？
采纳答案by FuzzyTree
Am I missing something here or typecasting is the only way I can get an integer value from a json field ?
我在这里遗漏了什么，或者类型转换是我从 json 字段中获取整数值的唯一方法？
You're correct, typecasting is the only way to read an integer value from a json field.
你是对的，类型转换是从 json 字段读取整数值的唯一方法。
If that's the case, does it not affect the performance when data becomes extremely large ?
如果是这样，当数据变得非常大时，它不会影响性能吗？
Postgres allows you to index functions including casts, so the index below will allow you to quickly retrieve all rows where data->>x has some integer value
Postgres 允许您索引包括强制转换在内的函数，因此下面的索引将允许您快速检索 data->>x 具有一些整数值的所有行
CREATE INDEX dummy_x_idx ON dummy(cast("data"->>'x' AS int))
回答by klin
JSON operator ->>means Get JSON array element (or object field) as text,so type cast is necessary.
JSON 运算符->>意味着将JSON 数组元素（或对象字段）作为文本获取，因此类型转换是必要的。
You could define your own JSON operator, but it would only simplify the code, without consequences for performance.
您可以定义自己的 JSON 运算符，但这只会简化代码，而不会影响性能。
