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 · 2023-12-19 20:48:52Z

181

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.

edited Dec 19, 2023 at 20:48

answered Jun 18, 2014 at 22:32

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

hobberwickey Over a year ago

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

Dynom Over a year ago

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" }

EoghanM Over a year ago

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

Kavinda Jayakody Over a year ago

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

Erwin Brandstetter Over a year ago

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

|

weinerk · Accepted Answer · 2019-05-30 17:13:40Z

21

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, 2019 at 17:13

weinerk

5426 silver badges13 bronze badges

2 Comments

MasterJoe Over a year ago

Does this work for JSONB columns also?

weinerk Over a year ago

yes, but not sure if this is optimal for jsonb

David Dehghan · Accepted Answer · 2020-05-08 21:25:10Z

8

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

answered May 8, 2020 at 21:25

David Dehghan

25.3k11 gold badges113 silver badges101 bronze badges

1 Comment

Juan Over a year ago

Although this didn't work for me, it helped. I ended up using SELECT * FROM table t WHERE (t.json->0) IS NOT NULL, on PostgreSQL 9.4.4.

Pensierinmusica · Accepted Answer · 2016-02-12 16:41:04Z

6

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!

answered Feb 12, 2016 at 16:41

Pensierinmusica

7,18810 gold badges44 silver badges60 bronze badges

Comments

RonVe · Accepted Answer · 2023-08-10 21:28:07Z

5

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

answered Aug 10, 2023 at 21:28

RonVe

1151 silver badge8 bronze badges

Comments

Clodoaldo Neto · Accepted Answer · 2014-07-25 21:28:17Z

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, 2014 at 21:28

answered Jul 25, 2014 at 21:15

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

3 Comments

Stratus3D Over a year ago

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

Clodoaldo Neto Over a year ago

@Stratus3D It is the mandatory alias for a subquery, in this case a function.

Anentropic Over a year ago

this also works for arrays by replacing json_each with json_array_elements

orpheus · Accepted Answer · 2021-04-15 16:14:24Z

1

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

answered Apr 15, 2021 at 16:14

orpheus

1,1602 gold badges17 silver badges24 bronze badges

Comments

kapouer · Accepted Answer · 2023-04-18 10:04:24Z

1

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, 2023 at 10:04

kapouer

512 bronze badges

Comments

ofir_aghai · Accepted Answer · 2023-08-14 10:28:19Z

-1

SELECT * FROM Table_Name WHERE JSON_LENGTH(column_name) = 0

answered Aug 14, 2023 at 10:28

ofir_aghai

3,3411 gold badge40 silver badges43 bronze badges

1 Comment

kolypto Over a year ago

Function json_length() does not exist. Check this out: SELECT json_length('{"a":1}'::json); -> ERROR: function json_length(json) does not exist

Juan · Accepted Answer · 2024-05-20 22:07:29Z

-1

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.

answered May 20, 2024 at 22:07

Juan

1,4411 gold badge16 silver badges30 bronze badges

2 Comments

kolypto Over a year ago

This ->0 check only tests for empty array. Not empty object.

Juan Over a year ago

@kolypto I found that ->0 can result in NULL, so I used IS NOT NULL, as you can see. What is a better alternative for PostgreSQL 9.4.4?

Collectives™ on Stack Overflow

How to query a json column for empty objects?

10 Answers 10

6 Comments

2 Comments

1 Comment

Comments

Comments

3 Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

6 Comments

2 Comments

1 Comment

Comments

Comments

3 Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related