CODEWITHSUNDEEP
sqljsonpostgresqltypesjsonb
sbeam
sbeam

Reputation: 4892

How to query a json column for empty objects?

Looking to find all rows where a certain json column contains an empty object, {}. This is possible with JSON arrays, or if I am looking for a specific key in the object. But I just want to know if the object is empty. Can't seem to find an operator that will do this.

 dev=# \d test
     Table "public.test"
  Column | Type | Modifiers
 --------+------+-----------
  foo    | json |

 dev=# select * from test;
    foo
 ---------
  {"a":1}
  {"b":1}
  {}
 (3 rows)

 dev=# select * from test where foo != '{}';
 ERROR:  operator does not exist: json <> unknown
 LINE 1: select * from test where foo != '{}';
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 dev=# select * from test where foo != to_json('{}'::text);
 ERROR:  operator does not exist: json <> json
 LINE 1: select * from test where foo != to_json('{}'::text);
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 dwv=# select * from test where foo != '{}'::json;
 ERROR:  operator does not exist: json <> json
 LINE 1: select * from test where foo != '{}'::json;
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Upvotes: 106

Views: 140456

Answers (10)

Juan
Juan

Reputation: 1421

This worked for me (I came looking for an answer to the exact same problem):

SELECT * FROM my_table t WHERE (t.json->0) IS NOT NULL

On PostgreSQL 9.4.4.

Upvotes: -1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

There is no equality (or inequality) operator for the data type json as a whole, because equality is hard to establish. Consider jsonb in Postgres 9.4 or later, where this is possible. More details in this related answer on dba.SE (last chapter):

SELECT DISTINCT and GROUP BY fail for the same reason (no equality operator).

Casting both sides of the expression to text allows the operators = or <>, but that's not normally reliable as there are many possible text representations for the same JSON value. However, for this particular case (empty object) it works just fine:

SELECT * FROM test WHERE foo::text <> '{}'::text;

In Postgres 9.4 or later, cast to jsonb instead. (Or use jsonb to begin with.)

SELECT * FROM test WHERE foo::jsonb <> '{}'::jsonb;

Or, to cover absolutely every possibility:

SELECT * FROM test
WHERE  CASE json_typeof(foo)
          WHEN 'object' THEN foo::text <> '{}'::text
          WHEN 'array'  THEN foo::text <> '[]'::text
          ELSE true  -- other types are never "empty"
          END;

The manual for json_typeof():

Possible types are object, array, string, number, boolean, and null.

Upvotes: 180

ofir_aghai
ofir_aghai

Reputation: 3301

SELECT * FROM Table_Name WHERE JSON_LENGTH(column_name) = 0

Upvotes: -1

RonVe
RonVe

Reputation: 105

You can use the Postgres <@ (included in) operator, see docs:

select '{"a":2, "b":"hello"}'::jsonb <@ '{}'::jsonb;  -- false
select '{}'::jsonb <@ '{}'::jsonb;                    -- true

select '[{"a":1}, {"a":2}, {"a":3}]'::jsonb <@ '[]'::jsonb;  -- false
select '[]'::jsonb <@ '[]'::jsonb;                           -- true

Upvotes: 4

kapouer
kapouer

Reputation: 51

The JSON Functions in PostgreSQL 12 features jsonb_path_exists.

To avoid serializing big jsonb objects, this correctly returns true if object is not empty:

select data from block where jsonb_path_exists(data, '$ ? (exists (@.*))');

Upvotes: 1

orpheus
orpheus

Reputation: 1140

According to the JSON Functions and Operators documentation you can use the double arrow function (->>) to get a json object or array field as text. Then do an equality check against a string.

So this worked for me:

SELECT jsonb_col from my_table
WHERE jsonb_col ->> 'key' = '{}';

Or if it's nested more than one level use the path function (#>>)

SELECT jsonb_col from my_table
WHERE jsonb_col #>> '{key, nestedKey}' = '{}';

Currently supported version as of this writing:

Supported Versions: Current (13) / 12 / 11 / 10 / 9.6

Upvotes: 1

David Dehghan
David Dehghan

Reputation: 24775

You have to be careful. Casting all your data as a different type so you can compare it will have performance issues on a large database.

If your data has a consistent key then you can look for the existence of the key. For example if plan data is {} or {id: '1'}

then you can look for items without 'id'

SELECT * FROM public."user"
where NOT(plan ? 'id')

Upvotes: 8

weinerk
weinerk

Reputation: 522

Empty JSON array [] could also be relevant.

Then this could work for both [] and {}:

select * from test where length(foo::text) > 2 ;

Upvotes: 21

Pensierinmusica
Pensierinmusica

Reputation: 6950

As of PostgreSQL 9.5 this type of query with JSON data is not possible. On the other hand, I agree it would be very useful and created a request for it:

https://postgresql.uservoice.com/forums/21853-general/suggestions/12305481-check-if-json-is-empty

Feel free to vote it, and hopefully it will be implemented!

Upvotes: 6

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

In 9.3 it is possible to count the pairs in each object and filter the ones with none

create table test (foo json);
insert into test (foo) values
('{"a":1, "c":2}'), ('{"b":1}'), ('{}');

select *
from test
where (select count(*) from json_each(foo) s) = 0;
 foo 
-----
 {}

or test the existence, probably faster for big objects

select *
from test
where not exists (select 1 from json_each(foo) s);

Both techniques will work flawlessly regardless of formating

Upvotes: 3

Related Questions