8.14. JSON 类型

• 8 .14.1. JSONImporting 和输出语法

• 8 .14.2. 有效设计 JSON 文档

• 8 .14.3. jsonb 包含与存在

• 8 .14.4. jsonb 索引

• 8.14.5. Transforms

JSON 数据类型用于存储RFC 7159中指定的 JSON(JavaScript 对象表示法)数据。这样的数据也可以存储为text，但是 JSON 数据类型的优点是可以根据 JSON 规则强制每个存储的值都是有效的。对于这些数据类型中存储的数据，还提供了各种特定于 JSON 的函数和运算符。参见Section 9.15。

有两种 JSON 数据类型：json和jsonb。他们接受几乎*相同的值集作为 Importing。实际的主要区别是效率之一。 json数据类型存储 Importing 文本的精确副本，处理功能必须在每次执行时重新解析；而jsonb数据以分解后的二进制格式存储，由于增加了转换开销，因此 Importing 速度稍慢，但由于不需要解析，因此处理速度明显加快。 jsonb还支持索引编制，这可能是一个很大的优势。

因为json类型存储了 Importing 文本的精确副本，所以它将保留令牌之间语义上无关紧要的空白以及 JSON 对象中键的 Sequences。另外，如果值中的 JSON 对象包含相同的键不止一次，则所有键/值对都会保留。 (处理功能将最后一个值视为有效值.)相反，jsonb不会保留空格，不会保留对象键的 Sequences，也不会保留重复的对象键。如果在 Importing 中指定了重复的键，则仅保留最后一个值。

通常，大多数应用程序应该将 JSON 数据存储为jsonb，除非有非常特殊的需求，例如关于对象键 Sequences 的传统假设。

PostgreSQL 每个数据库只允许一种字符集编码。因此，除非数据库编码为 UTF8，否则 JSON 类型不可能严格符合 JSON 规范。尝试直接包含数据库编码中无法表示的字符会失败；相反，将允许使用可以在数据库编码中表示但不能以 UTF8 表示的字符。

RFC 7159 允许 JSON 字符串包含\uXXXX表示的 Unicode 转义序列。在json类型的 Importing 函数中，无论数据库编码如何，都允许 Unicode 转义，并且仅检查语法正确性(即，四个十六进制数字在\u之后)。但是，jsonb的 Importing 函数更为严格：除非数据库编码为 UTF8，否则它将不允许非 ASCII 字符(U+007F以上的字符)使用 Unicode 转义。 jsonb类型也拒绝\u0000(因为在 PostgreSQL 的text类型中不能表示)，并且坚持认为使用 Unicode 替代对来指定 Unicode Basic Multilingual Plane 之外的字符都是正确的。有效的 Unicode 转义会转换为等效的 ASCII 或 UTF8 字符进行存储；这包括将代理对折叠为单个字符。

当将文本 JSONImporting 转换为jsonb时，RFC 7159 描述的原始类型将有效地 Map 到本地 PostgreSQL 类型，如Table 8.23所示。因此，对于构成有效jsonb数据的内容存在一些较小的附加约束，这些约束不适用于json类型或抽象的 JSON，这对应于基础数据类型可以表示的内容的限制。值得注意的是，jsonb会拒绝 PostgreSQL numeric数据类型范围之外的数字，而json不会。 RFC 7159 允许此类实现定义的限制。但是，实际上，在其他实现中更容易出现此类问题，因为通常将 JSON 的numberPrimitives 类型表示为 IEEE 754 双精度浮点(RFC 7159 明确预期)并允许)。当使用 JSON 作为此类系统的交换格式时，应考虑与 PostgreSQL 最初存储的数据相比失去数字精度的危险。

相反，如表中所示，JSON 基本类型的 Importing 格式有一些较小的限制，不适用于相应的 PostgreSQL 类型。

表 8.23. JSON 基本类型和相应的 PostgreSQL 类型

8 .14.1. JSONImporting 和输出语法

