文将为你介绍Hologres中JSON和JSONB数据类型的语法和使用方法。

使用限制

Hologres支持JSON和JSONB两种JSON数据类型,在使用时需要注意的事项如下:
  • 仅Hologres V0.9及以上版本支持JSON类型,如果您的实例是V0.9以下版本,请您提交工单或加入在线支持钉钉群申请升级实例。
  • 仅Hologres V1.1及以上版本支持JSONB类型创建GIN索引,如果您的实例是V1.1以下版本,请您提交工单或加入在线支持钉钉群申请升级实例。
  • Hologres暂不支持的函数包括如下函数:json_each、jsonb_each、json_each_text、jsonb_each_text、json_extract_path、jsonb_extract_path、jsonb_agg、jsonb_to_record。
  • JSONB不支持Flink写入。

JSON和JSONB类型概述

JSON和JSONB两种JSON数据类型,主要的区别之一是效率,具体说明如下:
  • JSON类型储存的是文本格式的数据,插入速度快,查询速度慢。JSON类型数据存储输入文本的精准拷贝,处理函数在每次执行时必须重新解析该数据。由于JSON类型数据存储的是输入文本的准确拷贝,因此可能会保留因为语法使用而存在的空格等内容。如果一个值中的JSON对象包含同一个键超过一次,所有的键/值对都会被保留( 处理函数会把最后的值当作有效值)。
  • JSONB储存的是BINARY格式的数据,插入速度慢,而查询速度快。JSONB类型数据被存储在一种分解好的二进制格式中,因为需要做附加的转换,在输入时要稍慢一些。由于不需要解析,因此在处理时要快很多。JSONB不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键,只有最后一个值会被保留。
说明 键值对的键必须使用双引号。

JSON和JSONB的操作符

JSON数据类型用来存储JSON数据,这种数据也可以被存储为TEXT类型。JSON数据类型的优势在于能强制要求每个被存储的值符合JSON规则,JSON支持的操作符使得其操作更为方便。
操作符 右操作数类型 描述 操作示例 执行结果
-> int 获得JSON数组元素(索引从0开始,负整数从末尾开始计) select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text 通过键获得JSON对象域 select '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int 以TEXT形式获得JSON数组元素 select '[1,2,3]'::json->>2 3
->> text 以TEXT形式获得JSON对象域 select '{"a":1,"b":2}'::json->>'b' 2
#> text[] 获取在指定路径的JSON对象 select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c":"foo"}
#>> text[] 以TEXT形式获取在指定路径的JSON对象 select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

额外的JSONB操作符

如下表格中为JSONB数据类型支持的操作符。
操作符 右操作数类型 描述 操作示例 执行结果
@> jsonb 左侧的JSON值是否在顶层包含右侧的JSON路径或值? select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb true
<@ jsonb 左侧的JSO 路径或值项是否被包含在右侧的JSON 值的顶层? select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb true
? text 键或元素字符串是否存在于JSON值的顶层? select '{"a":1, "b":2}'::jsonb ? 'b' true
?| text[] 数组字符串中的任何一个是否做为顶层键存在? select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] true
?& text[] 是否所有数组字符串都作为顶层键存在? select '["a", "b"]'::jsonb ?& array['a', 'b'] true
|| jsonb 将两个jsonb值串接成一个新的jsonb值 select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb ["a", "b", "c", "d"]
- text 从左操作数删除键/值对或者string元素。键/值对基于它们的键值来匹配。 select '{"a": "b"}'::jsonb - 'a' {}
- text[] 从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。 select '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] {}
- integer 删除具有指定索引(负值表示倒数)的数组元素。如果顶层容器不是数组则抛出一个错误。 select '["a", "b"]'::jsonb - 1 ["a"]
#- text[] 删除具有指定路径的域或者元素(对于JSON数组,负值表示倒数). select '["a", {"b":1}]'::jsonb #- '{1,b}' ["a", {}]

JSON创建函数

