Postgresql数据库中的Json类型字段官方文档说明_postgres json

8.14. JSON Types

JSON数据类型用于存储JSON (JavaScript对象符号)数据，如RFC 71594中所述。

这样的数据也可以存储为文本，但是JSON数据类型的优点是强制每个存储值根据JSON规则是有效的。对于存储在这些数据类型中的数据，还可以使用各种特定于json的函数和操作符;参见9.16节。

PostgreSQL提供了两种类型的JSON数据存储:JSON和jsonb。为了对这些数据类型实现高效的查询机制，PostgreSQL还提供了章节8.14.7中描述的jsonpath数据类型。

json和jsonb数据类型接受几乎相同的值集作为输入。主要的实际差别在于效率。json数据类型存储输入文本的精确副本，每次执行时，处理函数都必须重新解析它;而jsonb数据以分解的二进制格式存储，由于增加了转换开销，因此输入速度略慢，但处理速度显著加快，因为不需要重新解析。Jsonb还支持索引，这可能是一个显著的优势。

因为json类型存储输入文本的精确副本，它将保留标记之间语义上无关紧要的空白，以及json对象中的键的顺序。此外，如果值中的JSON对象包含同一个键不止一次，则保留所有键/值对。(处理函数将最后一个值视为运算值。)相比之下，jsonb不保留空白，不保留对象键的顺序，也不保留重复的对象键。如果在输入中指定了重复的键，则只保留最后一个值。

一般来说，大多数应用程序应该倾向于将JSON数据存储为jsonb，除非有非常特殊的需求，例如关于对象键排序的遗留假设

RFC 7159规定JSON字符串应该用UTF8编码。因此，JSON类型不可能严格遵守JSON规范，除非数据库编码是UTF8。直接包含数据库编码中不能表示的字符的尝试将失败;反之，可以用数据库编码表示但不能用UTF8表示的字符将被允许。

RFC 7159允许JSON字符串包含由\uXXXX表示的Unicode转义序列。在json类型的输入函数中，无论数据库编码如何，都允许Unicode转义，并且只检查语法正确性(也就是说，在\u后面有四个十六进制数字)。但是，jsonb的输入函数更严格:它禁止对数据库编码中不能表示的字符进行Unicode转义。jsonb类型还拒绝\u0000(因为它不能在PostgreSQL的文本类型中表示)，并且它坚持使用Unicode代理对来指定Unicode基本多语言平面之外的字符是正确的。有效的Unicode转义被转换为等效的单个字符以供存储;这包括将代理项对折叠成单个字符。

第9.16节中描述的许多JSON处理函数会将Unicode es cape转换为常规字符，因此即使它们的输入类型是JSON而不是jsonb，也会抛出相同类型的错误。json输入函数没有进行这些检查的事实可能被认为是一个历史工件，尽管它允许在不支持表示字符的数据库编码中简单存储(不处理)json Unicode转义。
1

当将文本JSON输入转换为jsonb时，RFC 7159描述的基本类型被有效地映射到原生PostgreSQL类型上，如表8.23所示。因此，对于构成有效jsonb数据的内容，存在一些小的附加约束，这些约束不适用于json类型，也不适用于抽象的json，对应于底层数据类型可以表示的内容的限制。值得注意的是，jsonb将拒绝超出PostgreSQL数字数据类型范围的数字，而json则不会。RFC 7159允许这种实现定义的限制。然而，在实践中，这样的问题更有可能发生在其他实现中，因为通常将JSON的数字基元类型表示为IEEE 754双精度浮点数(RFC 7159明确预测并允许)。当在这样的系统中使用JSON作为交换格式时，与最初由PostgreSQL存储的数据相比，应该考虑失去数字精度的危险。

相反，如表中所述，JSON原始类型的输入格式有一些小限制，不适用于相应的PostgreSQL类型

JSON基本类型和相应的PostgreSQL类型

8.14.1. JSON Input and Output Syntax （JSON输入和输出语法）

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

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

--简单标量/原始值
--原始值可以是数字、带引号的字符串、true、false或null
SELECT '5'::json;

--对象，该对象包含键和值对
--注意，对象键必须始终是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

--数组和对象可以任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
1
2
3
4
5
6
7
8
9
10

