7

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?

timpone
  • 647
  • 3
  • 8
  • 17
  • 1
    Also be aware that there is no equality operator for json, only for jsonb. https://dba.stackexchange.com/questions/64759/how-to-remove-known-elements-from-a-json-array-in-postgresql/64765#64765 – Erwin Brandstetter Oct 21 '17 at 13:36

2 Answers2

9

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 = '"{}"';
klin
  • 2,174
  • 17
  • 16
6

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

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479