如下为可以用于创建JSON值的函数描述及操作示例。
函数 描述 操作示例 执行结果
to_json(anyelement) 此函数可以将该值返回为JSON。数组和组合会被(递归)转换成数组和对象,对于不是数组和组合的值,如果有从该类型到JSON的造型,造型函数将被用来执行该转换,否则将产生一个标量值。对于任何不是数字、布尔、空值的标量类型,将使用文本表达,使其是一个有效的JSON值。 select to_json('Fred said "Hi."'::text) "Fred said \"Hi.\""
to_jsonb(anyelement)
array_to_json(anyarray [, pretty_bool]) 此函数可以将数组作为一个JSON数组返回。一个PostgreSQL多维数组会成为一个数组的JSON数组。如果pretty_bool为真,将在第1维度的元素之间增加换行。 select array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
json_build_array(VARIADIC "any") 此函数可以从一个可变参数列表构造一个可能包含异质类型的JSON数组。 select json_build_array(1,2,'3',4,5) [1, 2, "3", 4, 5]
jsonb_build_array(VARIADIC "any")
json_build_object(VARIADIC "any") 此函数可以从一个可变参数列表构造一个JSON对象。通过转换,该参数列表由交替出现的键和值构成。 select json_build_object('foo',1,'bar',2) {"foo": 1, "bar": 2}
jsonb_build_object(VARIADIC "any")
json_object(text[]) 此函数可以从一个文本数组构造一个JSON对象。该数组必须可以是具有偶数个成员的一维数组(成员被当做交替出现的键/值对),或者是一个二维数组(每一个内部数组刚好有2个元素,可以被看做是键/值对)。 select json_object('{a, 1, b, "def", c, 3.5}'); {"a": "1", "b": "def", "c": "3.5"}
jsonb_object(text[]) select jsonb_object('{a, 1, b, "def", c, 3.5}'); {"a": "1", "b": "def", "c": "3.5"}
json_object(keys text[], values text[]) json_object的这种形式从两个独立的数组得到键/值对。在其他方面和一个参数的形式相同。 select json_object('{a, b}', '{1,2}') {"a": "1", "b": "2"}
jsonb_object(keys text[], values text[])

JSON处理函数

如下为可以用于处理JSON值的函数描述及操作示例。
函数 返回值 描述 操作示例 执行结果
json_array_length(json) int 返回最外层JSON数组中的元素数量。 select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 5
jsonb_array_length(jsonb)
json_object_keys(json) setof text 返回最外层JSON对象中的键集合。 select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
json_object_keys
------------------
 f1
 f2
jsonb_object_keys(jsonb)
json_populate_record(base anyelement, from_json json) anyelement 扩展from_json中的对象成一个行,它的列匹配由base定义的记录类型。
begin;
create table 
myrowtype( a text, b text, c text);commit;
select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | {"d": 4, "e": "a b c"}
jsonb_populate_record(base anyelement, from_json jsonb)
json_populate_recordset(base anyelement, from_json json) setof anyelement 扩展from_json中最外的对象数组为一个集合,该集合的列匹配由base定义的记录类型。
begin;
create table 
myrowtype(a text,b text);
commit;
select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
 a | b
---+---
 1 | 2
 3 | 4
jsonb_populate_recordset(base anyelement, from_json jsonb)
json_array_elements(json) setof json 把一个JSON数组扩展成一个JSON值的集合。 select * from json_array_elements('[1,true, [2,false]]')
   value
-----------
 1
 true
 [2,false]
jsonb_array_elements(jsonb) setof jsonb
json_array_elements_text(json) setof text 把一个JSON数组扩展成一个text值集合。 select * from json_array_elements_text('["foo", "bar"]')
 value
-----------
 foo
 bar
