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