使用PL/pgSQL检查密钥是否存在于JSON中? [英] Check if key exists in a JSON with PL/pgSQL?

问题描述

我正在尝试检查在PL/pgSQL函数中作为参数发送的JSON中是否存在密钥.

I'm trying to check if a key exists in a JSON sent as parameter in a PL/pgSQL 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;$$;

我尝试了PERFORM子句，但是即使json键不存在，也会执行IF FOUND子句中的语句.

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

我尝试了PERFORM user_info->>'firstname' INTO myvar;以便检查变量内容，但它会触发错误ERROR: query "SELECT user_info->>'firstname' INTO myvar" is not a SELECT.

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.

如何使用PL/pgSQL检查JSON中是否存在JSON密钥?

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

推荐答案

您已经发现可以测试表达式user_info->>'username'是否为NULL.但是您的功能仍然效率很低.而且仍然存在歧义.

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.

为多列重复更新一行是昂贵的. Postgres为每次更新编写一个新的行版本.尽可能使用单个 UPDATE:

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;

致电:

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

• 对于多列而言，这实际上更快，因为仅执行了一个UPDATE(最多).如果WHERE子句的计算结果不为true，则根本不会进行任何更新，结果是'{"success":false}'.

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

    • 我如何(或者我可以)在多个列上选择DISTINCT吗?

    原始变量中缺少变量/参数user_id.

    The variable / parameter user_id is missing in your original.

    仍然存在一个极端的情况，即歧义.如果该元素存在并将其设置为 JSON null ，您还将得到一个SQL NULL作为结果.考虑:

    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;
    

  • 不知道为什么要使用数据类型json作为返回类型，我只是保留了这一点.但是，如果该函数未更新，则无法确定为什么得到false.给定的id可能没有行，键名'firstname'和'lastname'可能丢失-或为null ...

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

    
    

    在Postgres 9.4 中，使用 jsonb 和 -甚至可以将索引用于更大的表(与您的函数无关):

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

    这篇关于使用PL/pgSQL检查密钥是否存在于JSON中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！