我想检查一个键是否存在于JSON中作为PL/pgSQL函数的参数发送。
函数如下:
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
子句中的语句。
我尝试PERFORM user_info->>'firstname' INTO myvar;
,以检查变量内容,但它触发错误ERROR: query "SELECT user_info->>'firstname' INTO myvar" is not a SELECT
。
如何检查json中是否存在PL/pgSQL的json键?
您已经发现可以测试表达式user_info->>'username'
是否为NULL。但是你的函数仍然非常低效。而且仍然存在歧义。
Postgres 9.3中更好的解决方案
为多个列重复更新一行是非常昂贵的。Postgres为每次更新写入一个新的行版本。如果可能的话,使用单个 UPDATE
:
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}'
。如果有时表中的值已经是要更改的值,则可能进行另一种优化。考虑一下这个相关答案的最后一段:
- 我如何(或我可以)在多列上选择DISTINCT ?
您的原始文件中缺少变量/参数
user_id
仍然有一个角case 歧义。如果元素存在并且被设置为JSON
null
,那么您还会得到一个SQLNULL
作为结果。考虑: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
…
Postgres 9.4
在Postgres 9.4中有一个干净的和简单的解决方案, jsonb
与 ?
"存在"操作符,它甚至可以使用索引为更大的表(与你的函数无关):
SELECT ('{"b": null}'::jsonb ? 'b') AS b_is_null
, ('{"c": 2}'::jsonb ? 'b') AS b_missing;
和?|
和?&
变体一次检查多个键。
所以我们可以实现:
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;
这些调用现在按预期工作:
SELECT sp_update_user(123, '{"firstname": null, "lastname": "doe1"}'::jsonb);
SELECT sp_update_user(123, '{"firstname": "doris"}'::jsonb);
可以使用"json_object_keys"函数作为解决方案。
您可以将使用此函数的查询结果选择到声明的变量中,然后检查变量是否为空。下面是一个例子:
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;
找到一个更简单的解决方案,检查json键是否有值。
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;
检查json中的键'username'是否有一个非NULL的卷
另一种检查JSON键是否存在的方法是使用json_extract_path()
。下面是一个例子:
SELECT permit_id FROM building_permits WHERE
json_extract_path(containing_boundaries, 'neighborhood') IS NULL
在JSON对象("containing_boundaries")中查找一个名为"neighborhood"的属性。如果属性不存在,则返回NULL。