Hologres:JSONデータ型とJSONBデータ型

演算子

右オペランド型

説明

例

実行結果

->

int

0から始まるインデックスを持つJSON配列要素を取得します。負の整数は、末尾から逆方向に要素がカウントされることを示します。

select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2

{"c":"baz"}

->

text

キーに基づいてJSONオブジェクトフィールドを取得します。

select '{"a": {"b":"foo"}}'::json->'a'

{"b":"foo"}

->>

int

JSON配列要素をテキストとして取得します。

select '[1,2,3]'::json->>2

3

->>

text

JSONオブジェクトフィールドをテキストとして取得します。

select '{"a":1,"b":2}'::json->>'b'

2

#>

text[]

指定されたパスからJSONオブジェクトを取得します。

select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'

{"c":"foo"}

#>>

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

2つの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)

jsonb の集合

json_array_elements_text(json)

setof text

JSON 配列をテキスト値のセットに展開します。

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 パラメーター内のオブジェクトを返します。null 値を持つオブジェクトフィールドは省略されます。その他の null 値は保持されます。

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 パラメータの値に置き換えられます。create_missing パラメータの値が true で、path パラメータで指定された項目が存在しない場合、new_value パラメータの値が挿入されます。create_missing パラメータのデフォルト値は true です。パス指向演算子の要件に従って、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 パラメータで指定されたノードは、JSONB 配列内にある場合があります。この場合、insert_after パラメータの値が false（デフォルト値）の場合、new_value パラメータの値は target パラメータの値の前に挿入されます。それ以外の場合、new_value パラメータの値は target パラメータの値の後に挿入されます。path パラメータで指定されたノードは、JSONB オブジェクト内にある場合があります。この場合、new_value パラメータの値は、target パラメータの値が存在しない場合にのみ挿入されます。パス指向演算子の要件に従って、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

NULL 値を含む値を 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

キーと値のペアを 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

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