实时数仓Hologres：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

操作符

右操作数类型

描述

操作示例

执行结果

@>

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值。

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_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）。

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