根据RFC 7159
中的说明,JSON 数据类型是用来存储 JSON(JavaScript Object Notation)
数据的。这种数据也可以被存储为text
,但是 JSON 数据类型的
优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函
数和操作符可以用于存储在这些数据类型中的数据,见
第?6.15?节。
有两种 JSON 数据类型:json
和 jsonb
。它们
几乎接受完全相同的值集合作为输入。主要的实际区别之一是
效率。json
数据类型存储输入文本的精准拷贝,处理函数必须在每
次执行时必须重新解析该数据。而jsonb
数据被存储在一种分解好的
二进制格式中,它在输入时要稍慢一些,因为需要做附加的转换。但是
jsonb
在处理时要快很多,因为不需要解析。jsonb
也支
持索引,这也是一个令人瞩目的优势。
由于json
类型存储的是输入文本的准确拷贝,其中可能会保留在语法
上不明显的、存在于记号之间的空格,还有 JSON 对象内部的键的顺序。还有,
如果一个值中的 JSON 对象包含同一个键超过一次,所有的键/值对都会被保留(
处理函数会把最后的值当作有效值)。相反,jsonb
不保留空格、不
保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键,只有
最后一个值会被保留。
通常,除非有特别特殊的需要(例如遗留的对象键顺序假设),大多数应用应该
更愿意把 JSON 数据存储为jsonb
。
UXDB对每个数据库只允许一种 字符集编码。因此 JSON 类型不可能严格遵守 JSON 规范,除非数据库编码 是 UTF8。尝试直接包括数据库编码中无法表示的字符将会失败。反过来,能 在数据库编码中表示但是不在 UTF8 中的字符是被允许的。
RFC 7159 允许 JSON 字符串包含\u
所标记的 Unicode 转义序列。在XXXX
json
类型的输入函数中,不管数据库
编码如何都允许 Unicode 转义,并且只检查语法正确性(即,跟在\u
后面的四个十六进制位)。但是,jsonb
的输入函数更加严格:它不允
许非 ASCII 字符的 Unicode 转义(高于U+007F
的那些),除非数据
库编码是 UTF8。jsonb
类型也拒绝\u0000
(因为
UXDB的text
类型无法表示
它),并且它坚持使用 Unicode 代理对来标记位于 Unicode 基本多语言平面之外
的字符是正确的。合法的 Unicode 转义会被转换成等价的 ASCII 或 UTF8 字符进
行存储,这包括把代理对折叠成一个单一字符。
很多第?6.15?节中描述的 JSON 处理函数将把 Unicode
转义转换成常规字符,并且将因此抛出和刚才所描述的同样类型的错误(即使它们
的输入是类型json
而不是jsonb
)。json
的
输入函数不做这些检查是由来已久的,不过它确实允许将 JSON Unicode 转义简单
的(不经处理)存储在一个非 UTF8 数据库编码中。通常,最好尽可能避免在一个非 UTF8
数据库编码的 JSON 中混入 Unicode 转义。
在把文本 JSON 输入转换成jsonb
时,RFC 7159描述
的基本类型会被有效地映射到原生的
UXDB类型(如
表?5.23中所示)。因此,在合法
jsonb
数据的组成上有一些窗拿庞蜗菲教ㄗ⒉嵬惊额外约束,它们不适合
json
类型和抽象意义上的 JSON,这些约束对应于有关哪些东西不
能被底层数据类型表示的限制。尤其是,jsonb
将拒绝位于
UXDB numeric
数据类型范
围之外的数字,而json
则不会。这类实现定义的限制是
RFC 7159 所允许的。不过,实际上这类问题更可能发生在其他实
现中,因为把 JSON 的number
基本类型表示为 IEEE 754 双精度浮点
是很常见的(这也是RFC 7159 明确期待和允许的)。当在这类系
统间使用 JSON 作为一种交换格式时,应该考虑丢失数字精度的风险。
相反地,如表中所述,有一些 JSON 基本类型输入格式上的窗拿庞蜗菲教ㄗ⒉嵬惊限制并不适用于相 应的UXDB类型。
表?5.23.?JSON 基本类型和相应的UXDB类型
JSON 基本类型 | UXDB类型 | 注释 |
---|---|---|
string | text | 不允许\u0000 ,如果数据库编码不是 UTF8,非 ASCII 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 比传统关系数据模型要灵活得多,在需求不固定时 这种优势更加令人感兴趣。在同一个应用里非常有可能有两种方法共存 并且互补。不过,即便是在要求最大灵活性的应用中,澳门游戏平台注册网站还是推荐 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": "UXDB", "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 值的顶层 查找指定的键或数组元素。
第?6.15?节中记录了多个包含和存在操作符,以及 所有其他 JSON 操作符和函数。
jsonb
索引 GIN 索引可以被用来有效地搜索在大量jsonb
文档(数据)中出现
的键或者键值对。提供了两种 GIN “操作符类”,它们在性能和灵活
性方面做出了不同的平衡。
jsonb
的默认 GIN 操作符类支持使用@>
、
?
、?&
以及?|
操作符的查询(这些
操作符实现的详细语义请见表?6.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" ] }
澳门游戏平台注册网站把这些文档存储在一个名为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'
上的应用(更多有关表达式索引的信息可见第?8.7?节)。
另一种查询的方法是利用包含,例如:
-- 寻找这样的文档:其中键 "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_ops
GIN 索引之间的技术区别是前者为数据中的每一个键和值创建独立的索引项,
而后者值为该数据中的每个值创建索引项。
[2]
基本上,每一个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 值的比较会使用低层UXDB 数据类型相同的比较规则进行。字符串的比较会使用默认的数据库排序规则。