如前所述，当输入JSON值并在不进行任何额外处理的情况下打印时，JSON输出与输入相同的文本，而jsonb不保留语义无关的细节，如空白。例如，请注意这里的区别:

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

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

值得注意的一个语义无关紧要的细节是，在jsonb中，数字将根据底层数字类型的行为打印。例如，在实践中，这意味着用E符号输入的数字将不带它被打印出来

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
1

但是，jsonb将保留后面的小数零，如本例所示，尽管对于相等性检查等目的，这些零在语义上并不重要。

用于构造和处理JSON值的内置函数和操作符列表，请参见9.16节。

8.14.2. Designing JSON Documents （设计JSON文档）

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

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

8.14.3. jsonb Containment and Existence

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

--简单标量/原语值只包含相同的值:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

--右边的数组包含在左边的数组中:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

--数组元素的顺序并不重要，所以这也是成立的:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

--重复的数组元素也不重要:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

--包含右侧有一对的对象
--在左侧的对象中:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

--对象中包含的右边的数组
--数组的左边，即使类似的数组嵌套在其中:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- yields false

--但是通过一层嵌套，它包含:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

--类似地，此处没有报告为ture:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- yields false

--包含一个顶级键和一个空对象:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

作为结构必须匹配的一般原则的特殊例外，数组可以包含一个基元值:

--该数组包含原始字符串值:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

--此异常不是互惠的——此处报告了不包含:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- yields false
1
2
3
4
5

Jsonb还有一个存在操作符，这是包含主题的一种变体:它测试字符串(作为文本值给出)是否作为对象键或数组元素出现在Jsonb值的顶层。这些例子返回true，除非有说明:

--字符串作为数组元素存在
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

--字符串作为对象键存在:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

--不考虑对象值:
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- yields false

--与包含一样，存在必须在顶级匹配:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

--如果字符串匹配原始JSON字符串，则认为它存在:
SELECT '"foo"'::jsonb ? 'foo';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

提示 由于JSON包含是嵌套的，因此适当的查询可以跳过显式选择子对象。举个例子，假设我们有一个文档列，在顶层包含对象，大多数对象包含标签字段，这些标签字段包含子对象的数组。该查询查找子对象同时包含"term":"paris"和"term":"food" ap pear的条目，而忽略标签数组之外的任何此类键:

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

一个SQL可以完成同样的事情，比如说，

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

但这种方法不太灵活，而且通常效率也较低。

另一方面，JSON存在操作符不是嵌套的:它只在JSON值的顶层查找指定的键或数组元素。

各种包含操作符和存在操作符，以及所有其他JSON操作符和函数在第9.16节中有详细说明。

8.14.4. jsonb Indexing

GIN索引可用于高效地搜索大量jsonb文档(数据)中的键或键/值对。提供了两个GIN“操作符类”，提供不同的性能和灵活性权衡。

jsonb的默认GIN操作符类支持使用键存在操作符?、?|和?&进行查询，包含操作符@>以及jsonpath匹配操作符@?和@@。(有关这些操作符实现的语义的详细信息，请参见表9.46。)使用此操作符类创建索引的示例如下:

CREATE INDEX idxgin ON api USING GIN (jdoc);
1

非默认的GIN操作符类jsonb_path_ops不支持key-exists操作符，但是它支持@>， @?和@@。使用此操作符类创建索引的示例如下:

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

考虑一个存储从第三方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"
 ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

我们将这些文档存储在一个名为api的表中，在一个名为jdoc的jsonb列中。如果在这个列上创建了一个GIN索引，像下面这样的查询可以使用索引:

--查找关键字“company”值为“Magnafone”的文档
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
1
2

然而，索引不能用于如下查询，因为尽管操作符?是可索引的，它不会直接应用到已索引的列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';
1
2
3

不过，通过适当地使用表达式索引，上面的查询可以使用索引。如果在"tags"键中查询特定的项是常见的，那么定义这样的索引可能是值得的:

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

现在，WHERE子句jdoc ->“标签”?'qui’将被识别为可索引操作符的应用程序?到索引表达式jdoc ->“标签”。(关于表达式索引的更多信息可以在第11.7节中找到。)

查询的另一种方法是利用容器，例如:

--查找键“tags”包含数组元素“qui”的文档
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

1
2
3

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

