Check if key exists in a JSON with PL/pgSQL?

Question

I'm trying to check if a key exists in a JSON sent as parameter in a PL/pgSQL function.

Here is the function.

CREATE FUNCTION sp_update_user(user_info json) RETURNS json
    LANGUAGE plpgsql
    AS $$
    BEGIN
    PERFORM user_info->>'lastname';
    IF FOUND
    THEN
      UPDATE users SET lastname = user_info->>'lastname' WHERE id = sp_update_user.user_id;
    END IF;

    PERFORM user_info->>'firstname';
    IF FOUND
    THEN
      UPDATE users SET firstname = user_info->>'firstname' WHERE id = sp_update_user.user_id;
    END IF;

    RETURN row_to_json(row) FROM (SELECT true AS success) row;
END;$$;

I tried with the PERFORM-clause, but even if the json key does not exists, the statements in the IF FOUND clause are executed.

I tried PERFORM user_info->>'firstname' INTO myvar; in order to check the variable content but it triggers an error ERROR: query "SELECT user_info->>'firstname' INTO myvar" is not a SELECT.

How can I check if a json key exists in a JSON with PL/pgSQL?

score 11 · Accepted Answer · edited May 23 '17 at 11:47

You already found that you can test the expression user_info->>'username' for NULL. But your function is still very inefficient. And there are still ambiguities.

Better solution in Postgres 9.3

It is expensive to update a row repeatedly for multiple columns. Postgres writes a new row version for every update. Use a single UPDATE if at all possible:

CREATE OR REPLACE FUNCTION sp_update_user(_user_id int, _user_info json)
  RETURNS json AS
$func$
BEGIN
   UPDATE users u
   SET    firstname = COALESCE(_user_info->>'firstname', u.firstname)
        , lastname  = COALESCE(_user_info->>'lastname' , u.lastname)
   WHERE  id = sp_update_user._user_id
   AND   ((_user_info->>'firstname') IS NOT NULL OR
          (_user_info->>'lastname')  IS NOT NULL);

   IF FOUND THEN
      RETURN '{"success":true}'::json;
   ELSE
      RETURN '{"success":false}'::json;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT sp_update_user(123, '{"firstname": "jon", "lastname": "doe"}')

This is substantially faster for multiple columns, since only a single UPDATE (at most) is executed. If the WHERE clause doesn't evaluate to true, no update happens at all and you get '{"success":false}' as result.
If sometimes the values in the table are already what they are being changed to, another optimization is possible. Consider the last paragraph of this related answer:
- How do I (or can I) SELECT DISTINCT on multiple columns?
The variable / parameter user_id is missing in your original.
There is still a corner case ambiguity. If the element exists and is set to JSON null, you also get an SQL NULL as result. Consider:
```
SELECT ('{"b": null}'::json->>'b') IS NULL AS b_is_null
     , ('{"c": 2}'::json->>'b')    IS NULL AS b_missing;
```
Not sure why you use data type json as return type, I just kept that. But if the function does not update, you cannot be sure why you get false. There might be no row with the given id, the key names 'firstname' and 'lastname' could be missing - or be null ...

Superior solution in Postgres 9.4

There is a clean and simple solution in Postgres 9.4 with jsonb with the ? "existence" operator - which can even use an index for bigger tables (not relevant in your function):

SELECT ('{"b": null}'::jsonb ? 'b') AS b_is_null
     , ('{"c": 2}'::jsonb ? 'b')    AS b_missing;

And the ?| and ?& variants to check for multiple keys at once.
So we can implement:

CREATE OR REPLACE FUNCTION sp_update_user(_user_id int, _user_info jsonb)
  RETURNS jsonb AS
$func$
BEGIN
   UPDATE users u
   SET    firstname = CASE WHEN _user_info ? 'firstname' THEN _user_info->>'firstname' ELSE u.firstname END
        , lastname  = CASE WHEN _user_info ? 'lastname'  THEN _user_info->>'lastname'  ELSE u.lastname  END
   WHERE  id = sp_update_user._user_id
   AND    _user_info ?| '{firstname,lastname}';

   IF FOUND THEN
      RETURN '{"success":true}'::jsonb;
   ELSE
      RETURN '{"success":false}'::jsonb;
   END IF;
END
$func$  LANGUAGE plpgsql;

These calls work as expected now:

SELECT sp_update_user(123, '{"firstname": null, "lastname": "doe1"}'::jsonb);
SELECT sp_update_user(123, '{"firstname": "doris"}'::jsonb);

Thank you very much for this answer, it is way much better than anything I've found before ! I'm seriously considering an update to Postgres 9.4... — Eric Ly, Jun 09 '15 at 19:56
@DeadEye: if you have to deal with JSON a lot that's an excellent idea. — Erwin Brandstetter, Jun 09 '15 at 20:27

score 2 · Answer 2 · answered Jun 05 '15 at 16:47

The "json_object_keys" function can be a solution.

You can select the result of a query that uses this function into a declared variable and then check if your variable is null. Here is an example:

DECLARE
test character varying;
BEGIN
    SELECT a INTO test FROM json_object_keys(user_info) a WHERE a = 'lastname';
    IF (test IS NOT NULL) THEN
        --SUCESSS
    ELSE
        --FAIL
    END IF;
END;

This solution is pretty fine ! Thank you ! – Eric Ly Jun 05 '15 at 18:43 — Eric Ly, Jun 05 '15 at 18:43

score 0 · Answer 3 · answered Jun 05 '15 at 19:28

Found a simpler solution that checks if the json key have a value.

IF (user_info->>'username') IS NOT NULL
THEN
      RAISE NOTICE 'username';
      UPDATE users SET username = user_info->>'username' WHERE id = sp_update_user.user_id;
END IF;

This checks if the key 'username' in the json have a volume other than NULL.

score 0 · Answer 4 · answered Jan 09 '16 at 20:41

Another way to check if a JSON key exists is with json_extract_path(). Here's an example use:

SELECT permit_id FROM building_permits WHERE
json_extract_path(containing_boundaries, 'neighborhood') IS NULL

This looks for a property in your JSON object ("containing_boundaries") called "neighborhood". If the property does not exist, it returns NULL.

Check if key exists in a JSON with PL/pgSQL?

4 Answers4

Better solution in Postgres 9.3

Superior solution in Postgres 9.4

Linked