PostgreSQL filter JSON column with mixed boolean and JSON values in it

Question

My schema looks like this:

create table mytable
(
    id integer not null,
    value json not null
);

And the value column contains mixed data, both JSON and booleans like this:

id | value
----------
 1 | {"key1": 1, "key2": 2}
 2 | false
 3 | {"key2": 3}
 4 | true

These mixed values are accepted just fine by PostgreSQL.

Now I want to select all rows that either contain some json data or are true. I.e. I want to select rows 1, 3 and 4.

Here's the SQL query which I could come up with:

SELECT mytable.value
FROM mytable
WHERE CAST(CAST(mytable.value AS TEXT) AS BOOLEAN) IS NOT false;

But it fails with this message:

ERROR: argument of IS NOT FALSE must be type boolean, not type json

Your value false is not boolean but varchar (insert into mytable (id, value) values (4, true); fails, while insert into mytable (id, value) values (4, 'true'); works fine). You can select all values that are not 'false' like this: SELECT mytable.value FROM mytable WHERE mytable.value::text != 'false'; — Heiko Jakubzik
– Heiko Jakubzik, Commented Sep 6, 2018 at 17:41
@HeikoJakubzik Good idea, I tried casting as above and it worked. Add an answer and I'll accept it. — Babken Vardanyan
– Babken Vardanyan, Commented Sep 6, 2018 at 18:05

Heiko Jakubzik · Accepted Answer · 2018-09-06 18:47:46Z

1

Your value false is not boolean but varchar (insert into mytable (id, value) values (4, true); fails, while insert into mytable (id, value) values (4, 'true'); works fine). You can select all values that are not 'false' like this:

SELECT mytable.value FROM mytable WHERE mytable.value::text != 'false';

answered Sep 6, 2018 at 18:47

Heiko Jakubzik

4196 silver badges13 bronze badges

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

2 Comments

404 Over a year ago

Slight correction... it's not varchar, it's json. Inserting 'true' works because it can be cast successfully to JSON.

Abelisto Over a year ago

Consider to use jsonb type instead of json. It provides more possibilities including comparison of values, so your will be able to avoid explicit type casting SELECT mytable.value FROM mytable WHERE mytable.value != 'false';

Collectives™ on Stack Overflow

PostgreSQL filter JSON column with mixed boolean and JSON values in it

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related