全部产品
Search
文档中心

实时数仓Hologres:JSON和JSONB类型

更新时间:Dec 28, 2023

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

JSON和JSONB介绍

近年来随着移动端应用的普及,应用埋点、用户标签计算等场景开始诞生,为了更好的支撑这类场景,越来越多的大数据系统开始使用半结构化格式来存储此类数据,以获得更加灵活的开发和处理。常用的半结构化类型为JSON和JSONB,在实际业务中JSON和JSONB的区别如下:

  • JSON储存的是文本格式的数据,JSONB储存的是Binary格式的数据。

  • JSON插入速度快,查询速度慢,原因是处理函数必须在每次执行时重新解析该数据。JSONB插入速度慢,而查询速度快,原因是JSONB数据被存储在一种分解好的二进制格式中,因为需要做附加的转换,它在输入时要稍慢一些。但是JSONB在查询数据时快很多,因为不需要重新解析。

  • JSON储存的数据是对数据的完整拷贝,会保留源数据的空格、重复键和顺序等,如果一个值中的JSON对象包含同一个键超过一次,所有的键、值对都会被保留。而JSONB在解析时会删除掉不必要的空格、重复键和数据的顺序等,如果在输入中指定了重复的键,只有最后一个值会被保留。

JSON和JSONB两种JSON数据类型,主要的区别之一是效率,具体说明如下:

  • JSON类型数据存储输入文本的精准拷贝,处理函数在每次执行时必须重新解析该数据。由于JSON类型数据存储的是输入文本的准确拷贝,因此可能会保留因为语法使用而存在的空格等内容。如果一个值中的JSON对象包含同一个键超过一次,所有的键值对都会被保留( 处理函数会把最后的值当作有效值)。

  • JSONB类型数据被存储在一种分解好的二进制格式中,因为需要做附加的转换,在输入时要稍慢一些。由于不需要解析,因此在处理时要快很多。JSONB不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键,只有最后一个值会被保留。

使用限制

Hologres支持JSON和JSONB两种JSON数据类型,在使用时需要注意的事项如下:

  • 仅Hologres V0.9及以上版本支持JSON类型,如果您的实例是V0.9以下版本,请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

  • 仅Hologres V1.1及以上版本支持JSONB类型创建GIN索引。

  • 仅Hologres V1.3及以上版本支持JSONB类型开启列式存储,且JSONB的列存优化仅能用于列存表,行存表暂不支持,并且至少1000条数据才会触发列存优化。

  • Hologres暂不支持的函数包括如下函数:json_each、jsonb_each、json_each_text、jsonb_each_text、json_extract_path、jsonb_extract_path、jsonb_to_record。

    如果您需要使用jsonb_extract_path和json_extract_path函数可以使用如下等价写法。

    • SELECT json_extract_path(
                              '{"key":{"key1":"key1","key2":"key2"}}'::json
                              , 'key'
                              , 'key1'
      );
      --json_extract_path函数的等价写法如下
      SELECT
          '{"key":{"key1":"key1","key2":"key2"}}'::json #> '{"key","key1"}';
    • SELECT jsonb_extract_path(
                  '{"key":{"key1":"key1","key2":"key2"}}'::jsonb
                  , 'key'
                  , 'key1'
      );
      -- jsonb_extract_path函数的等价写法如下
      SELECT
          '{"key":{"key1":"key1","key2":"key2"}}'::jsonb #> '{"key","key1"}';                                    

JSON和JSONB操作符

常用JSON和JSONB操作符

常用的JSON和JSONB操作符如下表所示:

操作符

右操作数类型

描述

操作示例

执行结果

->

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

额外的JSON和JSONB操作符

除了常用的JSON和JSONB操作符之外,也支持如下额外操作符,满足不同业务开发需求。

操作符

右操作数类型

描述

操作示例

执行结果

@>

jsonb

左侧的JSON值是否包含右侧的JSON路径或值。

select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

true

<@

jsonb

左侧的JSON路径或值是否被包含在右侧的JSON值中。

select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb

true

?

text

键或元素字符串是否存在于JSON值中。

select '{"a":1, "b":2}'::jsonb ? 'b'

true

?|

text[]

数组字符串中的任何一个键或元素字符串是否存在于JSON值中。

select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']

true

?&

text[]

是否所有数组字符串都存在于JSON值中。

