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?

Kostanos · Accepted Answer · 2018-02-14 02:08:05Z

175

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.

edited Feb 14, 2018 at 2:08

Kostanos

10.6k4 gold badges60 silver badges70 bronze badges

answered Nov 10, 2015 at 13:34

DaL

1,9612 gold badges11 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dennis Over a year ago

Note when using this in a service that prepares the SQL statements (e.g. Metabase), it won't work, but you might be able to use ?? instead of ?.

Josh Mc Over a year ago

This seems like a much better answer, given a key that has a value of null will come back as true, which is expected.

Bo Jeanes Over a year ago

This is the better way, as the top suggestions won't differentiate between a key being set with a NULL value and the key being absent entirely.

Akaisteph7 Apr 14 at 15:49

No idea what this is actually supposed to return but this didn't work for me and just outputted true for every row when I tried it.

X-Istence · Accepted Answer · 2015-03-08 00:36:19Z

51

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)

answered Mar 8, 2015 at 0:36

X-Istence

16.7k6 gold badges62 silver badges75 bronze badges

1 Comment

Teboto Over a year ago

Got it working, thanks! Such a simple fix yet easy to overlook. Also good catch on the name.

Talanted Vu · Accepted Answer · 2024-03-01 17:43:59Z

11

If you want to select all rows where jsonb key exists you can just use such query:

SELECT * FROM my_table WHERE my_jsonb_field->'my_key' is not null

answered Mar 1, 2024 at 17:43

Talanted Vu

1392 silver badges6 bronze badges

1 Comment

Akaisteph7 Apr 14 at 15:51

Make sure to use single quotes!

jian · Accepted Answer · 2022-03-22 16:28:26Z

6

PostgreSQL 14 add subscripting feature for json/jsonb.

SELECT ('{"key_a":1}'::jsonb) ['key_a'] is not null;

return t.

answered Mar 22, 2022 at 16:28

jian

5,0131 gold badge29 silver badges46 bronze badges

Comments

Dennis · Accepted Answer · 2018-09-12 10:40:53Z

4

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

edited Sep 12, 2018 at 10:40

Dennis

60.1k28 gold badges148 silver badges143 bronze badges

answered Aug 16, 2018 at 15:45

Piyush Sharma

6618 silver badges9 bronze badges

Comments

zoheir maz · Accepted Answer · 2021-01-27 10:02:17Z

3

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)

answered Jan 27, 2021 at 10:02

zoheir maz

1471 silver badge5 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

4 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related