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
@HeikoJakubzik Good idea, I tried casting as above and it worked. Add an answer and I'll accept it. — Babken Vardanyan

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

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';

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

1 Answers