在 plpgsql 中识别 jsonb null 的最佳实践 [英] Best practice to identify a jsonb null in plpgsql
问题描述
我知道之前有人问过这个问题的变体:
我无法从上述链接中了解到是否有最佳实践.
考虑以下代码:
做$$宣布_main_jsonb jsonb = '{i_am_null":null，a_string":null"}';_sub_jsonb jsonb;开始SELECT (_main_jsonb->'i_am_null') INTO _sub_jsonb;如果 _sub_jsonb 为 NULL THENRAISE INFO '这一点*未*达到.坏的?';万一;-- 这是我真正感兴趣的部分SELECT (_main_jsonb->>'i_am_null')::jsonb INTO _sub_jsonb;如果 _sub_jsonb 为 NULL THENRAISE INFO '这一点*已达到*.好的.';万一;-- 这是我真正感兴趣的部分SELECT (_main_jsonb->>'a_string')::jsonb INTO _sub_jsonb;如果 _sub_jsonb 为 NULL THENRAISE INFO '奖励积分.这一点*未*达到.好的.';万一;结尾;$$是否有更好的方法来确定 i_am_null 是否为空?
仅供对此问题感兴趣的人参考，您可能对这个后续问题感兴趣...
您链接的两个答案都包含解决方案，但最好有一个综合答案.
Postgres 是强类型的.它的函数和运算符返回特定类型.
-> 返回 jsonb.比较它不是 SQL null 而是 jsonb null.
test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->'i_am_null' = 'null'::jsonb;?柱子?----------吨(1 行)test=# select '{i_am_null":null，a_string":null"}'::jsonb->'a_string' = 'null'::jsonb;?柱子?----------F(1 行)->> 返回文本，将 jsonb null 转换为 SQL null.
test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->>'i_am_null' is null;?柱子?----------吨(1 行)test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->>'a_string' is null;?柱子?----------F(1 行)请注意，虽然 jsonb null 只是另一个值，但 SQL null 非常特殊.Null 不是一个值，它是一个值的缺失.空等于零，甚至不等于空.似乎将 null 转换为 jsonb 应该会产生 jsonb null，但 SQL 标准要求 null 仅转换为 null 否则这意味着 null 等同于某物.>
这就是为什么可以将 jsonb null 转换为 null，而 null 不会转换为 jsonb null.null::jsonb 为空.这很不方便，但 SQL 标准要求这样做.这也是不推荐在 jsonb 和 text 之间来回转换的原因之一.
I'm aware that variants of this question have been asked before:
- Why can't NULL be converted to JSON's null in postgreSQL?
- Why does JSON null not cast to SQL null in postgres?
- perhaps others...
What I wasn't able to glean from the above links is whether there is a best practice.
Consider the following code:
DO
$$
DECLARE
_main_jsonb jsonb = '{"i_am_null": null, "a_string": "null"}';
_sub_jsonb jsonb;
BEGIN
SELECT (_main_jsonb->'i_am_null') INTO _sub_jsonb;
IF _sub_jsonb IS NULL THEN
RAISE INFO 'This point *not* reached. Bad?';
END IF;
-- THIS IS THE PART I AM REALLY INTERESTED IN
SELECT (_main_jsonb->>'i_am_null')::jsonb INTO _sub_jsonb;
IF _sub_jsonb IS NULL THEN
RAISE INFO 'This point *is* reached. Good.';
END IF;
-- THIS IS THE PART I AM REALLY INTERESTED IN
SELECT (_main_jsonb->>'a_string')::jsonb INTO _sub_jsonb;
IF _sub_jsonb IS NULL THEN
RAISE INFO 'Bonus points. This point *not* reached. Good.';
END IF;
END;
$$
Is there a better way to figure out if i_am_null is null?
Edit: FYI to those interested in this question, you might be interested in this follow-up question...
Both of your linked answers contain solutions, but it might be good to have an omnibus answer.
Postgres is strongly typed. Its functions and operators return specific types.
-> returns jsonb. Compare it not to SQL null but jsonb null.
test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->'i_am_null' = 'null'::jsonb;
?column?
----------
t
(1 row)
test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->'a_string' = 'null'::jsonb;
?column?
----------
f
(1 row)
->> returns text and will convert jsonb null into SQL null.
test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->>'i_am_null' is null;
?column?
----------
t
(1 row)
test=# select '{"i_am_null": null, "a_string": "null"}'::jsonb->>'a_string' is null;
?column?
----------
f
(1 row)
Note that while jsonb null is just another value, SQL null is very special. Null is not a value, it is the lack of a value. Null equals nothing, not even null. It might seem like casting null to jsonb should produce jsonb null, but the SQL standard requires that null only casts to null otherwise that would mean null is equivalent to something.
This is why jsonb null can be converted to null, but null is not cast to jsonb null. null::jsonb is null. This is inconvenient, but required by the SQL standard. It is one of the reasons casting back and forth between jsonb and text is not recommended.
这篇关于在 plpgsql 中识别 jsonb null 的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