JSON 数据类型的 Importing/输出语法在 RFC 7159 中指定。

以下是所有有效的json(或jsonb)表达式：

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

如前所述，当 ImportingJSON 值然后在不进行任何其他处理的情况下进行打印时，json输出与 Importing 相同的文本，而jsonb不保留语义上无关紧要的细节，例如空格。例如，请注意此处的区别：

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

值得注意的一个语义无关紧要的细节是，在jsonb中，将根据基础numeric类型的行为来打印数字。实际上，这意味着使用E表示法 Importing 的数字将不打印，例如：

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

但是，jsonb将保留尾随零，如在本示例中所示，即使它们在语义上无意义(例如，相等检查)也是如此。

8 .14.2. 有效设计 JSON 文档

将数据表示为 JSON 可以比传统的关系数据模型灵活得多，而传统的关系数据模型在需求多变的环境中非常引人注目。这两种方法很可能在同一应用程序中共存和互补。但是，即使对于需要最大灵 Active 的应用程序，仍然建议 JSON 文档具有某种固定的结构。该结构通常是不强制执行的(尽管可以声明性地执行某些业务规则)，但是具有可预测的结构可以使编写查询变得更加容易，该查询可以有效地汇总表中的一组“文档”(基准)。

JSON 数据存储在表中时，与其他任何数据类型一样，要遵循相同的并发控制注意事项。尽管存储大型文档是可行的，但请记住，任何更新都将获得整行的行级锁。考虑将 JSON 文档限制为可 Management 的大小，以减少更新事务之间的锁争用。理想情况下，JSON 文档应该每个都代表一个原子数据，业务规则规定不能将该原子数据进一步细分为可以独立修改的较小数据。

8 .14.3. jsonb 包含与存在

测试“包含”是jsonb的一项重要功能。 json类型没有并行的工具集。包容性测试一个jsonb文档中是否包含另一个。这些示例返回 true，除非另有说明：

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields false

-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

一般原则是，包含对象必须在结构和数据内容上与包含对象匹配，可能是在从包含对象中丢弃了一些不匹配的数组元素或对象键/值对之后。但是请记住，进行包含匹配时，数组元素的 Sequences 并不重要，并且重复数组元素仅被有效考虑一次。

作为结构必须匹配的一般原则的特殊 exception，数组可以包含原始值：

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

jsonb还具有* existence *运算符，它是包含主题的变体：它测试字符串(以text值形式给出)是作为对象键还是数组元素出现在jsonb值的顶层。这些示例返回 true，除非另有说明：

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';

当涉及许多键或元素时，JSON 对象比数组更适合用于测试是否包含或存在，因为与数组不同，JSON 对象在内部进行了优化以进行搜索，因此不需要线性搜索。

SELECT doc->'site_name' FROM websites
WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

SELECT doc->'site_name' FROM websites
WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

Section 9.15中记录了各种包含和存在运算符，以及所有其他 JSON 运算符和函数。

8 .14.4. jsonb 索引

GIN 索引可用于有效搜索大量jsonb文档(基准)中出现的键或键/值对。提供了两种 GIN“操作员类别”，提供了不同的性能和灵 Active 权衡。

jsonb的默认 GIN 运算符类支持使用顶级键存在的运算符?，?&和?|运算符以及路径/值存在的运算符@>进行查询。 (有关这些运算符实现的语义的详细信息，请参见Table 9.44。)使用此运算符类创建索引的示例是：

CREATE INDEX idxgin ON api USING GIN (jdoc);

非默认 GIN 运算符类别jsonb_path_ops仅支持对@>运算符编制索引。使用此运算符类创建索引的示例是：

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

考虑一个表的示例，该表存储了从第三方 Web 服务检索到的 JSON 文档以及已记录的架构定义。典型的文档是：

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

我们将这些文档存储在名为_3 的jsonb列中的api表中。如果在此列上创建了 GIN 索引，则如下查询可以利用该索引：

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

