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

Question

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 1

You can also use the '?' operator like that:

SELECT '{"key_a":1}'::jsonb ? 'key_a' 

And if you need to query by nested key, use like this:

SELECT '{"key_a": {"nested_key": "a"}}'::jsonb -> 'key_a' ? 'nested_key'  

See http://www.postgresql.org/docs/9.5/static/functions-json.html

NOTE: Only for jsonb type.

Answer 2

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)