Postgres 9.4 jsonb queries basic operators

Question

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/)

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},

If I do the current method of

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

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, not equal, in array, not in array

jsonb supported from 9.4, it must be a typo (but please update your question to be sure). — pozs
– pozs, Commented Jan 26, 2016 at 9:25

Dzmitry Savinkou · Accepted Answer · 2016-01-25 21:47:51Z

2

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;

Result is:

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

answered Jan 25, 2016 at 21:47

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jakub Kania · Accepted Answer · 2016-01-25 21:48:21Z

1

The documentation is on the postgresql page. The documentation states that the ->> operator returns string, your right hand operand is a string too so the result is correct.

To do what you wanted to do you must cast the result returned from the json to integer:

SELECT * FROM mytable where (json_field ->> 'power')::int < '2';

Please note the brackets are needed as not to case 'power' to int.

answered Jan 25, 2016 at 21:48

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Postgres 9.4 jsonb queries basic operators

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related