Compare boolean values in PostgreSQL 9.3 json objects

Question

Is there any other way to match a boolean value from a PostgreSQL (version 9.3) json object without converting it to string?

What I mean is: The table contains the following object in its jsoncolumn column:

'{"path":"mypath", "exists": true}'

the following query fetches the record (note that the exists value is fetched as text with ->>):

select * from thetable where jsoncolumn ->> 'exists' = 'true';

and this one doesn't:

select * from thetable where jsoncolumn -> 'exists' = true;

I wonder if there is a more appropriate way to do a boolean comparison?

Did you try: (jsoncolumn -> 'exists')::boolean = true?

user330315
– user330315

2016-09-14 14:37:01 +00:00
Commented Sep 14, 2016 at 14:37 — user330315
– user330315, Commented Sep 14, 2016 at 14:37
@a_horse_with_no_name yes, I have. it didn't work either.

BanzaiTokyo
– BanzaiTokyo

2016-09-14 14:51:03 +00:00
Commented Sep 14, 2016 at 14:51 — BanzaiTokyo
– BanzaiTokyo, Commented Sep 14, 2016 at 14:51

Kristo Mägi · Accepted Answer · 2016-09-14 16:08:00Z

5

Here're all the valid combinations to validate json(b) boolean:

-- This works only with jsonb, not with json because in Postgres json type is just a string.
SELECT $${ "exists": true }$$::jsonb -> 'exists' = 'true';
-[ RECORD 1 ]
?column? | t

-- All the following works with regular json as well with jsonb:
SELECT ( $${ "exists": true }$$::json ->> 'exists' )::boolean;
-[ RECORD 1 ]
bool | t

SELECT ( $${ "exists": true }$$::json ->> 'exists' )::boolean IS TRUE;
-[ RECORD 1 ]
?column? | t

SELECT ( $${ "exists": true }$$::json ->> 'exists' )::boolean = TRUE;
-[ RECORD 1 ]
?column? | t

answered Sep 14, 2016 at 16:08

Kristo Mägi

1,70413 silver badges16 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2016-09-14 15:19:27Z

0

Get the value as text then cast to boolean:

select pg_typeof((j ->> 'exists')::boolean)
from (values ('{"path":"mypath", "exists": true}'::json)) v(j)
;
 pg_typeof 
-----------
 boolean

Valid boolean literals

edited Sep 14, 2016 at 15:19

answered Sep 14, 2016 at 15:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

3 Comments

BanzaiTokyo Over a year ago

my question is how to query if the value is true rather than check if it is a boolean. sorry if it was unclear from my question.

Clodoaldo Neto Over a year ago

That was to show how to get a boolean and not a json. You can just use it as intended.

user9645 Over a year ago

This doesn't work. You can't tell the difference between {"exists": "true"} and {"exists": true} using this method.

Collectives™ on Stack Overflow

Compare boolean values in PostgreSQL 9.3 json objects

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related