CODEWITHSUNDEEP
postgresql
greduan
greduan

Reputation: 4938

Match JSONB row where at least one of object's values is not null

In our database we have a data set sorta like the following:

+----+-------------------------------+
| id | stuff                         |
+----+-------------------------------+
| 1  | {}                            |
+----+-------------------------------+
| 2  | {"a": "something", "b": null} |
+----+-------------------------------+
| 3  | {"c": null, "d": null}        |
+----+-------------------------------+

I would like to match only, in this case, the one with id = 2, reason being that at least one of the values in the object is not null.

How can this be done with PostgreSQL?

Upvotes: 1

Views: 276

Answers (1)

klin
klin

Reputation: 121574

Use the function jsonb_each_text() or json_each_text(), example:

with my_table(id, jdata) as (
values
    (1, '{}'::jsonb),
    (2, '{"a": "something", "b": null}'),
    (3, '{"c": null, "d": null}')
)

select distinct t.*
from my_table t
cross join jsonb_each_text(jdata)
where value is not null;

 id |             jdata             
----+-------------------------------
  2 | {"a": "something", "b": null}
(1 row)

This query (proposed by Abelisto, see the comments) should be more performant on a larger dataset:

select t.*
from my_table t
where exists (
    select 1
    from jsonb_each_text(jdata)
    where value is not null);

Upvotes: 3

Related Questions