postgresql - 在 plpgsql 中识别 jsonb null 的最佳实践

Question

我知道之前已经有人问过这个问题的变体:

• Why can't NULL be converted to JSON's null in postgreSQL?
• Why does JSON null not cast to SQL null in postgres?
• 也许其他人...

我无法从上述链接中了解到是否存在最佳实践。

考虑以下代码:

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

是否有更好的方法来确定 i_am_null 是否为 null？

编辑:仅供对此问题感兴趣的人引用，you might be interested in this follow-up question...

Answer 1

两个链接的答案都包含解决方案，但最好有一个综合答案。

Postgres 是强类型的。它的函数和运算符返回特定类型。

-> 返回 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)

---

->> 返回文本和 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)

---

请注意，虽然 jsonb null 只是另一个值，但 SQL null 非常特殊。 Null 不是一个值，而是缺少一个值。 Null equals nothing, not even null 。看起来将 null 转换为 jsonb 应该会生成 jsonb null，但 SQL 标准要求 null 只能转换为 null 否则这意味着 null 等同于某些内容。 p>

这就是为什么 jsonb null 可以转换为 null，但 null 不能转换为 jsonb null。 null::jsonb 为 null。这很不方便，但 SQL 标准要求这样做。这是不建议在 jsonb 和 text 之间来回转换的原因之一。