GIN索引还支持@?和@@操作符，它们执行jsonpath匹配。的例子是

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @?  '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

1
2
3
4

对于这些操作符，GIN索引从jsonpath模式中提取accessors_chain = constant形式的子句，并基于这些子句中提到的键和值进行索引搜索。访问器链可以包括.key、[*]和[index]访问器。jsonb_ops操作符类还支持.*和.**处理器，但jsonb_path_ops操作符类不支持。

虽然jsonb_path_ops操作符类只支持使用@>， @?和@@ operator (@@ operator)相比，它比默认操作符类jsonb_ops具有显著的性能优势。对于相同的数据，json b_path_ops索引通常比jsonb_ops索引小得多，而且搜索的特异性更好，特别是当查询包含在数据中频繁出现的键时。因此，搜索操作通常比默认操作符类执行得更好。

jsonb_ops和jsonb_path_ops GIN索引之间的技术差异在于，前者为数据中的每个键和值创建独立的索引项，而后者仅为数据中的每个值创建索引项。基本上，每个jsonb_path_ops索引项都是值和指向它的键的散列;例如，索引{“foo”: {“bar”: “baz”}}，将创建一个索引项，将foo、bar和baz中的所有三个都合并到哈希值中。因此，寻找该结构的包含查询将导致极其特定的索引搜索;但是根本没有办法确定foo是否作为键出现。另一方面，jsonb_ops索引将创建三个索引项，分别表示foo、bar和baz;然后，要执行包含查询，它将查找包含所有这三个项的行。虽然GIN索引可以相当有效地执行这样的AND搜索，但它仍然比同等的jsonb_path_ops搜索更不具体，更慢，特别是如果有大量的行包含三个索引项中的任何一个时。

jsonb_path_ops方法的一个缺点是，它不会为不包含任何值的JSON结构(如{" A ":{}})生成索引项。如果请求搜索包含这种结构的文档，则需要进行全索引扫描，这是相当慢的。因此，Jsonb_path_ops不适合经常执行此类搜索的应用程序。

Jsonb还支持btree和hash索引。这些通常只在检查完整JSON文档的相等性非常重要时才有用。对于jsonb数据的btree排序很少有很大的兴趣，但为了完整性，它是:

Object > Array > Boolean > Number > String > Null
有n对的对象 > 对象具有n - 1对
n个元素的数组 > n-1个元素的数组
1
2
3

具有相等数目的对的对象按顺序进行比较:

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

注意对象键是按它们的存储顺序进行比较的;特别是，由于较短的键存储在较长键之前，这可能会导致不直观的结果，例如:

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

类似地，具有相同数量元素的数组按照以下顺序进行比较:

element-1, element-2 ...
1

基本JSON值的比较使用与底层Post greSQL数据类型相同的比较规则。字符串使用默认的数据库排序规则进行比较

8.14.5. jsonb Subscripting

jsonb数据类型支持数组样式的下标表达式来提取和修改元素。嵌套值可以通过链接下标表达式来表示，遵循与jsonb_set函数中的path参数相同的规则。如果jsonb值是一个数组，则数字下标从0开始，负整数从数组的最后一个元素开始倒数。不支持切片表达式。下标表达式的结果总是jsonb数据类型。

UPDATE语句可以在SET子句中使用下标来修改jsonb值。下标路径必须可以遍历所有受影响的值，只要它们存在。例如，路径val[‘a’][‘b’] [‘c’]可以一直遍历到c，如果每个val, val[‘a’]和val[‘a’][‘b’]是一个对象。如果没有定义val[‘a’]或val[‘a’][‘b’]，则将其创建为空对象，并在必要时进行填充。但是，如果任何val本身或中间值之一被定义为非对象(如字符串、数字或jsonb null)，遍历将无法进行，因此会引发错误并终止事务。

下标语法的一个例子:

--按键提取对象值
SELECT ('{"a": 1}'::jsonb)['a'];

--通过键路径提取嵌套对象值
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

--根据索引提取数组元素
SELECT ('[1, "2", null]'::jsonb)[1];

--按键更新对象值。注意'1'周围的引号:赋值对象
--值也必须为jsonb类型
UPDATE table_name SET jsonb_field['key'] = '1';

--如果任何记录的jsonb_field['a']['b']是某个值，则将引发错误
--而不是一个物体。例如，值{"a": 1}有一个数值
--a键的。
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

