Teboto Teboto - 2 months ago 31
JSON Question

How do I check if a json key exists in Postgres?

Let's say I have a json that looks like this:

some_json = {'key_a': {'nested_key': 'a'},
'key_b': {'nested_key': 'b'}}


Note that
key_a
and
key_b
are optional keys mapped to dictionaries and may or may not exist.

I have a function that checks if an outer key exists in
some_json
and returns a boolean.

CREATE FUNCTION key_exists(some_json json, outer_key text)
RETURNS boolean AS $$
BEGIN
RETURN (some_json->outer_key IS NULL);
END;
$$ LANGUAGE plpgsql;


I get the following error:

ProgrammingError: operator does not exist: json -> boolean


Why is
outer_key
equating to a boolean? What's the proper syntax to perform this check?

Answer Source

Your function does the exact opposite of what the name is, but the way to fix your function is to add ( and ) around the some_json->outer_key.

Here is it fully functioning, and matching the name of your function (notice the NOT in front of the NULL).

CREATE FUNCTION key_exists(some_json json, outer_key text)
RETURNS boolean AS $$
BEGIN
    RETURN (some_json->outer_key) IS NOT NULL;
END;
$$ LANGUAGE plpgsql;

Some tests:

select key_exists('{"key_a": {"nested_key": "a"}, "key_b": {"nested_key": "b"}}'::json, 'key_a');
 key_exists 
------------
 t
(1 row)

And here when a key doesn't exist:

select key_exists('{"key_a": {"nested_key": "a"}, "key_b": {"nested_key": "b"}}'::json, 'test');
 key_exists 
------------
 f
(1 row)