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.

Erwin Brandstetter · Accepted Answer · 2021-05-19T16:49:16.660

162

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 json_column ... or ... GROUP BY json_column fail for the same reason (no equality operator).

Casting both sides of the expression to text allows = or <> operators, but that's not normally reliable as there are many possible text representations for the same JSON value. In Postgres 9.4 or later, cast to jsonb instead. (Or use jsonb to begin with.)

However, for this particular case (empty object) it works just fine:

select * from test where foo::text <> '{}'::text;

edited May 19 '21 at 16:49

answered Jun 18 '14 at 22:32

Erwin Brandstetter

605,456
145
1,078
1,228

4

Probably obvious but this works for empty arrays as well just replace the {} with [] – hobberwickey Dec 19 '14 at 18:34
3

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" }` – Dynom Dec 10 '15 at 12:56
This is pretty terrible where there are lots of rows, and each `foo` is a large structure; each one is coerced to text! – EoghanM Apr 08 '16 at 10:29
When i'm using this i didn' want to add '{}'::text. Is it alright? (I didn't used the foo::text also) – Kavinda Jayakody Aug 07 '19 at 07:32

score 16 · Answer 2 · answered May 30 '19 at 17:13

16

Empty JSON array [] could also be relevant.

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

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

answered May 30 '19 at 17:13

weinerk

472
6
12

score 7 · Answer 3 · answered May 08 '20 at 21:25

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

score 6 · Answer 4 · answered Feb 12 '16 at 16:41

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!

Clodoaldo Neto · Answer 5 · 2014-07-25T21:28:17.063

3

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

edited Jul 25 '14 at 21:28

answered Jul 25 '14 at 21:15

Clodoaldo Neto

118,695
26
233
260

Why the `s` after the json_each call in these examples? What purpose does it serve? – Stratus3D Aug 24 '17 at 16:04
@Stratus3D It is the mandatory alias for a subquery, in this case a function. – Clodoaldo Neto Aug 24 '17 at 16:36
this also works for arrays by replacing `json_each` with `json_array_elements` – Anentropic Mar 15 '18 at 11:33

score 1 · Answer 6 · answered Apr 15 '21 at 16:14

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

score 1 · Answer 7 · answered Aug 10 '23 at 21:28

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

score 0 · Answer 8 · answered Apr 18 '23 at 10:04

0

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

answered Apr 18 '23 at 10:04

kapouer

41
2

score 0 · Answer 9 · answered Aug 14 '23 at 10:28

0

SELECT * FROM Table_Name WHERE JSON_LENGTH(column_name) = 0

answered Aug 14 '23 at 10:28

ofir_aghai

3,017
1
37
43

How to query a json column for empty objects?

9 Answers9

Linked