--使用带下标的WHERE子句筛选记录。由于
--下标是jsonb，与它比较的值也必须是jsonb。
--双引号使“value”也是一个有效的jsonb字符串。
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

通过下标的Jsonb赋值处理一些与jsonb_set不同的边缘情况。当源jsonb值为NULL时，通过下标赋值将继续进行，就像它是下标键所暗示的类型(对象或数组)的空JSON值一样:

--原来jsonb_field是NULL，现在是{"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

--哪里jsonb_field是NULL，它现在是[1]
UPDATE table_name SET jsonb_field[0] = '1';
1
2
3
4
5

如果为包含太少元素的数组指定了索引，则将追加NULL元素，直到索引可达且可以设置该值为止。

--jsonb_field以前是[]，现在是[null, null, 2];
--Jsonb_field是[0]，现在是[0,null, 2]
UPDATE table_name SET jsonb_field[2] = '2';
1
2
3

jsonb值将接受对不存在的下标路径的赋值，只要要遍历的最后一个现有元素是一个对象或数组，就像相应的下标所暗示的那样(路径中最后一个下标所指示的元素没有遍历，而且可能是任何东西)。将创建嵌套数组和对象结构，在前一种情况下，将按照下标路径指定填充null，直到可以放置所分配的值。

--在jsonb_field{},现在{“a”:[{“b”:1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

--jsonb_field以前是[]，现在是[null， {"a": 1}]
UPDATE table_name SET jsonb_field[1]['a'] = '1';
1
2
3
4
5

8.14.6. Transforms

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

PL/Perl的扩展名为jsonb_plperl和jsonb_plperlu。如果使用它们，jsonb值将被映射到Perl数组、散列和标量

PL/Python的扩展名为jsonb_plpython3u。如果你使用它，jsonb值会被映射到适当的Python字典、列表和标量。

在这些扩展中，jsonb_plperl被认为是“受信任的”，也就是说，它可以由在当前数据库上拥有CREATE权限的非缺省的超级用户安装。其余的需要超级用户权限才能安装。

8.14.7. jsonpath Type

jsonpath类型在PostgreSQL中实现了对SQL/JSON路径语言的支持，以有效地查询JSON数据。它提供了解析的SQL/JSON路径表达式的二进制表示形式，该表达式指定了路径引擎要从JSON数据中检索的项，以便使用SQL/JSON查询函数进行进一步处理。

SQL/JSON路径谓词和操作符的语义通常遵循SQL。同时，为了提供一种自然的处理JSON数据的方式，SQL/JSON路径语法使用了一些JavaScript约定:

• 点(.)用于成员访问。
• •括号([])用于数组访问。
• SQL/JSON数组是相对于0的，不像常规的SQL数组从1开始。

SQL/JSON路径表达式通常在SQL查询中以SQL字符串文字形式编写，因此它必须用单引号括起来，并且在值中需要的任何单引号都必须加倍(参见4.1.2.1节)。某些形式的路径表达式需要字符串字面值。这些em -的字符串字面值遵循JavaScript/ECMAScript惯例:它们必须被双引号包围，并且可以在其中使用反斜杠转义来表示难以键入的字符。特别地，在嵌入字符串文字中写入双引号的方法是"，而要写入反斜杠本身，必须写入\。其他特殊的反斜杠序列包括JSON字符串中可识别的反斜杠序列:\b， \f， \n， \r， \t， \v用于各种ASCII控制字符，\uNNNN用于Uni码字符，由其4位十六进制码位标识。反斜杠语法还包括JSON不允许的两种情况:仅由两个十六进制数字编写的字符代码\xNN，以及\u{N…}表示用1到6个十六进制数字编写的字符代码。

路径表达式由一系列路径元素组成，可以是以下任意一个:

• JSON基本类型的路径文字:Unicode文本、数字、true、false或null。
• 路径变量如表8.24所示。
• 表8.25列出的访问器操作符。
• jsonpath操作符和9.16.2.2节中列出的方法。
• 括号，可用于提供筛选器表达式或定义路径求值的顺序。

关于在SQL/JSON查询函数中使用jsonpath表达式的详细信息，请参见章节9.16.2。

表8.24.jsonpath变量 表8.25.jsonpath访问器