Finding null JSON values in Postgres
Postgres' JSONB functionality is fast and useful but when I find myself dropping down from the Django ORM into
SQL to do weird things, the syntax strikes me as confusing and arcane. As such, when I need to do esoteric things it
takes me longer time than I'd like, and in hopes that this saves you ten minutes of Stack Overflow trawling:
SELECT id, metadata FROM emails_subscriber
WHERE jsonb_typeof(metadata) != 'object'
OR jsonb_path_exists(metadata, '$.keyvalue() ? (@.value == null)')
Given a table emails_subscriber that has a JSONB column metadata, this returns all rows in the table where metadata itself is not null but some value within metadata is null (e.g. {"foo": 3, "bar": null}).