PostgreSQL and JSON in 2025

We’ve seen MySQL vs MariaDB fight to the death over JSON. We’ve seen Microsoft SQL Server roll up in a spaceship and embarrass everyone. And then there’s PostgreSQL… just sitting there since 2012, sipping tea, with the most mature, powerful, and actually pleasant JSON implementation of them all.

A short history (so you understand why it’s so good)

• 2006 – hstore (key/value text type)
• 2012 – PostgreSQL 9.2 → real json type appears
• 2014 – PostgreSQL 9.4 → jsonb (binary JSON) + GIN indexes → game over for everyone else
• 2016+ – constant improvements: jsonpath, JSON_TABLE, incremental upgrades every 6 months
• 2025 – PostgreSQL 17/18 → still the undisputed JSON champion among relational databases

What PostgreSQL actually gives you in 2025

Two types – pick whatever you want

• json – stores JSON exactly as text (good for exact round-tripping)
• jsonb – binary format, supports indexing, ordering, nesting, containment operators

Operators that make you cry with joy

-- containment
WHERE metadata @> '{"type": "premium"}'
WHERE metadata <@ '{"country": "PL"}'

-- key existence
WHERE metadata ? 'tags'
WHERE metadata ?| '{admin, moderator}'
WHERE metadata ?& '{admin, editor}'

-- path extraction
WHERE metadata -> 'profile' ->> 'name' = 'John'
WHERE metadata #>> '{address, city}' = 'Warsaw'

-- containment
WHERE metadata @> '{"type": "premium"}'
WHERE metadata <@ '{"country": "PL"}'

-- key existence
WHERE metadata ? 'tags'
WHERE metadata ?| '{admin, moderator}'
WHERE metadata ?& '{admin, editor}'

-- path extraction
WHERE metadata -> 'profile' ->> 'name' = 'John'
WHERE metadata #>> '{address, city}' = 'Warsaw'

jsonpath – the SQL/JSON standard path language (yes, the real one)

SELECT jsonb_path_query(data, '$.products[?(@.price > 1000)].name');
SELECT jsonb_path_exists(data, '$[*] ? (@.age > 30)');

SELECT jsonb_path_query(data, '$.products[?(@.price > 1000)].name');
SELECT jsonb_path_exists(data, '$[*] ? (@.age > 30)');

GIN indexes that actually scale

CREATE INDEX ON orders USING GIN (payload);                     -- whole document
CREATE INDEX ON orders USING GIN (payload jsonb_path_ops);     -- for @> operator
CREATE INDEX ON users ((payload -> 'tags'));                    -- specific array

CREATE INDEX ON orders USING GIN (payload);                     -- whole document
CREATE INDEX ON orders USING GIN (payload jsonb_path_ops);     -- for @> operator
CREATE INDEX ON users ((payload -> 'tags'));                    -- specific array

These GIN indexes support:

• @> containment (perfect for tenant/customer filtering)
• ? key existence
• nested arrays and objects
• millions of documents without exploding

JSON generation that doesn’t suck

SELECT jsonb_build_object(
    'id',      id,
    'name',    first_name || ' ' || last_name,
    'roles',   jsonb_agg_roles,
    'active',  is_active
) FROM users;

-- or just:
SELECT to_jsonb(u.*) FROM users u;

SELECT jsonb_build_object(
    'id',      id,
    'name',    first_name || ' ' || last_name,
    'roles',   jsonb_agg_roles,
    'active',  is_active
) FROM users;

-- or just:
SELECT to_jsonb(u.*) FROM users u;

JSON_TABLE – turn any JSON into real relational rows (SQL standard)

SELECT * FROM JSON_TABLE(@json, '$.orders[*]'
    COLUMNS (
        order_id   BIGINT     PATH '$.id',
        total      DECIMAL    PATH '$.total',
        items      JSON       PATH '$$ .items' NESTED PATH ' $$[*]'
            COLUMNS (product VARCHAR PATH '$.name')
    )
);

SELECT * FROM JSON_TABLE(@json, '$.orders[*]'
    COLUMNS (
        order_id   BIGINT     PATH '$.id',
        total      DECIMAL    PATH '$.total',
        items      JSON       PATH '$$ .items' NESTED PATH ' $$[*]'
            COLUMNS (product VARCHAR PATH '$.name')
    )
);

Head-to-head comparison (2025 reality)

The verdict nobody wants to hear

If you want the absolute best JSON experience inside a relational database in 2025, the answer is not SQL Server. It’s not MySQL. It’s not MariaDB.

It’s PostgreSQL – and it’s been the answer since about 2015.

SQL Server is extremely close (and sometimes wins on raw speed in simple cases), but PostgreSQL wins on:

• flexibility of operators
• real standard compliance (jsonpath, JSON_TABLE)
• indexing power (GIN on nested arrays/objects)
• zero licensing drama
• continuous 6-month release cycle with real improvements

Final knockout line

MySQL and MariaDB are still arguing who has the better TEXT column with JSON inside. SQL Server built a spaceship. PostgreSQL quietly invented JSON for relational databases back when most people were still using XML.

And in 2025? PostgreSQL is still the king. Free, boringly reliable, insanely powerful

The real winner was open source the whole time. You just had to look at the elephant in the room.