Hologres:JSON and JSONB data types

Operator

Right operand type

Description

Example

Execution result

->

int

Obtains a JSON array element with indexes starting from zero. A negative integer indicates that elements are counted backward from the end.

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

{"c":"baz"}

->

text

Obtains a JSON object field based on a key.

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

{"b":"foo"}

->>

int

Obtains a JSON array element as text.

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

3

->>

text

Obtains a JSON object field as text.

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

2

#>

text[]

Obtains a JSON object from the specified path.

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

{"c":"foo"}

#>>

text[]

Obtains a JSON object as text from the specified path.

select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

3

Operator

Right operand type

Description

Example

Execution result

@>

jsonb

Specifies whether the left JSON value contains the right JSON path or value.

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

true

<@

jsonb

Specifies whether the left JSON path or value is contained in the right JSON value.

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

true

?

text

Specifies whether a key or an element string exists in the JSON value.

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

true

?|

text[]

Specifies whether a key or an element string in an array string exists in the JSON value.

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

true

?&

text[]

Specifies whether all array strings exist in the JSON value.

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

true

||

jsonb

Concatenates two JSONB values into a new JSONB value.

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

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

-

text

Deletes a key or a value from the left operand. The key or value is matched based on the key value.

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

{}

-

text[]

Deletes multiple keys or values from the left operand. The keys or values are matched based on the key values.

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

{}

-

integer

Deletes an array element that is in the specified position. A negative integer indicates that elements are counted backward from the end. An error is returned if the JSON data is not stored as an array.

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

["a"]

#-

text[]

Deletes an element with the specified path. For JSON arrays, a negative integer indicates that elements are counted backward from the end.

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

["a", {}]

Function

Type of the return value

Description

Example

Execution result

json_array_length(json)

int

Returns the number of elements in the outermost JSON array.

select json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')

5

jsonb_array_length(jsonb)

json_object_keys(json)

setof text

Returns a set of keys in the outermost JSON object.

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

Expands the objects in the from_json parameter into a row with columns that match the record type defined by the base parameter.

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

Expands the outermost array of objects in the from_json parameter into a set of rows with columns that match the record type defined by the base parameter.

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

Expands a JSON array into a set of JSON values.

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

Expands a JSON array into a set of text values.

select * from json_array_elements_text('["foo", "bar"]')

 value
-----------
 foo
 bar

jsonb_array_elements_text(jsonb)

json_typeof(json)

text

Returns the data type of the outermost JSON value as a text string. Possible data types include OBJECT, ARRAY, STRING, NUMBER, BOOLEAN, and NULL.

select json_typeof('-123.4')

number

jsonb_typeof(jsonb)

json_strip_nulls(from_json json)

json

Returns the objects in the from_json parameter. Object fields that have null values are omitted. Other null values are retained.

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

Returns the objects in the target parameter. The section specified by the path parameter is replaced by the value of the new_value parameter. If the value of the create_missing parameter is true and the item specified by the path parameter does not exist, the value of the new_value parameter is inserted. The default value of the create_missing parameter is true. As required by the path-oriented operators, a negative integer in the value of the path parameter indicates that elements are counted backward from the end of JSON arrays.

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

Returns the objects in the target parameter with the value of the new_value parameter inserted. The section specified by the path parameter may be in a JSONB array. In this case, if the value of the insert_after parameter is false, which is the default value, the value of the new_value parameter is inserted before the value of the target parameter. Otherwise, the value of the new_value parameter is inserted after the value of the target parameter. The section specified by the path parameter may be in a JSONB object. In this case, the value of the new_value parameter is inserted only if the value of the target parameter does not exist. As required by the path-oriented operators, a negative integer in the value of the path parameter indicates that elements are counted backward from the end of JSON arrays.

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

Returns the objects in the from_json parameter as indented JSON text.

select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')

[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

jsonb_agg

jsonb

Aggregates values, including null values, into a JSON array.

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

Aggregates key-value pairs into JSON objects. The value can be empty, but the key cannot be empty.

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

Validates a JSON string. The Boolean value true (t) is returned if the input string is a valid JSON string. Otherwise, the Boolean value false (f) is returned.

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