数仓的两种轻量级数据交换格式：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 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

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net

相关推荐: 记录工作过程中一次业务优化

1需求 用户需要输入身份证和姓名进行登录，登录时需要判断是否存在在数据库存在，登录成功后需要记录登录的信息以及微信Id，如果用到不同的微信账号，需要记录多次记录。 2设计 2.1数据库设计 CREATE TABLE `ExamDefine` ( `Id` va…