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

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.

Probably obvious but this works for empty arrays as well just replace the {} with []
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" }
This is pretty terrible where there are lots of rows, and each foo is a large structure; each one is coerced to text!
When i'm using this i didn' want to add '{}'::text. Is it alright? (I didn't used the foo::text also)

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

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

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

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!

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

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

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

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

Why the s after the json_each call in these examples? What purpose does it serve?
@Stratus3D It is the mandatory alias for a subquery, in this case a function.
this also works for arrays by replacing json_each with json_array_elements

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

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

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

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

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

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

Linked

Related