CODEWITHSUNDEEP
sqljsonpostgresqlboolean
Mojtaba Arvin
Mojtaba Arvin

Reputation: 739

how to check a value of a key is true in postgres jsonb query

For example my table is :

CREATE TABLE mytable (
    id bigint NOT NULL,
    foo jsonb
);

and it has some values :

id   | foo
-----+-------
 1   | "{'a':false,'b':true}"
 2   | "{'a':true,'b':false}"
 3   | NULL

I want to know how to check if value of a key is true , and which operator should I use?

I want something like this that can check the value :

SELECT 1 
FROM mytable
WHERE
id=2
AND
foo['a'] is true
;

Upvotes: 11

Views: 8214

Answers (5)

lars
lars

Reputation: 670

Get the JSON object field, cast to boolean and do a regular SQL where clause:

select * 
from mytable
where (foo -> 'a')::boolean is true;

Upvotes: 1

slickmb
slickmb

Reputation: 21

More correct might be

SELECT 1
  FROM mytable
 WHERE id=2
   AND (foo -> 'a') = 'true'::JSONB;

This has the benefit of allowing postgres to make better use of any indexes you may have on your jsonB data as well as avoiding some of the ambiguity with the ->> operator that others have mentioned.

Using ->>

=> SELECT (('{"a": true}'::JSONB)->>'a') = 'true' as result;
 result 
--------
 t
(1 row)

=> SELECT (('{"a": "true"}'::JSONB)->>'a') = 'true' as result;
 result 
--------
 t
(1 row)

Using ->

=> SELECT (('{"a": "true"}'::JSONB)->'a') = 'true'::JSONB as result;
 result 
--------
 f
(1 row)

=> SELECT (('{"a": true}'::JSONB)->'a') = 'true'::JSONB as result;
 result 
--------
 t
(1 row)

Note: This is the same as Tamlyn's answer, but with an included example of how to compare against a JSONB true.

Upvotes: 2

Tamlyn
Tamlyn

Reputation: 23562

To get the text value of a key use ->> (double head) and to get the json or jsonb value use -> (single head).

Be careful because the text representations of JSON boolean value true and string value "true" are both true.

tamlyn=# select '{"a":true}'::json->>'a' bool, '{"a":"true"}'::json->>'a' str;
 bool | str
------+------
 true | true
(1 row)

In your case you probably want ->.

tamlyn=# select '{"a":true}'::json->'a' bool, '{"a":"true"}'::json->'a' str;
 bool |  str
------+--------
 true | "true"
(1 row)

Upvotes: 0

Mojtaba Arvin
Mojtaba Arvin

Reputation: 739

SELECT 1
FROM mytable
Where
id=2
AND
(foo ->> 'a')::boolean is true;
;

Upvotes: 4

user330315
user330315

Reputation:

The syntax foo['a'] is invalid in Postgres.

If you want to access the value of a key, you need to use the ->> operator as documented in the manual 

select *
from mytable
where id = 2
and foo ->> 'a' = 'true';

Upvotes: 12

Related Questions