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

Solution 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.

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

Solution 3:

To check key exists or not you can use the operator -> this is used to get Get JSON object field by key For example:

actual json data in column(attribute): {
    "active": "t",
    "email_address": "[email protected]",
    "pin": "2233"
}

SELECT attributes::json->'email_address'
FROM entity
WHERE entity_id = 55;

You can also search key via operator #> and #>>

Get JSON object field as text: '{"a":1,"b":2}'::json->>'b' by using the operator ->>

Solution 4:

In Postgres, if you select a key that does not exist it will return null. so u can check the existence of a key by checking the null value of that key.

select '{"key_a": {"nested_key": "a"},
    "key_b": {"nested_key": "b"}}'::jsonb->>'a'
------------
null
(1 row)