Search code examples
postgresqljsonb

Query Postgres JSONB where key doesn't exist

Given a set of data.

1 | { 'completed': true }
2 | { 'office_completed': false }

Is there a way to query office_completed != 'true' and have it return both records? Or a way to select all records that DON'T have the office_completed key? I can query all records which have the office_completed key with:

SELECT * FROM jsonb WHERE data ? 'office_completed';

But couldn't find a way to pull the opposite.

What brings me to this problem is that I have a data set that may or may not have the office_completed key and I need to query all records that have office_completed != 'true' but if the key doesn't exist I get nothing from it. If the key doesn't exist, I'd think that would count as !=true but it apparently doesn't. :-) I figured the alternative would be to select all records that don't have the office_completed key OR office_completed != 'true' but can't figure out how to do that.

I appreciate it if somebody has a better idea for this. Thanks!

Solution

  • Just for reference for previous post.

    Simply using NOT() like that if want to find all record don't have key:

    SELECT * FROM jsonb WHERE NOT( data ? 'office_completed' );