本文分享自华为云社区《GaussDB(DWS)——探究JSON,JSONB》，作者：yd_283975606。
1. 前言
- 适用版本：【8.1.1（及以上）】
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于将数据从服务器发送到Web应用程序。它采用人类易读和机器易解析的文本格式，基于键值对的集合，用于表示结构。
2. json/jsonb简介
json演进历程
|
版本 | |
|---|---|
|
8.1.1 |
支持JSON数据类型 |
|
8.1.2 |
支持JSONB高级特性、索引 |
|
9.1.0 |
支持JSON列存、向量化，JSONB支持索引 |
2.1 json/jsonb简介
参考DWS产品文档，JSON数据类型可以用来存储JSON（JavaScript Object Notation）数据。
可以是单独的一个标量，也可以是一个数组，也可以是一个键值对象，其中数组和对象可以统称容器(container)：
标量(scalar)：单一的数字、bool、string、null都可以叫做标量。- 数组(array)：[]结构，里面存放的元素可以是任意类型的JSON，并且不要求数组内所有元素都是同一类型。
- 对象(object)：{}结构，存储key:value的键值对，其键只能是用“”包裹起来的字符串，值可以是任意类型的JSON，对于重复的键，按最后一个键值对为准。
2.2 json与jsonb的区别
存储方式json是输入字符串的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等；
jsonb解析后存储，删除语义无关的细节和重复的键，对键值也会进行排序，使用时不用再次解析。
性能差别json由于精确拷贝，因此插入时性能较好，但是其在处理函数时，必须在每个执行上重新解析，因此其查询性能一般；
jsonb 数据以分解的二进制格式存储， 这使得它由于添加了转换机制而在输入上稍微慢些。但是其由于插入后即默认有序排列，因此可以更好地支持的额外操作（如bool关系的比较，顶层元素存在的判断）。并且，其在处理函数时， 不需要重新解析，查询性能较好。同时，jsonb支持创建btree、gist和gin索引。
3. json/jsonb输入格式
1.标量(scalar)：输入为数字、布尔类型时，使用单引号 ’ '声明，输入为字符串时必须加 " "声明
json_database=# SELECT '[1, 2, "foo", null, [[]], {}]'::jsonb;
jsonb
-------------------------------
[1, 2, "foo", null, [[]], {}]
(1 row)
2.数组(array)：使用中括号[]包裹，满足数组书写条件。数组内元素类型可以是任意合法的JSON，且不要求类型一致。
json_database=# SELECT '[1, 2, "foo", null, [[]], {}]'::jsonb;
jsonb
-------------------------------
[1, 2, "foo", null, [[]], {}]
(1 row)
3.对象(object)：使用大括号{}包裹，键必须是满足JSON字符串规则的字符串，值可以是任意合法的JSON。
json_database=# SELECT '{"a": 1, "b": {"a": 2, "b": null}}'::json;
json
-------------------------------------
{"a": 1, "b": {"a": 2, "b": null}}
(1 row)
4.嵌套数组和对象：数组array中可以是任意合法的json元素，对象object则严格遵循了key:value的格式，两者结合可以方便地有序查找json值。
json_database=# SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;
jsonb
-----------------------------------------------------
{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}
(1 row)
4. DWS的json与jsonb能力
当前DWS支持创建列存json、jsonb。
4.1 常用的json/jsonb函数及操作符(jsonb为例，json同理)
1.jsonb_object_field(jsonb, text)
描述：输入的json类型为json-object，返回指定键对应的值（可能为json-object或json-array）
对应操作符：->
返回类型：jsonb
json_database=# SELECT jsonb_object_field('{"a": {"b":"foo"}}','a');
jsonb_object_field
--------------------
{"b": "foo"}
(1 row)
json_database=# SELECT '{"a":{"b":"foo"}}'::jsonb->'a';
?column?
-------------
{"b":"foo"}
(1 row)
2.jsonb_array_element(array-jsonb, integer)
描述：输入的json类型为json-array，返回数组中指定下标的元素（为任意合法的JSON）
对应操作符：->
返回类型：jsonb
json_database=# SELECT jsonb_array_element('[1,true,[1,[2,3]],null]',2);
jsonb_array_element
---------------------
[1, [2, 3]]
(1 row)
json_database=# SELECT '[1,true,[1,[2,3]],null]'::jsonb->2;
?column?
-------------
[1, [2, 3]]
(1 row)
3.jsonb_extract_path((jsonb, VARIADIC text[])
描述：输入为json-object或json-array，返回$2所指路径的值。$2中可以为json-object对应的键值(字符串类型)，也可以为json-array对应的下标(整数类型)
对应操作符：#>
注意：GaussDB(DWS)对象标识符支持以符号"#“结尾，为避免a#>b解析过程出现歧义，因此操作符”#>"前后需要增加空格，否则解析报错。
返回类型：jsonb
json_database=# SELECT jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":["stringy",1,true]}}', 'f4','f6',2);
jsonb_extract_path
--------------------
true
(1 row)
json_database=# SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":["stringy",1,true]}}'::jsonb #> '{f4,f6,2}';
?column?
----------
true
(1 row)
4.2 jsonb高级特性
1.jsonb会丢弃空格等语义无关的细节
json_database=# select ' [1, " a ", {"a" :1 }] '::jsonb;
jsonb
----------------------
[1, " a ", {"a": 1}]
(1 row)
2.jsonb会默认对输入键值的重新排序
json_database=# insert into test_json values('{"C":1,"B":2,"A":false}','{"C":1,"B":2,"A":false}');
INSERT 0 1
json_database=# select *from test_json;
jj | jb
-------------------------+------------------------------
{"C":1,"B":2,"A":false} | {"A": false, "B": 2, "C": 1}
(1 row)
比较规则如下：
首先比较类型：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类型：长度长的 > 长度短的，长度相等则依次比较每个键值对，先比较键，再比较值。
5.总结
DWS的JSON能力总结
目前，DWS的JSON/JSONB的功能基本完善。主要体现在函数、操作符、索引功能的支持。但目前来说，JSON列存仍然采用的是直接存储JSON数据，即将原始的JSON数据存成单独的一列，以完整的JSON值作为最小的粒度在磁盘上，具体如下：
|
json_data |
|---|
|
{“user_id”:1001, “user_name”: “Adam”, “gender”: “Male”, “age”: 16} |
|
{“user_id”:1002, “user_name”: “Bob”, “gender”: “Male”, “age”: 41} |
|
{“user_id”:1003, “user_name”: “Clair”, “gender”: “Female”, “age”: 21} |
优点是：JSON则天然支持Schema Evoluation，上游业务的变更，只需要在JSON列数据中进行增删相应的字段，无需对数仓中的表做任何DDL就能完成，也能对中间的ETL作业做到透明，最大程度地保留了半结构化数据的易用性和灵活性，能大大降低维护和管理表结构的成本。
缺点是：应用端查询时需要选择合适的处理函数和方法，才能解析到需要的数据，开发较为复杂，如果JSON较复杂，同时查询性能会有退化，因为每次JSON列的数据参与计算的时候，都需要对JSON数据完整的解析一遍，比如需要抽取出整个JSON中某个字段，那么查询引擎执行的时候就要读出每一行JSON，解析一遍，取出需要的字段再返回。这中间会涉及大量的IO和计算，而需要的可能只是JSON数据成百上千字段当中的一个字段，这中间的大量IO和计算都是浪费的。
另外，当前云原生分支上JSON的向量化支持仍然是沿用的通用的向量化框架，没有定制化的向量化函数。通用的向量化函数框架本质上来说仍然为行存的调用，并不是完全意义上的向量化。
后续演进路线
如上所述，后续想要提升JSON/JSONB的查询性能，首先必须提升JSON的存储方式，即在解析前端将JSON拍平成宽表，真正意义上发挥JSON半结构化数据的优势。
|
user_id |
user_name |
gender |
age |
|---|---|---|---|
|
1001 |
Adam |
Male |
16 |
|
1002 |
Bob |
Male |
41 |
|
1003 |
Clair |
Female |
21 |
这种做法的优点是：写入DWS时，因为是普通列写入，所以写入性能会更好，同时在查询侧，不需要对JSON数据进行解析，查询性能也会更好。
缺点是：每当上游的数据格式有变更时，比如变更数据类型、增删字段、执行DDL进行加列或者删列，中间的实时数据ETL作业也需要进行适配改动并重新上线，使用非常不灵活，也会额外增加运维和开发负担。并且当JSON的每一个键值都为一列，若出现异常数据，可能导致列数的急速膨胀，进而影响性能。
当前Hologres的方案类似，但其对特殊的列采用单独列（属性为JSON）存储那些同质化不强的json键值（极少数出现的json键值）
另外，当前列存JSON的性能当前瓶颈点在于向量化的性能，一方面需要提升通用当前DWS的向量化能力，另一方面也可以考虑对json函数做出优化。
标签：数仓,jsonb,database,JSON,json,键值,array,轻量级 From： https://www.cnblogs.com/huaweiyun/p/18145941