select '["a", "b"]'::jsonb ?& array['a', 'b']

true

||

jsonb

将两个JSONB值串接成一个新的JSONB值。

说明

||操作符将其每一个JSON中的操作元素串接起来,但不会递归操作。例如:两个操作数都是具有相同键字段名称的对象,则结果中字段的值将恰好是右侧操作数的值。

select '["a", "b"]'::jsonb || '["c", "d"]'::jsonb

["a", "b", "c", "d"]

-

text

根据键值从左操作数开始删除键或者值。

select '{"a": "b"}'::jsonb - 'a'

{}

-

text[]

根据键值从左操作数开始删除多个键或者值。

select '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]

{}

-

integer

删除指定位置的数组元素(负值表示倒数)。如果JSON不是数组则抛出一个错误。

select '["a", "b"]'::jsonb - 1

["a"]

#-

text[]

删除具有指定路径元素(对于JSON数组,负值表示倒数)。

select '["a", {"b":1}]'::jsonb #- '{1,b}'

["a", {}]

JSON和JSONB函数

JSON和JSONB处理函数

如下为可以用于处理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
]

jsonb_agg

jsonb

将值(包括空值)聚合为JSON数组。

DROP TABLE IF EXISTS t;

CREATE TABLE t (
    k int PRIMARY KEY,
    class int NOT NULL,
    v text NOT NULL
);

INSERT INTO t (k, class, v)
SELECT
    (1 + s.v),
    CASE (s.v) < 3
    WHEN TRUE THEN
        1
    ELSE
        2
    END,
    chr(97 + s.v)
FROM
    generate_series(0, 5) AS s (v);

SELECT
    class,
    jsonb_agg(v ORDER BY v DESC) FILTER (WHERE v <> 'b') AS "jsonb_agg",
    jsonb_object_agg(v, k ORDER BY v DESC) FILTER (WHERE v <> 'e') AS "jsonb_object_agg(v, k)"
FROM
    t
GROUP BY
    class;
 class |    jsonb_agg    |  jsonb_object_agg(v, k)
-------+-----------------+--------------------------
     1 | ["c", "a"]      | {"a": 1, "b": 2, "c": 3}
     2 | ["f", "e", "d"] | {"d": 4, "f": 6}

jsonb_object_agg

jsonb

将Key/Value对聚合为JSON对象,值可以为空,但名称不能为空。

DROP TABLE IF EXISTS t;

CREATE TABLE t (
    k int PRIMARY KEY,
    class int NOT NULL,
    v text NOT NULL
);

INSERT INTO t (k, class, v)
SELECT
    (1 + s.v),
    CASE (s.v) < 3
    WHEN TRUE THEN
        1
    ELSE
        2
    END,
    chr(97 + s.v)
FROM
    generate_series(0, 5) AS s (v);

SELECT
    class,
    jsonb_agg(v ORDER BY v DESC) FILTER (WHERE v <> 'b') AS "jsonb_agg",
    jsonb_object_agg(v, k ORDER BY v DESC) FILTER (WHERE v <> 'e') AS "jsonb_object_agg(v, k)"
FROM
    t
GROUP BY
    class;
 class |    jsonb_agg    |  jsonb_object_agg(v, k)
-------+-----------------+--------------------------
     1 | ["c", "a"]      | {"a": 1, "b": 2, "c": 3}
     2 | ["f", "e", "d"] | {"d": 4, "f": 6}

is_valid_json

BOOLEAN

IS_VALID_JSON函数用于验证JSON字符串,如果字符串格式是正确的JSON字符串,则该函数返回布尔值true(t);如果字符串格式不正确,函数将返回false (f)。

说明
  • 仅Hologres V1.3.12及以上版本支持此函数。

  • ARRAY类型也算是一种JSON类型,也可以被写入JSON/JSONB类型的列。

DROP TABLE IF EXISTS test_json;

CREATE TABLE test_json (
    id int,
    json_strings text
);

INSERT INTO test_json
    VALUES (1, '{"a":2}'), (2, '{"a":{"b":{"c":1}}}'), (3, '{"a": [1,2,"b"]}');

INSERT INTO test_json
    VALUES (4, '{{}}'), (5, '{1:"a"}'), (6, '[1,2,3]');

SELECT
    id,
    json_strings,
    is_valid_json (json_strings)
FROM
    test_json
ORDER BY
    id;
