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}).

Lightning bolt
Subscribe to my newsletter

I publish monthly roundups of everything I've written, plus pictures of my corgi.
© 2024 Justin Duke · All rights reserved · have a nice day.