Hologres:JSON and JSONB

Commonly used operators

The following six operators work with both JSON and JSONB.

JSONB-only operators

The following operators apply only to JSONB data and support containment checks, key existence tests, concatenation, and deletion.

Processing functions

`jsonb_agg` and `jsonb_object_agg` example:

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;

Result:

 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` example:

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;

Result:

 id | json_strings        | is_valid_json
----+---------------------+--------------
  1 | {"a":2}             | true
  2 | {"a":{"b":{"c":1}}} | true
  3 | {"a": [1,2,"b"]}    | true
  4 | {{}}                | false
  5 | {1:"a"}             | false
  6 | [1,2,3]             | true

Parsing functions

Native PostgreSQL operators

Native PostgreSQL GIN indexes require a data recheck after retrieval, which may limit query performance improvements. The examples below demonstrate both operator classes.

Example: `jsonb_ops` index with key-existence query

-- 1. Create a table.
BEGIN;
DROP TABLE IF EXISTS json_table;
CREATE TABLE IF NOT EXISTS json_table
(
    id INT,
    j  jsonb
);
COMMIT;

-- 2. Create a GIN index using jsonb_ops.
CREATE INDEX index_json ON json_table USING GIN(j);

-- 3. Insert data.
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. Query rows where key1 exists.
SELECT * FROM json_table WHERE j ? 'key1';

Result:

 id |                        j
----+-------------------------------------------------
  1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
  1 | {"key1": 1}

Run EXPLAIN to confirm the index is used:

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

The Bitmap Index Scan step confirms the index is used.

Example: `jsonb_path_ops` index with containment query

-- 1. Create a table.
BEGIN;
DROP TABLE IF EXISTS json_table;
CREATE TABLE IF NOT EXISTS json_table
(
    id INT,
    j  jsonb
);
COMMIT;

-- 2. Create a GIN index using jsonb_path_ops.
CREATE INDEX index_json ON json_table USING GIN(j jsonb_path_ops);

-- 3. Insert 1,000,000 rows.
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. Query rows containing '{"key1": "10"}'.
SELECT * FROM json_table WHERE j @> '{"key1": "10"}'::JSONB;

Result:

 id |                                   j
----+------------------------------------------------------------------------
 10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
(1 row)

Run EXPLAIN to confirm the index is used:

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)

Hologres operators

Hologres provides two additional operator classes — jsonb_holo_ops and jsonb_holo_path_ops — that eliminate the data recheck step required by native PostgreSQL GIN indexes, improving query performance.

jsonb_holo_ops and jsonb_holo_path_ops support index lengths of 1 to 127 bytes. Index values longer than 127 bytes are truncated, and truncated fields require a data recheck. Run EXPLAIN ANALYZE to check whether a recheck occurs.

• jsonb_holo_ops: equivalent to jsonb_ops. Supports ?, ?|, ?&, and @> operators.

• jsonb_holo_path_ops: equivalent to jsonb_path_ops. Supports only the @> operator.

Example: `jsonb_holo_ops` index

-- 1. Create a table.
BEGIN;
DROP TABLE IF EXISTS json_table;
CREATE TABLE IF NOT EXISTS json_table
(
    id INT,
    j  jsonb
);
COMMIT;

-- 2. Create a GIN index using jsonb_holo_ops.
CREATE INDEX index_json ON json_table USING GIN(j jsonb_holo_ops);

-- 3. Insert data.
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. Query rows where key1 exists.
SELECT * FROM json_table WHERE j ? 'key1';

Result:

 id |                        j
----+-------------------------------------------------
  1 | {"key1": 1}
  1 | {"key1": 1, "key2": [1, 2], "key3": {"a": "b"}}
(2 rows)

Example: `jsonb_holo_path_ops` index

-- 1. Create a table.
BEGIN;
DROP TABLE IF EXISTS json_table;
CREATE TABLE IF NOT EXISTS json_table
(
    id INT,
    j  jsonb
);

-- 2. Create a GIN index using jsonb_holo_path_ops.
CREATE INDEX index_json ON json_table USING GIN(j jsonb_holo_path_ops);

-- 3. Insert 1,000,000 rows.
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. Query rows containing '{"key1": "10"}'.
SELECT * FROM json_table WHERE j @> '{"key1": "10"}'::JSONB;

Result:

 id |                                   j
----+------------------------------------------------------------------------
 10 | {"key1": "10", "key2": "10", "key3": "10", "key4": "10", "key5": "10"}
(1 row)