id | json_strings        | is_valid_json
---+---------------------+--------------
 0 | {"a":2}             | true
 2 | {"a":{"b":{"c":1}}} | true
 4 | {"a": [1,2,"b"]}    | true
 6 | {{}}                | false
 8 | {1:"a"}             | false
10 | [1,2,3]             | true

解析函数

函数

描述

操作示例

执行结果

try_cast_to_jsonb(text)

将TEXT类型转换为JSONB类型。如果文本字段不符合JSONB格式,将返回NULL值。

说明

仅Hologres V2.0.24及以上版本支持该函数。

  • select try_cast_to_jsonb('{"key": 1}');

  • select try_cast_to_jsonb('{"key": 1');

  • {"key": 1}

  • NULL

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[])

JSONB索引

从Hologres V1.1版本开始,在查询层支持JSONB类型的GIN, BTree索引加速查询。在JSONB上创建GIN索引的方式有两种:使用默认的jsonb_ops操作符创建和使用jsonb_path_ops操作符创建。

说明

当前索引都是针对JSONB类型,请尽量使用JSONB类型而不是JSON类型。

  • 使用默认的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_ops的GIN索引中,JSONB数据中的每个key和value都是作为一个单独的索引项的,而jsonb_path_ops则只为每个value创建一个索引项。

GIN索引可以通过原生PostgreSQL操作符和Hologres操作符实现,使用示例如下。

