1

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?

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

  • yeah, I agree - unfortunately this schema predates me. I just need to get this working right now. – timpone Oct 21 '17 at 0:11
  • 1
    Evan's tip is important, see my updated answer. – klin Oct 21 '17 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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