PostgreSQL jsonb value in WHERE BETWEEN clause

Question

I've got jsonb field in my database table (a_table) with int value within, say:

{
  "abc":{
       "def":{
            "ghk":500
        }
   }
}

I'm about to create SELECT with filter by this field ("ghk") using WHERE clause:

SELECT * FROM a_table WHERE ghk BETWEEN 0 AND 1000;

How should i create such a query? Couldn't find good tutorial for jsonb usage so far.

Thanks in advance!

EDIT ~~I found this solution:~~

~~SELECT * FROM a_table WHERE a_field #> '{abc,def,ghk}' BETWEEN '0' AND '10000' ;~~

~~Is it correct?~~

score 5 · Accepted Answer · 2015-04-30 10:24:47Z

5

The #> returns a JSONB document which you cannot cast to an int. You need the #>> operator which returns a scalar value that can be casted to an integer:

select *
from a_table
where (json_col #>> '{abc,def,ghk}')::int between 0 and 1000

All JSON operators are documented in the manual: http://www.postgresql.org/docs/current/static/functions-json.html

Using BETWEEN '0' AND '10000' is not a good idea because this does a string comparison not a numeric comparison. The value '2' does not lie between '0' and '10000'. That's why you need to cast the returned value to a number to get a correct comparison.

edited Apr 30, 2015 at 10:24

answered Apr 30, 2015 at 10:19

user330315

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

1 Comment

Eckler Over a year ago

Many thanks :) I'll check your answer as a solution as soon as possible.

Collectives™ on Stack Overflow

PostgreSQL jsonb value in WHERE BETWEEN clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related