jsonb包含和存在jsonb索引根据RFC 7159中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函 数和操作符可以用于存储在这些数据类型中的数据，见 第 9.16 节。
PostgreSQL 提供存储JSON数据的两种类型：json和 jsonb。 为了实现这些数据类型高效的查询机制， PostgreSQL还在第 8.14.6 节中提供了jsonpath数据类型描述。
json和 jsonb数据类型接受几乎完全相同的值集合作为输入。 主要的实际区别之一是效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。
由于json类型存储的是输入文本的准确拷贝，其中可能会保留在语法 上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。还有， 如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（ 处理函数会把最后的值当作有效值）。相反，jsonb不保留空格、不 保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有 最后一个值会被保留。
通常，除非有特别特殊的需要（例如遗留的对象键顺序假设），大多数应用应该 更愿意把 JSON 数据存储为jsonb。
RFC 7159 指定 JSON 字符串应以 UTF8 编码。因此 JSON 类型不可能严格遵守 JSON 规范，除非数据库编码 是 UTF8。尝试直接包括数据库编码中无法表示的字符将会失败。反过来，能 在数据库编码中表示但是不在 UTF8 中的字符是被允许的。
RFC 7159 允许 JSON 字符串包含\u所标记的 Unicode 转义序列。在XXXXjson类型的输入函数中，不管数据库 编码如何都允许 Unicode 转义，并且只检查语法正确性（即，跟在\u后面的四个十六进制位）。但是，jsonb的输入函数更加严格：它不允许对无法在数据库 编码中表示的字符进行 Unicode 转义。jsonb类型也拒绝\u0000（因为 PostgreSQL的text类型无法表示 它），并且它坚持使用 Unicode 代理对来标记位于 Unicode 基本多语言平面之外 的字符是正确的。合法的 Unicode 转义会被转换成等价的单个字符进 行存储，这包括把代理对折叠成一个单一字符。
很多第 9.16 节中描述的 JSON 处理函数将把 Unicode 转义转换成常规字符，并且将因此抛出和刚才所描述的同样类型的错误（即使它们 的输入是类型json而不是jsonb）。json的 输入函数不做这些检查是由来已久的，不过它确实允许将 JSON Unicode 转义简单 的（不经处理）存储在一个不支持所表示字符的数据库编码中。
在把文本 JSON 输入转换成jsonb时，RFC 7159描述 的基本类型会被有效地映射到原生的 PostgreSQL类型（如 表 8.23中所示）。因此，在合法 jsonb数据的组成上有一些次要额外约束，它们不适合 json类型和抽象意义上的 JSON，这些约束对应于有关哪些东西不 能被底层数据类型表示的限制。尤其是，jsonb将拒绝位于 PostgreSQL numeric数据类型范 围之外的数字，而json则不会。这类实现定义的限制是 RFC 7159 所允许的。不过，实际上这类问题更可能发生在其他实 现中，因为把 JSON 的number基本类型表示为 IEEE 754 双精度浮点 是很常见的（这也是RFC 7159 明确期待和允许的）。当在这类系 统间使用 JSON 作为一种交换格式时，应该考虑丢失数字精度的风险。
相反地，如表中所述，有一些 JSON 基本类型输入格式上的次要限制并不适用于相 应的PostgreSQL类型。
表 8.23. JSON 基本类型和相应的PostgreSQL类型
| JSON 基本类型 | PostgreSQL类型 | 注释 |
|---|---|---|
string | text | \u0000是不允许的，因为 Unicode 转义表示数据库编码中不可用的字符 |
number | numeric | 不允许NaN和 infinity值 |
boolean | boolean | 只接受小写true和false拼写 |
null | (无) | SQL NULL是一个不同的概念 |
RFC 7159 中定义了 JSON 数据类型的输入/输出语法。
下列都是合法的json（或者jsonb）表达式：
-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;
-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;
-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
如前所述，当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而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记号 输入的数字被打印出来时就不会有该记号，例如：
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
json | jsonb
-----------------------+-------------------------
{"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row) 不过，如这个例子所示，jsonb将会保留拖尾的小数点后的零，即便这 对于等值检查等目的来说是语义上无意义的。
对于可用于构造和处理 JSON 值的内置函数和运算符的列表，参见 第 9.16 节。
将数据表示为 JSON 比传统关系数据模型要灵活得多，在需求不固定时 这种优势更加令人感兴趣。在同一个应用里非常有可能有两种方法共存 并且互补。不过，即便是在要求最大灵活性的应用中，我们还是推荐 JSON 文档有固定的结构。该结构通常是非强制的（尽管可能会强制一 些业务规则），但是有一个可预测的结构会使书写概括一个表中的 “文档”（数据）集合的查询更容易。
当被存储在表中时，JSON 数据也像其他数据类型一样服从相同的并发 控制考虑。尽管存储大型文档是可行的，但是要记住任何更新都在整行 上要求一个行级锁。为了在更新事务之间减少锁争夺，可考虑把 JSON 文档限制到一个可管理的尺寸。理想情况下，JSON 文档应该每个表示 一个原子数据，业务规则命令不会进一步把它们划分成更小的可独立修 改的数据。
jsonb包含和存在测试包含是jsonb的一种重要能力。对 json类型没有平行的功能集。包含测试会测试一个 jsonb文档是否被包含在另一个文档中。除了特别注解 之外，这些例子都会返回真：
-- 简单的标量/基本值只包含相同的值：
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; -- 得到假
-- 但是如果同样也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
-- 类似的，这个例子也不会被认为是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- 得到假
-- 包含一个顶层键和一个空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
一般原则是被包含的对象必须在结构和数据内容上匹配包含对象，这种匹配 可以是从包含对象中丢弃了不匹配的数组元素或者对象键值对之后成立。但 是记住做包含匹配时数组元素的顺序是没有意义的，并且重复的数组元素实 际也只会考虑一次。
结构必须匹配的一般原则有一种特殊情况，一个数组可以包含一个基本值：
-- 这个数组包含基本字符串值：
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
-- 反之不然，下面的例子会报告“不包含”：
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- 得到假
jsonb还有一个存在操作符，它是包含的一种 变体：它测试一个字符串（以一个text值的形式给出）是否出 现在jsonb值顶层的一个对象键或者数组元素中。除非特别注解， 下面这些例子返回真：
-- 字符串作为一个数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
-- 字符串作为一个对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';
-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- 得到假
-- 和包含一样，存在必须在顶层匹配：
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 得到假
-- 如果一个字符串匹配一个基本 JSON 字符串，它就被认为存在：
SELECT '"foo"'::jsonb ? 'foo';
当涉及很多键或元素时，JSON 对象比数组更适合于做包含或存在测试， 因为它们不像数组，进行搜索时会进行内部优化，并且不需要被线性搜索。
由于 JSON 的包含是嵌套的，因此一个恰当的查询可以跳过对子对象的显式选择。 例如，假设我们在顶层有一个doc列包含着对象，大部分对象 包含着tags域，其中有子对象的数组。这个查询会找到其中出现了 同时包含"term":"paris"和"term":"food"的子对象 的项，而忽略任何位于tags数组之外的这类键：
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"}]';
但是后一种方法灵活性较差，并且常常也效率更低。
在另一方面，JSON 的存在操作符不是嵌套的：它将只在 JSON 值的顶层 查找指定的键或数组元素。
第 9.16 节中记录了多个包含和存在操作符，以及 所有其他 JSON 操作符和函数。
jsonb索引GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。提供了两种 GIN “操作符类”，它们在性能和灵活 性方面做出了不同的平衡。
jsonb的默认 GIN 操作符类支持使用@>、 ?、?&以及?|操作符的查询（这些 操作符实现的详细语义请见表 9.45）。 使用这种操作符类创建一个索引的例子：
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"
]
} 我们把这些文档存储在一个名为api的表的名为 jdoc的jsonb列中。如果在这个列上创建一个 GIN 索引，下面这样的查询就能利用该索引：
-- 寻找键 "company" 有值 "Magnafone" 的文档
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
不过，该索引不能被用于下面这样的查询，因为尽管操作符?是可索引的，但它不能直接被应用于被索引列jdoc：
-- 寻找这样的文档：其中的键 "tags" 包含键或数组元素 "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'上的应用（更多有关表达式索引的信息可见第 11.7 节）。
此外, GIN 索引支持 @@和 @?运算符, 以执行 jsonpath匹配。
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
GIN 索引从jsonpath中提取如下格式的语句： accessors_chain= const。 存取器链可能由.key[*], 和 [存取器组成。 index]jsonb_ops此外还支持 .*和 .**存取器。
另一种查询的方法是利用包含，例如：
-- 寻找这样的文档：其中键 "tags" 包含数组元素 "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_opsGIN 索引之间的技术区别是前者为数据中的每一个键和值创建独立的索引项， 而后者值为该数据中的每个值创建索引项。 基本上，每一个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顺序很少有人关系，但是为了完整性其顺序是：
对象 > 数组 > 布尔 > 数字 > 字符串 > 空值
带有 n 对的对象 > 带有 n - 1 对的对象
带有 n 个元素的数组 > 带有 n - 1 个元素的数组
具有相同数量对的对象这样比较：
key-1, value-1, key-2 ...
注意对象键被按照它们的存储顺序进行比较，特别是由于较短的键被存储在 较长的键之前，这可能导致结果不直观，例如：
{ "aa": 1, "c": 1} > {"b": 1, "d": 1} 相似地，具有相同元素数量的数组按照以下顺序比较：
element-1, element-2 ...
基本 JSON 值的比较会使用低层PostgreSQL 数据类型相同的比较规则进行。字符串的比较会使用默认的数据库排序规则。
有一些附加的扩展可以为不同的过程语言实现jsonb类型的转换。
PL/Perl的扩展被称作jsonb_plperl和jsonb_plperlu。如果使用它们，jsonb值会视情况被映射为Perl的数组、哈希和标量。
PL/Python的扩展被称作jsonb_plpythonu、jsonb_plpython2u和jsonb_plpython3u（PL/Python命名习惯请见第 45.1 节）。如果使用它们，jsonb值会视情况被映射为Python的词典、列表和标量。
在这些扩展中，jsonb_plperl被认为是“trusted”， 也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。 其余的需要超级用户权限才能安装。
在PostgreSQL中，jsonpath类型实现支持SQL/JSON 路径语言以有效地查询 JSON 数据。 它提供了已解析的SQL/JSON路径表达式的二进制表示，该表达式指定路径引擎从JSON数据中检索的项，以便使用SQL/JSON查询函数进行进一步处理。
SQL/JSON 路径谓词和运算符的语义通常遵循 SQL。同时，为了提供使用 JSON 数据的自然方法，SQL/JSON 路径语法使用一些 JavaScript 约定：
小数点 (.) 用于成员访问.
方括号 ([]) 用于数组访问.
与从 1 开始的常规 SQL 数组不同，SQL/JSON 数组是 0 相对的。
SQL/JSON路径表达式通常以SQL字符串文字形式写入SQL查询中，因此它必须用单引号括起来，并且值中需要的任何单引号都必须是双引号(参见 第 4.1.2.1 节)。 某些形式的路径表达式需要其中的字符串文本。这些嵌入的字符串文本遵循JavaScript/ECMAScript约定:它们必须用双引号括起来，并且反斜杠转义可以用于表示其他难以输入的字符。 特别是，在嵌入字符串文本中编写双引号的方法为\"，并且要编写反斜杠本身，你必须写\\。 包括在JSON字符串中识别的其他特殊的反斜杠序列: \b, \f, \n, \r, \t, \v对于各种 ASCII 控制字符，以及由它的4个六位数编码点标识标识的 Unicode 字符\u。 反斜杠语法还包括 JSON 不允许的两个案例: NNNN\x对于只用两个十六进制数字编写的字符代码，以及NN\u{对于用 1 到 6 个十六进制数字编写的字符代码。 N...}
路径表达式由一系列路径元素组成，可以是以下任何一种：
JSON基本类型的路径文字:Unicode文本、数字、真、假或空.
Path variables listed in 表 8.24中列出的路径变量。
表 8.25中列出的访问器运算符。
第 9.16.2.2 节中列出的jsonpath运算符和方法。
括号，可用于提供筛选器表达式或定义路径计算的顺序。
有关使用jsonpath具有 SQL/JSON 查询函数的表达式的详细信息，参见第 9.16.2 节。
表 8.24. jsonpath变量
| 变量 | 描述 |
|---|---|
$ | 表示被查询的 JSON 值的变量(context item). |
$varname | 命名变量。其值可以由参数vars多个JSON处理函数设置。 详细信息请参见 表 9.47。 |
@ | 表示筛选器表达式中路径计算结果的变量。 |
表 8.25. jsonpathAccessors
| 访问器运算符 | 描述 |
|---|---|
| 返回具有指定密钥的对象成员的成员访问器。 如果键名称是以 |
| 通配符成员访问器，该访问器返回位于当前对象顶层的所有成员的值。 |
| 递归通配符成员访问器，它处理当前对象JSON层次结构的所有级别，并返回所有成员值，而不管它们的嵌套级别如何。 这是 PostgreSQL SQL/JSON 标准的扩展。 |
| 与 |
| 数组元素访问器. 指定的 |
| 返回所有数组元素的通配符数组元素访问器。 |
对于这种目的，术语“值”包括数组元素，尽管 JSON 的术语有时 认为数组元素与对象内的值不同。
备案信息： 粤ICP备15087711号-2
Copyright © 2008-2024 啊嘎哇在线工具箱 All Rights.
本站所有资料来源于网络，版权归原作者所有，仅作学习交流使用，如不慎侵犯了您的权利，请联系我们。