历史

PostgreSQL支持两种类型的JSON实现：JSON和JSONB。JSON的第一个实现已于2012年在PostgreSQL 9.2中发布。两年后，JSONB已在PostgreSQL9.4中添加。

仍然有两种不同的JSON标准。一个是Douglas Crockford的RFC7159,另一个是ECMA 404。

PostgreSQL使用的是RFC7159。与ANSI SQL标准相比，该标准通常不是真正的人类可读标准，该标准简短易懂，只有15页。

2017年3月，ISO发布了新标准ISO/IEC TR 19075-6:2017，定义了如何在SQL数据库中实现JSON。本文件仅适用于货币。

不久之后，ISO发布了另一份文档，解释了SQL/JSON标准。文件ISO/IEC TR 19075-6免费提供。通过本附加文档，更容易理解ISO/IEC TR 19075-6:2017标准。

ISO/IEC TR 19075-6:2017标准的另一部分是SQL/JSON路径语言，该语言已于2019年在PostgreSQL 12中实现。

它提供了查询JSON对象的更多可能性。

JSON

JSON的实现非常简单。每个有效的JSON对象都被接受。通过使用TOAST，每列的最大数据量为1 GB。

JSONB

JSONB是一个更复杂的实现。它对内部的路径和值进行排序。此外，重复项也将被删除，因为根据RFC7159，只能获得密钥最后一次出现的值。尽管JSONB只处理最后一个键，并删除具有相同名称的前一个键。此外，JSONB中的所有换行符和所有不必要的空格都将被删除。

-- JSON
SELECT '{
  "bar": "baz",
  "num": "one",
  "num":"two",
  "active":false
 }'::json;

结果：{“bar”：“baz”，“num”：“one”，“num：：“two”，“active”：false}

-- JSONB
SELECT '{
 "bar": "baz",
 "num": "one",
 "num":"two",
 "active":false
}'::jsonb;

结果：{“bar”：“baz”，“num”：“two”，“active”：false}

如上面的示例所示，JSONB中只出现了一个键“num”，而这两个键都是用JSON返回的。

这种行为使JSONB能够被索引，因为每个路径在其结构中都是唯一的。这里可以使用的索引类型是GIN。

通过使用TOAST，每列的最大数据量为1 GB。

提示

为什么是JSON？

PostgreSQL还支持XML数据类型，并包括多个函数，但JSON的优势在于它可以处理使用的代码页。这已经是标准的一部分，它是UTF。PostgreSQL将JSON数据类型实现为UTF-8。

何时不使用JSON

当您将相同的数据结构写入JSON/JSONB列时，请考虑将数据存储在关系表中。检查您一直想要的字段要容易得多。它也比解析JSON对象更快，以确保它有一个具有有效值的特定键。

例如，如果您期望值的类型为整数或在范围内。与JSON对象相比，使用专用数据类型更容易检查。

选择正确的JSON类型

许多函数可以处理这两种数据类型。其中一些是在JSONB中使用尾随b实现的。

如果您计划在数据库中除了插入和读取整个JSON对象之外什么都不做，请使用JSON。如果您需要归档JSON数据，这也是一个很好的解决方案，因为它是按原样存储的，只要它是有效的JSON。

但是，如果您想对JSON对象执行一些SQL魔术，那么JSONB是数据类型的选择。当使用GIN索引对JSONB列进行索引时，它提供了更多的可能性，并且速度更快。

可读的JSONB

JSONB数据类型中没有用于缩进的换行符和空格，而JSON数据类型返回存储的数据，对于我们人类来说，从JSONB读取结果有点困难，因为它只有一行。

在PostgreSQL 9.5中，函数jsonb_pretty已经实现。这个函数使我们现在能够阅读和理解来自JSONB列的JSON对象的内容。

SELECT
  jsonb_pretty (
    '[{"f1":1,"f2":null}, 2]'::jsonb
  );
/*
RESULT:
[
    {
        "f1": 1,
        "f2": null
    },
    2
]
*/

该函数只添加换行符和缩进。

时间/日期/日期时间

JSON不支持任何日期或时间数据类型。仅支持以下类型：

• string
• number
• object
• array
• true
• false
• null

PostgreSQL动态关联它将在这里使用的数据类型。JSON中的标准是手动处理日期/时间/日期时间，因为它是一个字符串，因此几个JSON库为这种数据类型实现了一个处理程序。

以空结尾的字符串

最近，在Telegram上的一次聊天中，一个家伙提到，使用哪种数据类型来存储JSON数据，两种PostgreSQL JSON类型都不存储以空结尾的字符串，如果您尝试，它们会返回一条错误消息。

但在我看来，这并不重要，因为JSON可以处理的字符类型在JSON标准中定义得很好。由于未提及这些类型，因此它们不符合标准。尽管如此，一些语言还是使用空终止的字符串来实现。由于标准，我仍然会避免使用它们。

SELECT '{
  "a_json_object": "with_a_\u0000_byte"
 }'::jsonb;

结果：错误：不支持的Unicode转义序列

SELECT E'{"
  a_json_object": "with_a_\u0000_byte"
 }'::json;

结果：“\u0000”处或附近的Unicode转义值无效

尽管如此，PostgreSQL做的一切都是正确的，并实现了定义的JSON标准。返回准确指向问题的错误消息是正确的最佳解决方案。

原文标题：PostgreSQL JSON versus JSONB
原文作者：Stefanie Janine Stölting
原文链接：https://proopensource.it/blog/json-versus-jsonb