Monday, December 21, 2020

[PostgreSQL] How to check null in jsonb array

 Use jsonb_array_elements  to convert jsonb array to set (queriable similar to normal table) then we can use  where x::text = 'null' to match the null value.

Examples:

=> select x->'id' as id 
from jsonb_array_elements(
'[{"id": 4, "val": [null,1,null]}, 
{"id": 2, "val": [3]}]'::jsonb
) as x, 
jsonb_array_elements(x->'val') as y 
where y::text = 'null';
 id 
----
 4
 4
(2 rows)


=> select x->'id' as id
from jsonb_array_elements(
'[{"id": 4, "val": [null,1,null]}, 
{"id": 2, "val": [3]}]'::jsonb
) as x, 
jsonb_array_elements(x->'val') as y 
group by x->'id' 
having count(x) filter (where y::text  = 'null') > 1;
 id 
----------
 4
(1 row)


No comments:

Collectd PostgreSQL Plugin

I couldn't find this link when searching with google https://www.collectd.org/documentation/manpages/collectd.conf.html#plugin-postgresql