原生PostgreSQL操作符

  • 创建jsonb_ops操作符索引。

    --1、创建表
    BEGIN;
    DROP TABLE IF EXISTS json_table;
    
    CREATE TABLE IF NOT EXISTS json_table
    (
        id INT
        ,j jsonb
    );
    COMMIT;
    
    --2、创建jsonb_ops操作符索引
    CREATE INDEX index_json on json_table USING GIN(j);
    
    --3、插入数据
    INSERT INTO json_table VALUES
    (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ,
    (1, '{"key1": 1}'),
    (2, '{"key2": [1, 2], "key3": {"a": "b"}}') ;
    
    --4、查询数据
    SELECT  * FROM    json_table WHERE   j ? 'key1';
    --返回结果
     id |                        j
    ----+-------------------------------------------------
      1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
      1 | {"key1": 1}
    
                        

    使用explain命令查看执行计划如下。

    explain SELECT  * FROM    json_table WHERE   j ? 'key1';
    
    QUERY PLAN
    Gather  (cost=0.00..0.26 rows=1000 width=12)
      ->  Local Gather  (cost=0.00..0.23 rows=1000 width=12)
            ->  Decode  (cost=0.00..0.23 rows=1000 width=12)
                  ->  Bitmap Heap Scan on json_table  (cost=0.00..0.13 rows=1000 width=12)
                        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 1.3.0

    执行计划中出现了Index Scan步骤,表明查询过程使用了索引。

  • 创建jsonb_path_ops操作符索引。

    --1、创建表
    BEGIN;
    DROP TABLE IF EXISTS json_table;
    
    CREATE TABLE IF NOT EXISTS json_table
    (
        id INT
        ,j jsonb
    );
    COMMIT;
    
    --2、创建jsonb_ops操作符索引
    CREATE INDEX index_json on json_table USING GIN(j jsonb_path_ops);
    
    --3、插入数据
    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"}'的数据
    SELECT  * FROM    json_table WHERE   j @> '{"key1": "10"}'::JSONB;
    --返回结果
     id |                                   j
    ----+------------------------------------------------------------------------
     10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
    (1 row)
                        

    使用explain命令查看执行计划如下。

    explain SELECT  * FROM    json_table WHERE   j @> '{"key1": "10"}'::JSONB;
    
                                            QUERY PLAN
    -------------------------------------------------------------------------------------------
     Gather  (cost=0.00..0.26 rows=1000 width=12)
       ->  Local Gather  (cost=0.00..0.23 rows=1000 width=12)
             ->  Decode  (cost=0.00..0.23 rows=1000 width=12)
                   ->  Bitmap Heap Scan on json_table  (cost=0.00..0.13 rows=1000 width=12)
                         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 1.3.0
    (8 rows)
                        

    执行计划中出现了Index Scan步骤,表明查询过程使用了索引。

Hologres操作符

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

说明

使用该操作符时,由于最大存储127个字节,超过该长度的索引会被截断,所以JSONB字段超长时还是会被截断,从而发生recheck的操作。是否有recheck行为,可以使用EXPLAIN ANALYZE语句查看物理执行计划验证。

其中jsonb_holo_ops对应jsonb_ops,支持?, ?|, ?&, @>的过滤操作。其中jsonb_holo_path_ops对应jsonb_path_ops,仅支持@>的过滤操作。

  • 创建jsonb_holo_ops操作符号索引。

    --1、创建表
    BEGIN ;
    DROP TABLE IF EXISTS json_table;
    CREATE TABLE IF NOT EXISTS json_table
    (
        id INT
        ,j jsonb
    );
    COMMIT ;
    
    --2、创建索引,使用jsonb_holo_ops操作符
    CREATE INDEX index_json on json_table USING GIN(j jsonb_holo_ops);
    
    --3、插入数据
    INSERT INTO json_table VALUES
    (1, '{"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}') ,
    (1, '{"key1": 1}'),
    (2, '{"key2": [1, 2], "key3": {"a": "b"}}') ;
    
    --4、查询
    SELECT  * FROM    json_table WHERE   j ? 'key1';
    --返回结果
     id |                        j
    ----+-------------------------------------------------
      1 | {"key1": 1}
      1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
    (2 rows)
  • 创建jsonb_holo_path_ops操作符号索引。

    --1、创建表
    BEGIN ;
    DROP TABLE IF EXISTS json_table;
    CREATE TABLE IF NOT EXISTS json_table
    (
        id INT
        ,j jsonb
    );
    
    --2、创建索引,使用jsonb_holo_path_ops操作符
    CREATE INDEX index_json on json_table USING GIN(j jsonb_holo_path_ops);
    
    --3、插入数据
    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"}'的数据
    SELECT  * FROM  json_table WHERE j @> '{"key1": "10"}'::JSONB ;
    --返回结果
     id |                                   j
    ----+------------------------------------------------------------------------
     10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
    (1 row)

数据导入示例:通过Flink实时导入JSONB数据

通过Flink将数据导入Hologres时,在Flink的SQL作业中,需要将字段定义为Flink的数据类型;只有在Hologres中创建内部表时,才需要将字段定义为Hologres的数据类型,Flink与Hologres数据类型映射请参见Blink/Flink与Hologres的数据类型映射

如果您期望使用Flink将JSON数据写入Hologres,则需要在Flink SQL作业中,将源表、结果表中的JSON数据字段定义为VARCHAR类型,仅在Hologres内部表中将其定义为JSONB类型,示例如下:

  • Hologres中创建内部表,将message字段定义为JSONB类型:

    BEGIN ;
    DROP TABLE IF EXISTS holo_internal_table;
    CREATE TABLE IF NOT EXISTS holo_internal_table
    (
        id BIGINT NOT NULL,
        message JSONB NOT NULL
    );
    CALL set_table_property('holo_internal_table', 'distribution_key', 'id');
    COMMIT ;
  • Flink作业中将源表、结果表的message字段定义为VARCHAR类型,并写入Hologres:

    CREATE TEMPORARY TABLE randomSource (
        id BIGINT,
        message VARCHAR
      )
    WITH ('connector' = 'datagen');
    
    CREATE TEMPORARY TABLE sink_holo (
        id BIGINT,
        message VARCHAR
      )
    WITH (
         'connector' = 'hologres',
        'dbname'='<yourDBname>',  --Hologres的数据库名称。
        'tablename'='<holo_internal_table>',  --Hologres用于接收数据的表名称。
        'username'='<yourUsername>',  --当前阿里云账号的AccessKey ID。
        'password'='<yourPassword>',  --当前阿里云账号的AccessKey Secret。
        'endpoint'='<yourEndpoint>',  --当前Hologres实例VPC网络的Endpoint。
      );
    
    INSERT INTO sink_holo
    SELECT 
      1,
      '{"k":"v"}'
    FROM
      randomSource;

高级调优:列式JSONB存储

GIN索引只是计算层的优化,实际计算时还需要扫描整个JSON内容。为了提升JSONB数据的查询效率,Hologres从 V1.3版本开始支持JSONB数据的列式存储,通过存储层的优化,使得JSONB数据能够像结构化数据一样按列式存储,有效提高数据的压缩效率,同时也提升JSONB数据的查询效率。

JSONB列式存储详情请参见列式JSONB