jsonb_array_elements_text(jsonb)
json_typeof(json) text 把最外层的JSON值的类型作为一个文本字符串返回。可能的类型是: object、array、string、number、 boolean以及null。 select json_typeof('-123.4') number
jsonb_typeof(jsonb)
json_strip_nulls(from_json json) json 返回from_json,其中所有具有空值的对象域都被省略。其他空值不动。 select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') [{"f1":1},2,null,3]
jsonb_strip_nulls(from_json jsonb) jsonb
jsonb_set(target jsonb, path text[], new_value jsonb[,create_missing boolean]) jsonb 返回target,其中由path指定的节用new_value替换,如果path指定的项不存在并且create_missing为真(默认为 true)则加上new_value。正如面向路径的操作符一样,出现在path中的负整数表示从JSON数组的末尾开始数。 select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false); [{"f1":[2,3,4],"f2":null},2,null,3]
select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean]) jsonb 返回被插入了new_value的target。如果path指定的target节在一个JSONB数组中,new_value将被插入到目标之前(insert_after为false,默认情况)或者之后(insert_after为真)。如果path指定的target节在一个JSONB对象内,则只有当target不存在时才插入new_value。对于面向路径的操作符来说,出现在path中的负整数表示从JSON数组的末尾开始计数。 select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') {"a": [0, "new_value", 1, 2]}
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) {"a": [0, 1, "new_value", 2]}
jsonb_pretty(from_json jsonb) text 把from_json返回成一段缩进后的JSON文本。 select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

更多关于JSON类型的用法,请参见JSON函数和操作符

JSONB索引

