jsonpostgresqlis-empty

What is the most efficient way to test whether a json object is empty(with out any key/value) in pg?

I am trying to detect whether a json object is empty. My json data varies. Some json data have a number of key/values. so, I think jsonb_object_keys would waste time to build key list. Are there better ways to realize the conceptual function such as "hasAnyKey", "isEmpty"?

Solution

  • JSONB type supports the equality check operator. So here is the simple function.

    create or replace function jsonb_is_empty(j jsonb)
returns boolean immutable language sql parallel safe as
$$
 select j = '{}'::jsonb or j = '[]'::jsonb;
$$;

    Alternative one (for JSON)

    create or replace function json_is_empty(j json)
returns boolean immutable language sql parallel safe as
$$
 select j::jsonb in ('{}', '[]');
$$;