possible to query a database for an empty jsonb

Question

I have a table in Postgres 9.6 db that is structured like this:

Table "public.pricings"
Column | Type | Modifiers 
---------------------------+-----------------------------+-------------------------------------------------------
id | integer | not null default nextval('pricings_id_seq'::regclass)
unconfirmed_matrix_prices | jsonb | not null default '"{}"'::jsonb

I'm new to using jsonb.

I'd like to search for any unconfirmed_matrix_prices that are empty(ie the default). I see I can do something like this:

solar_dev=# select count(*) from json_object_keys('{"f1":1,"f2":2}');
 count 
-------
 2
(1 row)

Is there a way I can do a where phrase where json_object_keys is equal to 0? I know a bit of a runaround way - Is there a better way?

• Erwin Brandstetter: Also be aware that there is no equality operator for json, only for jsonb. dba.stackexchange.com/questions/64759/…

Answer 1

You can simply check if the value is the default value, e.g.:

select *
from pricings
where unconfirmed_matrix_prices = '"{}"';

Note that an empty jsonb value should be written down as '{}', so the default value is not an empty jsonb. This may be problematic with some kind of queries, e.g. using the function jsonb_each(). I'd suggest to correct the default value in the way like this:

alter table pricings alter unconfirmed_matrix_prices set default '{}';
update pricings
set unconfirmed_matrix_prices = '{}'
where unconfirmed_matrix_prices = '"{}"';

Answer 2

This is a very bad idea.

not null default '"{}"'::jsonb

That's not storing an empty object. That's storing a JSONB string literal of "{}". You never want that. You normally want to confine it to a specific subtype instead, something like this..

not null default '{}' CHECK (jsonb_typeof(unconfirmed_matrix_prices) = 'object')

You also normally don't want to make it not-null. I'd rather have nullable and prevent empty objects if I need that kind of constraint. That simplifies your query substantially (as seen here).

• timpone: yeah, I agree - unfortunately this schema predates me. I just need to get this working right now.
• klin: Evan's tip is important, see my updated answer.
• duggi: @timpone oh man, I'm reading this in 2021 4 years later. hope this worked out :D
• timpone: @duggi yep - refactored out to use NULL - thx!