json - 为什么在 JSONB 中提取显式 `null` 作为文本会产生 SQL `null` ？

Question

我试图了解如何处理 PostgreSQL 的 jsonb 类型中的 null。因为

# select 'null'::jsonb is null;
 ?column? 
----------
 f
(1 row)

我假设它们不同于 SQL null(这是有道理的)- according to the manual ,

SQL NULL is a different concept.

因此，这两个查询一点也不奇怪:

# select '{"a": 1, "b": null}'::jsonb->'b' is null;
 ?column? 
----------
 f
(1 row)

# select '{"a": 1, "b": null}'::jsonb->'c' is null;
 ?column? 
----------
 t
(1 row)

根据 the manual :

The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such element exists.

然而，惊喜开始的地方是:

# select '{"a": 1, "b": null}'::jsonb->>'b' is null;
 ?column? 
----------
 t
(1 row)

# select '{"a": 1, "b": null}'::jsonb->>'c' is null;
 ?column? 
----------
 t
(1 row)

后一个我能理解 - 我们从提取中得到一个 SQL null，并将 null 转换为 text 将其保留为 null - 我假设 ->> 以这种方式工作，因为 the manual说

The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text.

(顺便说一句，我找不到确认将 SQL null 转换为任何其他类型会在 PostgreSQL 中再次产生 null - 它是否明确地写在某个地方？)

但前者对我来说是个谜。提取应该给我一个 jsonb null，我认为转换为 text 应该给我 'null'(即，表示“null”的字符串)，就像

# select ('null'::jsonb)::text;
 text 
------
 null
(1 row)

但是它返回一个正确的 SQL null。

为什么会这样？

Answer 1

在某种程度上这是实现者的意见；在 JSON 数据类型和 SQL 数据类型之间转换时，并不总是能够找到完美的对应关系，尤其是因为 SQL NULL 太奇怪了。

但是它的实现方式有一定的逻辑。

SELECT (JSONB '{"a": "null"}' -> 'a')::text,
       (JSONB '{"a": null}' -> 'a')::text;

  text  | text 
--------+------
 "null" | null
(1 row)

转换为 text 总是会产生一个结果，当转换回原始类型时，会产生原始值。这是 PostgreSQL 的设计原则。

因此 JSON 字符串 "null" 和 JSON null 将被转换为不同的字符串。

现在看看这个:

SELECT JSONB '{"a": "null"}' ->> 'a',
       JSONB '{"a": null}' ->> 'a';

 ?column? | ?column? 
----------+----------
 null     | 
(1 row)

在这里，与上面的转换不同，PostgreSQL 试图在 SQL 中找到最接近 JSON 值的等价物。您不希望字符串 "null" 保留其双引号，因为这在 SQL 中是完全不同的字符串，对吗？

但另一方面，如果 "null" 和 null 在 SQL 中以相同的方式表示，感觉也不对，对吗？

据我所知，JSON null的意思是“不存在”，这是SQL NULL的含义之一。此外，具有值为 null 的 JSON 属性意味着与省略该属性大致相同，不是吗？

因此，尽管存在争论的余地，但我认为它的实现方式背后有一些韵律和原因。