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}');
(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?


2 Answers 2


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 = '"{}"';

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

  • yeah, I agree - unfortunately this schema predates me. I just need to get this working right now.
    – timpone
    Commented Oct 21, 2017 at 0:11
  • 1
    Evan's tip is important, see my updated answer.
    – klin
    Commented Oct 21, 2017 at 1:11
  • 1
    @timpone oh man, I'm reading this in 2021 4 years later. hope this worked out :D
    – duggi
    Commented Feb 3, 2021 at 17:33
  • 3
    @duggi yep - refactored out to use NULL - thx!
    – timpone
    Commented Feb 3, 2021 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.