How to compare JSON integer values

[aspeakman]

Environment

• PostgreSQL version: 10
• PostgREST version: 7.0.1
• Operating system: Ubuntu 18

Description of issue

If I have a JSON field in an exposed Postgrest view, for example containing

{ 'a_text': 'Example text', 'b_int': 88 }

I seem to be able to retrieve entries as text using the standard PostgreSQL JSON '->>' operator. The following both work

?json_field->>a_text=like.Ex*
?json_field->>b_int=eq.88

But I cannot retrieve properly using integer comparison even if casting values to integer. Both the following examples erroneously match the entry above because '88' is lexographically less than '9'

?json_field->>b_int=lt.9
?json_field->>b_int::int=lt.9

[steve-chavez]

Instead of the double arrowhead operator ->>(get as text) use the single arrowhead operator->(get as json). Like:

GET /example?json_field->b_int=lt.9

More info about pg json operators at: https://www.postgresql.org/docs/current/functions-json.html

Edit: Note that comparison operators will only work if the type is jsonb and not json

[aspeakman]

This operator does not work for me in postgrest

?json_field->b_int=eq.88
?json_field->b_int::int=eq.88

both result in

{"hint":"No operator matches the given name and argument type(s). You might need to add explicit type casts.","details":null,"code":"42883","message":"operator does not exist: json = unknown"}

But to be fair this just reflects what is going on in the equivalent WHERE clause of the underlying PostGreSQL

select * from dummy where (json_field->'b_int') = 88;
ERROR: operator does not exist: json = integer

or with a cast

select * from dummy where (json_field->'b_int')::int = 88;
ERROR: cannot cast type json to integer

[steve-chavez]

Can you change your type to jsonb? Then the operators will work.

[aspeakman]

In PostGreSQL I can solve this by casting from the text operator - but this does not seem to work in postgrest?

select * from dummy where (json_field->>'b_int')::int = 88;

Could change the field to jsonb in the future or maybe an integer index on the 'b_int' field might work

[aspeakman]

Thanks yes I have verified that the '->' operator works for integer comparisons using a jsonb field

There might still be a small problem with JSON boolean values and the IS operator on a jsonb field, as this fails

?jsonb_field->c_bool=is.false
argument of IS FALSE must be type boolean, not type jsonb

However you can get round it by using the eq operator (presumably comparing contents as text)

?jsonb_field->c_bool=eq.false

[steve-chavez]

?jsonb_field->c_bool=is.false
argument of IS FALSE must be type boolean, not type jsonb

Ah, that's a gotcha. The eq.false is a good workaround.

In PostGreSQL I can solve this by casting from the text operator - but this does not seem to work in postgrest?

The problem is that casting like that invalidates index usage. We had a discussion about this before: #1651 (comment)

So converting to jsonb is definitely your best bet now.