更新日期：2024年09月11日
JSON(JavaScript Object Notation)数据，可以是单独的一个标量，也可以是一个数组，也
可以是一个键值对象。其中数组和对象可以统称容器(container)：
标量(scalar)：单一的数字、bool、string、null 都可以叫做标量。
数组(array)：
[]结构，
里面存放的元素可以是任意类型的JSON，
并且不要求数组内所有
元素都是同一类型。
对象(object)：{}结构，存储key:value 的键值对，其键只能是用“”包裹起来的字符串，
值可以是任意类型的JSON，对于重复的键，按最后一个键值对为准。
GBase 8c 支持两种存储JSON 数据的类型：JSON 和JSONB。其中JSON 是对输入的字
符串的完整拷贝，使用时再去解析，所以它会保留输入的空格、重复键以及顺序等；JSONB
解析输入后保存的二进制，
它在解析时会删除语义无关的细节和重复的键，
对键值也会进行
排序，使用时不用再次解析。
二者接受输入相同的字符串。但差别在于处理效率：JSON 数据类型存储输入文本的精
确拷贝，处理函数必须在每个执行上重新解析；而JSONB 数据类型以分解的二进制格式存
储，这使得由于转换机制而在输入过程略慢些，但是在处理过程明显更快，因为不需要重新
解析。同时，由于JSONB 类型存在解析后的格式归一化等操作，同等语义下只会有一种格
式。因此，JSONB 能够更好地支持一些额外操作，如按照特定规则进行大小比较等。此外，
JSONB 还支持索引。
输入格式
输入必须是一个符合JSON 数据格式的字符串，此字符串用单引号''声明。
null (null-json)：仅null，全小写。
gbase=# select 'null'::json;
json
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
75
------
null
(1 row)
gbase=# select 'NULL'::jsonb;
ERROR:
invalid input syntax for type json
LINE 1: select 'NULL'::jsonb;
^
DETAIL:
Token "NULL" is invalid.
CONTEXT:
JSON data, line 1: NULL
referenced column: jsonb
数字(num-json)：正负整数、小数、0，支持科学计数法。
gbase=# select '1'::json;
json
------
1
(1 row)
gbase=# select '-1.5'::json;
json
------
-1.5
(1 row)
gbase=# select '-1.5e-5'::jsonb, '-1.5e+2'::jsonb;
jsonb
| jsonb
----------+-------
-.000015 | -150
(1 row)
gbase=# select '001'::json, '+15'::json, 'NaN'::json;
ERROR:
invalid input syntax for type json
LINE 1: select '001'::json, '+15'::json, 'NaN'::json;
^
DETAIL:
Token "001" is invalid.
CONTEXT:
JSON data, line 1: 001
referenced column: json
-- 结果表明，
JSON 数据类型格式不支持多余的前导0，
正数的+号，
以及NaN 和infinity。
布尔(bool-json)：仅true、false，全小写。
gbase=# select 'true'::json;
json
------
true
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
76
(1 row)
gbase=# select 'false'::jsonb;
jsonb
-------
false
(1 row)
字符串(str-json)：必须是加双引号的字符串。
gbase=# select '"a"'::json;
json
------
"a"
(1 row)
gbase=# select '"abc"'::jsonb;
jsonb
-------
"abc"
(1 row)
数组(array-json)：使用中括号[]包裹，满足数组书写条件。数组内元素类型可以是任意
合法的JSON，且不要求类型一致。
gbase=# select '[1, 2, "foo", null]'::json;
json
---------------------
[1, 2, "foo", null]
(1 row)
gbase=# select '[]'::json;
json
------
[]
(1 row)
gbase=# select '[1, 2, "foo", null, [[]], {}]'::jsonb;
jsonb
-------------------------------
[1, 2, "foo", null, [[]], {}]
(1 row)
对象(object-json)：使用大括号{}包裹，键必须是满足JSON 字符串规则的字符串，值可
以是任意合法的JSON。
gbase=# select '{}'::json;
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
77
json
------
{}
(1 row)
gbase=# select '{"a": 1, "b": {"a": 2, "b": null}}'::json;
json
------------------------------------
{"a": 1, "b": {"a": 2, "b": null}}
(1 row)
gbase=# select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;
jsonb
-----------------------------------------------------
{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}
(1 row)
'null'::json 和null::json 是两个不同的概念，
类似字符串str="" 和str=null 的区别。
对于数字，当使用科学计数法的时候，jsonb 类型会将其展开，而json 会精准拷贝
输入。
JSONB 高级特性
注意事项
不支持列存。
不支持作为分区键。
不支持外表、mot。
JSON 和JSONB 的主要差异在于存储方式上的不同，
JSONB 存储的是解析后的二进制，
能够体现JSON 的层次结构，更方便直接访问等，因此JSONB 会有很多JSON 所不具有的
高级特性。
格式归一化
对于输入的object-json 字符串，解析成jsonb 二进制后，会丢弃语义上无关紧
要的细节，比如空格。如下示例：
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
78
gbase=# select '
[1, " a ", {"a"
:1
}] '::jsonb;
jsonb
----------------------
[1, " a ", {"a": 1}]
(1 row)
对于object-json，会删除重复的键值，只保留最后一个出现的。如下示例：
gbase=# select '{"a" : 1, "a" : 2}'::jsonb;
jsonb
----------
{"a": 2}
(1 row)
对于object-json，
键值会重新进行排序，
排序规则：
长度长的在后、
长度相等则ascii
码大的在后。如：
gbase=# select '{"aa" : 1, "b" : 2, "a" : 3}'::jsonb;
jsonb
---------------------------
{"a": 3, "b": 2, "aa": 1}
(1 row)
大小比较
由于经过了格式归一化，保证了同一种语义下的JSONB 只会有一种存在形式。因此可
以按照制定的规则，进行大小比较。比较类型排序规则为：object-jsonb > array-jsonb >
bool-jsonb > num-jsonb > str- jsonb > null-jsonb
同类型则比较内容：
str-json 类型：依据text 比较的方法，使用数据库默认排序规则进行比较，返回值
正数代表大于，负数代表小于，0 表示相等。
num-json 类型：数值比较
bool-json 类型：true > false
array-jsonb 类型：长度长的> 长度短的，长度相等则依次比较每个元素。
object-jsonb 类型：长度长的> 长度短的，长度相等则依次比较每个键值对，先比
较键，在比较值。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
79
object-jsonb 类型内比较时，是对格式整理后的结果进行比较，因此相对于直接输入的
形式，未必会很直观。
创建索引、主外键
BTREE 索引
JSONB 类型支持创建btree 索引，支持创建主键、外键。
GIN 索引
GIN 索引可以用来有效的搜索出现在大量JSONB 文档（datums）中的键或者键/值对。
提供了两个GIN 操作符类(jsonb_ops、jsonb_hash_ops)，提供了不同的性能和灵活性取舍。
缺省的GIN 操作符类支持使用@>、<@、?、?&和?| 操作符查询，非缺省的GIN 操作符类
jsonb_path_ops 只支持索引@>、<@操作符。
相关的操作符请参见5.13 JSON/JSONB 函数和操作符。
包含存在
查询JSON 中是否包含某些元素，或者某些元素是否存在于某个JSON 中，是JSONB
类型的一个重要能力。
-- 简单的标量/原始值只包含相同的值：
gbase=# SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
?column?
----------
t
(1 row)
-- 左侧数组包含了右侧字符串。
gbase=# SELECT '[1, “aa”, 3]'::jsonb ? 'aa';
ERROR:
invalid input syntax for type json
LINE 1: SELECT '[1, “aa”, 3]'::jsonb ? 'aa';
^
DETAIL:
Token "“aa”" is invalid.
CONTEXT:
JSON data, line 1: [1, “aa”...
-- 左侧数组包含了右侧的数组所有元素，顺序、重复不重要。
GBase 8c SQL 参考手册
南大通用数据技术股份有限公司
80
gbase=# SELECT '[1, 2, 3]'::jsonb @> '[1, 3, 1]'::jsonb;
?column?
----------
t
(1 row)
-- 左侧object-json 包含了右侧object-json 的所有键值对。
gbase=# SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb":true}'::jsonb
@> '{"version":9.4}'::jsonb;
?column?
----------
t
(1 row)
-- 左侧数组并没有包含右侧的数组所有元素，因为左侧数组的三个元素为1、2、[1,3]，
右侧的为1、3。
gbase=# SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
?column?
----------
f
(1 row)
-- 比较相似值但不一致，结果值应返回为false。
gbase=# SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;
?column?
----------
f
(1 row)
相关的操作符请参见5.13 JSON/JSONB 函数和操作符。
函数和操作符
JSON/JSONB 类型相关支持的函数和操作符，参见5.13 JSON/JSONB 函数和操作符。