数仓的两种轻量级数据交换格式：json 与 jsonb

本文分享自华为云社区《GaussDB(DWS)——探究JSON,JSONB》，作者：yd_283975606。

1. 前言

• 适用版本：【8.1.1（及以上）】

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于将数据从服务器发送到 Web 应用程序。它采用人类易读和机器易解析的文本格式，基于键值对的集合，用于表示结构。

2. json/jsonb 简介

json 演进历程

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 1json_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 则天然支持 Schema Evoluation，上游业务的变更，只需要在 JSON 列数据中进行增删相应的字段，无需对数仓中的表做任何 DDL 就能完成，也能对中间的 ETL 作业做到透明，最大程度地保留了半结构化数据的易用性和灵活性，能大大降低维护和管理表结构的成本。

缺点是：应用端查询时需要选择合适的处理函数和方法，才能解析到需要的数据，开发较为复杂，如果 JSON 较复杂，同时查询性能会有退化，因为每次 JSON 列的数据参与计算的时候，都需要对 JSON 数据完整的解析一遍，比如需要抽取出整个 JSON 中某个字段，那么查询引擎执行的时候就要读出每一行 JSON，解析一遍，取出需要的字段再返回。这中间会涉及大量的 IO 和计算，而需要的可能只是 JSON 数据成百上千字段当中的一个字段，这中间的大量 IO 和计算都是浪费的。

另外，当前云原生分支上 JSON 的向量化支持仍然是沿用的通用的向量化框架，没有定制化的向量化函数。通用的向量化函数框架本质上来说仍然为行存的调用，并不是完全意义上的向量化。

后续演进路线

如上所述，后续想要提升 JSON/JSONB 的查询性能，首先必须提升 JSON 的存储方式，即在解析前端将 JSON 拍平成宽表，真正意义上发挥 JSON 半结构化数据的优势。

这种做法的优点是：写入 DWS 时，因为是普通列写入，所以写入性能会更好，同时在查询侧，不需要对 JSON 数据进行解析，查询性能也会更好。

缺点是：每当上游的数据格式有变更时，比如变更数据类型、增删字段、执行 DDL 进行加列或者删列，中间的实时数据 ETL 作业也需要进行适配改动并重新上线，使用非常不灵活，也会额外增加运维和开发负担。并且当 JSON 的每一个键值都为一列，若出现异常数据，可能导致列数的急速膨胀，进而影响性能。

当前 Hologres 的方案类似，但其对特殊的列采用单独列（属性为 JSON）存储那些同质化不强的 json 键值（极少数出现的 json 键值）

另外，当前列存 JSON 的性能当前瓶颈点在于向量化的性能，一方面需要提升通用当前 DWS 的向量化能力，另一方面也可以考虑对 json 函数做出优化。

点击关注，第一时间了解华为云新鲜技术~