但是，索引不能用于以下查询，因为尽管运算符?是可索引的，但它不会直接应用于已索引的列jdoc：

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

尽管如此，通过适当使用表达式索引，上述查询仍可以使用索引。如果在"tags"键中查询特定项很常见，则定义这样的索引可能是值得的：

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

现在，WHERE子句jdoc -> 'tags' ? 'qui'将被识别为可索引运算符?到索引表达式jdoc -> 'tags'的应用。 (有关表达式索引的更多信息，请参见Section 11.7。)

查询的另一种方法是利用遏制，例如：

-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

jdoc列上的简单 GIN 索引可以支持此查询。但是请注意，这样的索引将在jdoc列中存储每个键和值的副本，而上一示例的表达式索引仅存储在tags键下找到的数据。尽管简单索引方法更加灵活(因为它支持对任何键的查询)，但目标表达式索引可能比简单索引更小且搜索速度更快。

尽管jsonb_path_ops运算符类仅支持@>运算符的查询，但与默认的运算符类jsonb_ops相比，它具有明显的性能优势。对于相同的数据，jsonb_path_ops索引通常比jsonb_ops索引小得多，并且搜索的特殊性更好，尤其是当查询包含在数据中频繁出现的键时。因此，搜索操作通常比默认运算符类具有更好的性能。

jsonb_ops和jsonb_path_ops GIN 索引之间的技术区别在于，前者为数据中的每个键和值创建独立的索引项，而后者仅为数据中的每个值创建索引项。 [6]基本上，每个jsonb_path_ops索引项都是值和导致它的键的哈希；例如，要对{"foo": {"bar": "baz"}}进行索引，将创建单个索引项，将foo，bar和baz的全部三个都合并到哈希值中。因此，寻找这种结构的遏制查询将导致极其特定的索引搜索。但是根本无法确定foo是否作为键出现。另一方面，jsonb_ops索引将创建三个分别代表foo，bar和baz的索引项；然后执行包含查询，它将查找包含所有这三个项目的行。尽管 GIN 索引可以相当有效地执行此类 AND 搜索，但它仍不如同等的jsonb_path_ops搜索那样具体，并且速度也较慢，尤其是当包含三个索引项中的任何一个的行非常多时。

jsonb_path_ops方法的缺点是，它不为不包含任何值(例如{"a": {}})的 JSON 结构生成索引条目。如果要求搜索包含这种结构的文档，则将需要全索引扫描，这非常慢。因此，jsonb_path_ops不适合经常执行此类搜索的应用程序。

jsonb还支持btree和hash索引。仅当检查完整 JSON 文档的重要性很重要时，这些通常才有用。 jsonb基准的btree排序很少引起人们的兴趣，但出于完整性考虑，它是：

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

具有相等对数的对象按以下 Sequences 进行比较：

key-1, value-1, key-2 ...

请注意，对象键是按照其存储 Sequences 进行比较的；特别是，由于较短的键存储在较长的键之前，因此可能导致结果不直观，例如：

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

同样，按相同 Sequences 比较元素数相等的数组：

element-1, element-2 ...

使用与基础 PostgreSQL 数据类型相同的比较规则来比较原始 JSON 值。使用默认数据库排序规则比较字符串。

8.14.5. Transforms

可以使用其他扩展来实现针对不同程序语言的jsonb类型的转换。

PL/Perl 的 extensions 为jsonb_plperl和jsonb_plperlu。如果使用它们，则jsonb值将酌情 Map 到 Perl 数组，哈希和标量。

PL/Python 的 extensions 为jsonb_plpythonu，jsonb_plpython2u和jsonb_plpython3u(有关 PL/Python 的命名约定，请参见Section 46.1)。如果使用它们，则jsonb值将酌情 Map 到 Python 字典，列表和标量。

[6]为此，术语“值”包括数组元素，尽管 JSON 术语有时认为数组元素与对象中的值不同。