从Hologres V1.1版本开始,支持JSONB索引,如下为您介绍在JSONB类型字段上建GIN索引的语法解释和使用示例。

  • 语法解释
    JSONB类型支持GIN,BTree索引。一般情况下,会在JSONB类型字段上建GIN索引,建GIN索引存在三种方式,语法分别如下。
    • 使用默认的jsonb_ops操作符创建索引
      CREATE INDEX idx_name ON table_name USING gin (idx_col);
    • 使用jsonb_path_ops操作符创建索引
      CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_path_ops);
    • 使用jsonb_holo_path_ops操作符创建索引
      CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_holo_path_ops);
    三种方式的区别为:在jsonb_ops的GIN索引中,JSONB数据中的每个key和value都是作为一个单独的索引项的;而jsonb_path_ops则只为每个value创建一个索引项;jsonb_holo_path_ops为Hologres全新的操作符号,可以省去检索数据后recheck的动作。
  • 使用示例

    JSONB类型字段上建GIN索引,可以通过jsonb_opsjsonb_path_opsjsonb_holo_path_ops这三种操作符号创建索引,具体步骤及语句如下。

    • 创建jsonb_ops操作符号索引
      1. 创建表的SQL语句如下。
        CREATE TABLE IF NOT EXISTS json_table 
        (
            id INT
            ,j jsonb
        );
      2. 创建jsonb_ops操作符号索引的SQL语句如下。
        CREATE INDEX index_json on json_table USING GIN(j);
      3. 在表中插入数据的SQL语句如下。
        INSERT INTO json_table VALUES
        (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ,
        (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ;
      4. 筛选包含数据的SQL语句如下。
        SELECT  *
        FROM    json_table
        WHERE   j ? 'key1'
        ;
      5. 执行结果如下。
         id |                        j                        
        ----+-------------------------------------------------
          1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
          1 | "key1"
        (2 rows)
      6. 使用explain命令查看执行计划如下。
                                                QUERY PLAN                                         
        -------------------------------------------------------------------------------------------
         Gather  (cost=0.00..3.17 rows=1 width=43)
           ->  Exchange (Gather Exchange)  (cost=0.00..3.17 rows=1 width=43)
                 ->  Decode  (cost=0.00..3.17 rows=1 width=43)
                       ->  Bitmap Heap Scan on json_table  (cost=0.00..3.07 rows=1 width=43)
                             Recheck Cond: (j ? 'key1'::text)
                             ->  Bitmap Index Scan on index_json  (cost=0.00..0.00 rows=0 width=0)
                                   Index Cond: (j ? 'key1'::text)
         Optimizer: HQO version 0.10.0
        (8 rows)
        执行计划中出现了Index Scan步骤,表明查询过程使用了索引。
    • 创建jsonb_path_ops操作符号索引
      1. 创建表的SQL语句如下。
        CREATE TABLE IF NOT EXISTS json_table 
        (
            id INT
            ,j jsonb
        );
      2. 创建jsonb_path_ops操作符号索引的SQL语句如下。
        CREATE INDEX index_json on json_table USING GIN(j jsonb_path_ops);
      3. 插入数据的SQL语句如下。
        INSERT INTO json_table (
            SELECT 
                i, 
                ('{
                    "key1": "'||i||'"
                    ,"key2": "'||i%100||'"
                    ,"key3": "'||i%1000 ||'"
                    ,"key4": "'||i%10000||'"
                    ,"key5": "'||i%100000||'"
                }')::jsonb 
            FROM generate_series(1, 1000000) i
        ) ;
      4. 筛选包含'{"key1": "10"}'数据的SQL语句如下。
        SELECT  *
        FROM    json_table
        WHERE   j @> '{"key1": "10"}'::JSONB 
        ;
      5. 执行结果如下。
         id |                                   j                                    
        ----+------------------------------------------------------------------------
         10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
        (1 row)
      6. 使用explain命令查看执行计划如下。
                                                   QUERY PLAN                                            
        -------------------------------------------------------------------------------------------------
         Gather  (cost=0.00..34709320.40 rows=400000 width=88)
           ->  Exchange (Gather Exchange)  (cost=0.00..34709240.95 rows=400000 width=88)
                 ->  Decode  (cost=0.00..34709240.92 rows=400000 width=88)
                       ->  Bitmap Heap Scan on json_table  (cost=0.00..34709240.00 rows=400000 width=88)
                             Recheck Cond: (j @> '{"key1": "10"}'::jsonb)
                             ->  Bitmap Index Scan on index_json  (cost=0.00..0.00 rows=0 width=0)
                                   Index Cond: (j @> '{"key1": "10"}'::jsonb)
         Optimizer: HQO version 0.10.0
        (8 rows)
        执行计划中出现了Index Scan步骤,表明查询过程使用了索引。
    • 创建jsonb_holo_path_ops操作符号索引

      由于原生PostgreSQL的JSOB的GIN索引是非精确的索引,所以检索数据后需要进行recheck动作。最终导致创建索引后性能不一定提升。针对上述情况,Hologres实现了一种新的ops_class,可以省去recheck的动作,且若不指定索引操作符,系统会默认使用该操作符,具体使用方式如下。

      1. 创建表的SQL语句如下。
        CREATE TABLE IF NOT EXISTS json_table 
        (
            id INT
            ,j jsonb
        );
      2. 创建jsonb_holo_path_ops操作符号索引的SQL语句如下。
        CREATE INDEX index_json on json_table USING GIN(j jsonb_holo_path_ops);
      3. 插入数据的SQL语句如下。
        INSERT INTO json_table (
            SELECT 
                i, 
                ('{
                    "key1": "'||i||'"
                    ,"key2": "'||i%100||'"
                    ,"key3": "'||i%1000 ||'"
                    ,"key4": "'||i%10000||'"
                    ,"key5": "'||i%100000||'"
                }')::jsonb 
            FROM generate_series(1, 1000000) i
        ) ;
      4. 筛选包含'{"key1": "10"}'数据的SQL语句如下。
        SELECT  *
        FROM    json_table
        WHERE   j @> '{"key1": "10"}'::JSONB 
        ;
      5. 执行结果如下。
         id |                                   j                                    
        ----+------------------------------------------------------------------------
         10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
        (1 row)
      6. 使用explain命令查看执行计划如下。
        QUERY PLAN
        Gather  (cost=0.00..39038928.99 rows=400000 width=88)
          ->  Exchange (Gather Exchange)  (cost=0.00..39038843.49 rows=400000 width=88)
                ->  Decode  (cost=0.00..39038843.37 rows=400000 width=88)
                      ->  Bitmap Heap Scan on json_table  (cost=0.00..39038840.00 rows=400000 width=88)
        "                    Recheck Cond: (j @> '{"key1": "10"}'::jsonb)"
                            ->  Bitmap Index Scan on index_json  (cost=0.00..0.00 rows=0 width=0)
        "                          Index Cond: (j @> '{"key1": "10"}'::jsonb)"
        Optimizer: HQO version 0.10.0
        执行计划中出现了Index Scan步骤,表明查询过程使用了索引。