Postgres 9.4 jsonb查询基本运算符 [英] Postgres 9.4 jsonb queries basic operators

问题描述

有什么方法可以对Postgres中的某些表中的jsonb字段进行查询，这些查询基本上可以与Mongodb查询运算符等同(在此处

Is there any way to make queries on a jsonb field in some table in Postgres that are basically equitable to the Mongodb query operators (listed here https://docs.mongodb.org/manual/reference/operator/query-comparison/)

我希望能够在Postgres表中存储一些json对象，例如:

I would like to be able to store some json objects in a postgres table for example:

{"power": 200},
{"power": 400},
{"power": 0},
{"power": 146297},

如果我使用

SELECT * FROM mytable where json_field ->> 'power' < '2';

我被撤回了功率为0和功率146297的行...

I get retured both the row for power 0 and power 146297...

某处是否有一些文档指定了操作方法

Is there some documentation somewhere that specifies how to do

gt，gte，lt，lte，eq，eq，不等于，在数组中，不在数组中

gt, gte, lt, lte, eq, not equal, in array, not in array

推荐答案

您需要转换->>字符串结果值:

You need to cast ->> string result values:

WITH mytable(json_field) AS ( VALUES
  ('{"power": 200}'::JSONB),
  ('{"power": 400}'::JSONB),
  ('{"power": 0}'::JSONB),
  ('{"power": 146297}'::JSONB)
)
SELECT * FROM mytable where (json_field->>'power')::INTEGER < 2;

结果是:

  json_field  
--------------
 {"power": 0}
(1 row)

这篇关于Postgres 9.4 jsonb查询基本运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！