使用 "json_field@>" 表示大于、在 etc 中 (PostgreSQL)

Question

假设我在PostgreSQL数据库中有一个表，比如

id | json_field
---+-----------
1    {"name":"foo","age":20}
2    {"name":"bar", "age":10, "alive":1}
.
.

我可以通过获得json_field的数据，其中name=="foo"

SELECT * from my_table where json_field@>"{'name':'foo'}"

或

SELECT * from my_table where (json_field->>'name')='foo'"

由于第一个查询要快得多(据我所知，它是索引的，第二个查询必须循环通过每个记录并检查(，有没有一种方法可以实现其他条件，比如"；大于"处于"不是"；例如(一些伪代码(

SELECT * from my_table where json_field@>"{'name':is not 'foo'}"
SELECT * from my_table where json_field@>"{'name':>'foo'}"
SELECT * from my_table where json_field@>"{'age':is in '(20,50)'}"

Answer 1

如果您使用的是Postgres 12或更高版本，您可以使用JSON路径查询：

SELECT * 
from my_table 
where json_field @@ '$.name <> "foo"';
SELECT * 
from my_table 
where json_field @@ '$.age == 20 || $.age == 50'

如果您使用的是较旧的Postgres版本，则需要提取值并使用"；正常的"；SQL表达式：

SELECT * 
from my_table 
where json_field ->> 'name' <> 'foo';

SELECT * 
from my_table 
where (json_field ->> 'age')::int in (20,50);

为了提高效率，您必须为这些表达式创建索引：

create index on my_table ( (json_field ->> 'name') );
create index on my_table ( ((json_field ->> 'age')::int) );

这就是你为数据模型去标准化所要付出的代价。