How to query a json column for empty objects?

Question

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.

Andreasandree

Answer 1

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

• How to remove known elements from a JSON[] array in PostgreSQL?

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.

Quinine

Probably obvious but this works for empty arrays as well just replace the {} with [] – Ginetteginevra

If you're looking for nested structures, the following might be something you could use: select * from test where foo->>'property' = '[]'; where the structure might be something like: { "property": [], "foo": "bar" } – Florrieflorry

This is pretty terrible where there are lots of rows, and each foo is a large structure; each one is coerced to text! – Ahmadahmar

When i'm using this i didn' want to add '{}'::text. Is it alright? (I didn't used the foo::text also) – Riojas

How do you you use jsonb instead i.e. what is the query? – Diffluent

@MasterJoe: For jsonb: ... WHERE foo = '{}'; – Quinine

Answer 2

Empty JSON array [] could also be relevant.

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

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

Chape

Does this work for JSONB columns also? – Diffluent

yes, but not sure if this is optimal for jsonb – Chape

Answer 3

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

Medawar

Answer 4

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!

Hays

Answer 5

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

Philomenaphiloo

Why the s after the json_each call in these examples? What purpose does it serve? – Sabbath

@Sabbath It is the mandatory alias for a subquery, in this case a function. – Philomenaphiloo

this also works for arrays by replacing json_each with json_array_elements – Recruit

Answer 6

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

Lutenist

Answer 7

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

Torpedo

Answer 8

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 (@.*))');

Tega

Answer 9

SELECT * FROM Table_Name WHERE JSON_LENGTH(column_name) = 0

Physicality