AnalyticDB:Operations on JSON & JSONB data types

Operators for JSON and JSONB

JSONB-only operators

JSON creation functions

JSON processing functions

Store and query JSON data

-- Create a table with JSON and array columns
CREATE TABLE tj (id serial, ary int[], obj json, num integer);

-- Insert rows
INSERT INTO tj (ary, obj, num) VALUES ('{1,5}'::int[], '{"obj":1}', 5);
INSERT INTO tj (ary, obj, num) VALUES ('{2,5}'::int[], '{"obj":2}', 5);

-- Convert rows to JSON objects
SELECT row_to_json(q) FROM (SELECT id, ary, obj, num FROM tj) AS q;
--                  row_to_json
-- -------------------------------------------
--  {"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
--  {"f1":2,"f2":[2,5],"f3":{"obj":2},"f4":5}

Join tables on a JSON field

Use ->> to extract a JSON field as text for join conditions:

CREATE TABLE tj2 (id serial, ary int[], obj json, num integer);
INSERT INTO tj2 (ary, obj, num) VALUES ('{2,5}'::int[], '{"obj":2}', 5);

-- Join using the ->> operator
SELECT * FROM tj, tj2 WHERE tj.obj->>'obj' = tj2.obj->>'obj';

-- Equivalent join using json_object_field_text()
SELECT * FROM tj, tj2
  WHERE json_object_field_text(tj.obj, 'obj') = json_object_field_text(tj2.obj, 'obj');

Create a function index on a JSON column

Function indexes let you index the result of a JSON path expression, enabling efficient equality lookups on nested fields:

CREATE TEMP TABLE test_json (json_type text, obj json);
INSERT INTO test_json VALUES ('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}');
INSERT INTO test_json VALUES ('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');

-- Create an index on the extracted path value
CREATE INDEX i ON test_json (json_extract_path_text(obj, '{f4}'));

-- The planner can use the index for equality queries
SELECT * FROM test_json WHERE json_extract_path_text(obj, '{f4}') = '{"f5":99,"f6":"foo"}';
--  json_type |                 obj
-- -----------+-------------------------------------------
--  aa        | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}

Compare GIN index performance on JSONB columns

The following example uses 10 million rows to show the impact of GIN indexes on containment queries.

-- Create test tables
CREATE TABLE jtest1 (id int, jdoc json);
CREATE TABLE jtest2 (id int, jdoc jsonb);
CREATE TABLE jtest3 (id int, jdoc jsonb);

-- Generate a random string helper function
CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
    ARRAY (
        SELECT substring(
            '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
            FROM (ceil(random()*62))::int FOR 1
        )
        FROM generate_series(1, $1)
    ),
    ''
)
$BODY$
LANGUAGE sql VOLATILE;

-- Populate jtest1 with 10 million rows
INSERT INTO jtest1
SELECT t.seq,
       ('{"a":{"a1":"a1a1","a2":"a2a2"},"name":"' || random_string(10) || '","b":"bbbbb"}')::json
FROM generate_series(1, 10000000) AS t(seq);

-- Copy to JSONB tables
INSERT INTO jtest2 SELECT id, jdoc::jsonb FROM jtest1;
INSERT INTO jtest3 SELECT id, jdoc::jsonb FROM jtest1;

-- Create GIN indexes
CREATE INDEX idx_jtest2 ON jtest2 USING gin(jdoc);                        -- jsonb_ops
CREATE INDEX idx_jtest3 ON jtest3 USING gin(jdoc jsonb_path_ops);         -- jsonb_path_ops

Query performance comparison:

-- Sequential scan on the unindexed JSON table: ~1,778 ms
EXPLAIN ANALYZE SELECT * FROM jtest1 WHERE jdoc @> '{"name":"N9WP5txmVu"}';

-- GIN index with jsonb_ops: ~1.3 ms
EXPLAIN ANALYZE SELECT * FROM jtest2 WHERE jdoc @> '{"name":"N9WP5txmVu"}';

-- GIN index with jsonb_path_ops: ~1.3 ms
EXPLAIN ANALYZE SELECT * FROM jtest3 WHERE jdoc @> '{"name":"N9WP5txmVu"}';

Both GIN index types reduce query time from ~1,778 ms to ~1.3 ms on 10 million rows. Choose jsonb_path_ops when queries use only @>; use jsonb_ops when you also need key-existence operators (?, ?\|, ?&).

Query JSONB data from Python

Use psycopg2 to connect and run JSON queries:

#!/usr/bin/env python
import json
import psycopg2

def run_query(sql):
    conn = psycopg2.connect("dbname=<your-database>")
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute(sql)
    return cur.fetchall()

rows = run_query("SELECT obj FROM tj;")
for row in rows:
    print(json.loads(row[0]))

Replace <your-database> with your database name.