Select records that have any null value on the Jsonb column-CodePudding

I have a table with a JSONB column. How to select records that have any null value on the JSONB column? I searched for a while and can't find anything, all results I have need a special key for query, and because I have dynamic key-value, I can't use this method.

abc
{"color": null, "size": "XL" }
{"name": "John", "Height": null, "weight": null }

CodePudding user response：

Using jsonb_each_text, sample data

select * into tbl 
from (
  values
     (1, '{"color": "blue", "size": "XL" }'::jsonb),
     (2, '{"name": "John", "Height": null, "weight": null }'::jsonb)
)t(id, val);

The query

select t.*
from tbl t
where exists (
   select 1 
   from jsonb_each_text(t.val) 
   where value is null
)

CodePudding user response：

You can do this using a JSON path expression.

select *
from the_table
where jsonb_path_exists(the_column, '$.keyvalue() ? (@.value == null)')