Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_data (
    id serial PRIMARY KEY,
    name text,
    age int,
    metadata json
);

-- Insert 100,000 rows of fake data
INSERT INTO test_data (name, age, metadata)
SELECT 
    'User_' || g,
    (random() * 100)::int,
    json_build_object('source', 'system', 'active', g % 2 = 0)
FROM generate_series(1, 10000) g;

select * from test_data limit 2;

-- Table to store aggregated json
CREATE TABLE json_output (
    id serial,
    payload json
);

-- Table to store aggregated jsonb
CREATE TABLE jsonb_output (
    id serial,
    payload jsonb
);

CREATE TABLE

INSERT 0 10000

id
name
age
metadata

1
User_1
38
{"source" : "system", "active" : false}

2
User_2
48
{"source" : "system", "active" : true}

SELECT 2

CREATE TABLE

CREATE TABLE

EXPLAIN ANALYZE
INSERT INTO json_output (payload)
SELECT json_agg(t)
FROM (
    SELECT * FROM test_data LIMIT 10000
) t;

QUERY PLAN

Insert on json_output  (cost=321.77..321.78 rows=0 width=0) (actual time=30.988..30.990 rows=0 loops=1)

  ->  Subquery Scan on "*SELECT*"  (cost=321.77..321.78 rows=1 width=36) (actual time=18.281..18.284 rows=1 loops=1)

        ->  Aggregate  (cost=321.77..321.78 rows=1 width=32) (actual time=18.221..18.223 rows=1 loops=1)

              ->  Subquery Scan on t  (cost=0.00..298.68 rows=9234 width=96) (actual time=0.035..5.044 rows=10000 loops=1)

                    ->  Limit  (cost=0.00..206.34 rows=9234 width=72) (actual time=0.012..2.118 rows=10000 loops=1)

                          ->  Seq Scan on test_data  (cost=0.00..206.34 rows=9234 width=72) (actual time=0.011..1.269 rows=10000 loops=1)

Planning Time: 0.173 ms

Execution Time: 31.162 ms

EXPLAIN

-- Serialize using jsonb and insert
EXPLAIN ANALYZE
INSERT INTO jsonb_output (payload)
SELECT jsonb_agg(t)
FROM (
    SELECT * FROM test_data LIMIT 10000
) t;

QUERY PLAN

Insert on jsonb_output  (cost=321.77..321.78 rows=0 width=0) (actual time=85.766..85.768 rows=0 loops=1)

  ->  Subquery Scan on "*SELECT*"  (cost=321.77..321.78 rows=1 width=36) (actual time=68.514..68.517 rows=1 loops=1)

        ->  Aggregate  (cost=321.77..321.78 rows=1 width=32) (actual time=68.428..68.429 rows=1 loops=1)

              ->  Subquery Scan on t  (cost=0.00..298.68 rows=9234 width=96) (actual time=0.016..7.966 rows=10000 loops=1)

                    ->  Limit  (cost=0.00..206.34 rows=9234 width=72) (actual time=0.011..3.051 rows=10000 loops=1)

                          ->  Seq Scan on test_data  (cost=0.00..206.34 rows=9234 width=72) (actual time=0.010..1.996 rows=10000 loops=1)

Planning Time: 0.096 ms

Execution Time: 86.298 ms

EXPLAIN

-- Storage size in bytes for json
SELECT pg_column_size(payload)/1024 AS json_size FROM json_output;

-- Storage size in bytes for jsonb
SELECT pg_column_size(payload)/1024 AS jsonb_size FROM jsonb_output;

json_size

102

SELECT 1

jsonb_size

91

SELECT 1

CREATE TABLE flat_json AS
SELECT id, row_to_json(t) AS payload
FROM test_data t;

-- Flattened JSONB version
CREATE TABLE flat_jsonb AS
SELECT id, to_jsonb(t) AS payload
FROM test_data t;

EXPLAIN ANALYZE
SELECT payload FROM flat_json WHERE id = 50000;

EXPLAIN ANALYZE
SELECT payload FROM flat_jsonb WHERE id = 50000;

SELECT 10000

SELECT 10000

QUERY PLAN

Seq Scan on flat_json  (cost=0.00..496.80 rows=122 width=32) (actual time=0.778..0.778 rows=0 loops=1)

  Filter: (id = 50000)

  Rows Removed by Filter: 10000

Planning Time: 0.840 ms

Execution Time: 0.790 ms

EXPLAIN

QUERY PLAN

Seq Scan on flat_jsonb  (cost=0.00..662.40 rows=163 width=32) (actual time=0.858..0.858 rows=0 loops=1)

  Filter: (id = 50000)

  Rows Removed by Filter: 10000

Planning Time: 0.034 ms

Execution Time: 0.865 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT payload->'metadata'->>'source'
FROM flat_json
WHERE (payload->'metadata'->>'active') = 'true';

EXPLAIN ANALYZE
SELECT payload->'metadata'->>'source'
FROM flat_jsonb
WHERE (payload->'metadata'->>'active') = 'true';

QUERY PLAN

Seq Scan on flat_json  (cost=0.00..619.33 rows=122 width=32) (actual time=0.111..25.338 rows=5000 loops=1)

  Filter: (((payload -> 'metadata'::text) ->> 'active'::text) = 'true'::text)

  Rows Removed by Filter: 5000

Planning Time: 0.138 ms

Execution Time: 25.519 ms

EXPLAIN

QUERY PLAN

Seq Scan on flat_jsonb  (cost=0.00..825.78 rows=163 width=32) (actual time=0.024..3.804 rows=5000 loops=1)

  Filter: (((payload -> 'metadata'::text) ->> 'active'::text) = 'true'::text)

  Rows Removed by Filter: 5000

Planning Time: 0.064 ms

Execution Time: 3.978 ms

EXPLAIN

-- Create index on a nested field
CREATE INDEX idx_jsonb_active
ON flat_jsonb ((payload->'metadata'->>'active'));

EXPLAIN ANALYZE
SELECT payload->'metadata'->>'source'
FROM flat_jsonb
WHERE (payload->'metadata'->>'active') = 'true';

CREATE INDEX

QUERY PLAN

Bitmap Heap Scan on flat_jsonb  (cost=4.67..131.30 rows=50 width=32) (actual time=0.225..1.755 rows=5000 loops=1)

  Recheck Cond: (((payload -> 'metadata'::text) ->> 'active'::text) = 'true'::text)

  Heap Blocks: exact=193

  ->  Bitmap Index Scan on idx_jsonb_active  (cost=0.00..4.66 rows=50 width=0) (actual time=0.193..0.193 rows=5000 loops=1)

        Index Cond: (((payload -> 'metadata'::text) ->> 'active'::text) = 'true'::text)

Planning Time: 0.494 ms

Execution Time: 1.955 ms

EXPLAIN
id	name	age	metadata
1	User_1	38	{"source" : "system", "active" : false}
2	User_2	48	{"source" : "system", "active" : true}