KAHIBARO
Discord Login Register ☰
☰

11.10 JSON Data

Table of Contents

Why JSON Matters in PostgreSQL

Backend APIs frequently send and receive JSON. PostgreSQL has first‑class support for JSON, which lets you:

PostgreSQL actually has two JSON related types: json and jsonb. Understanding them and the related functions is key to using JSON effectively.

JSON vs JSONB

PostgreSQL supports:

They look similar at first, but behave differently internally and in queries.

Core differences

Featurejsonjsonb
Storage formatTextBinary, parsed
Validation on insertYes, must be valid JSONYes, must be valid JSON
Preserves whitespaceYesNo
Preserves key orderYesNo
Duplicate keysPreserved as‑isLast key wins
Indexing supportLimitedRich support (GIN, etc.)
Query performance (operators)Slower, re‑parses textFaster, pre‑parsed
Recommended for new applicationsRarelyAlmost always

Rule: For backend applications, use jsonb by default unless you have a very specific reason to preserve original JSON formatting exactly.

Storing JSON Data

You can define columns of type json or jsonb in your tables.

Creating a table with JSONB

sql 
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    attributes  JSONB,          -- flexible product attributes
    metadata    JSONB           -- extra info, logs, tags, etc.
);

Inserting data:

sql 
INSERT INTO products (name, attributes, metadata)
VALUES (
    'T‑Shirt',
    '{ "color": "blue", "size": "M", "in_stock": true }',
    '{
        "tags": ["clothing", "summer"],
        "created_by": "admin",
        "dimensions": { "width": 40, "height": 60 }
     }'
);

PostgreSQL validates JSON on insert. If you insert invalid JSON, you get an error:

sql 
INSERT INTO products (name, attributes)
VALUES ('Broken', '{color: blue}');  -- invalid JSON, keys must be quoted

This will fail because JSON requires double quotes around keys and string values.

Using parameterized queries in code

In a Python backend using psycopg2 or asyncpg, you pass JSON as native structures:

python 
cur.execute(
    """
    INSERT INTO products (name, attributes)
    VALUES (%s, %s)
    RETURNING id
    """,
    (
        "Sneakers",
        {
            "color": "white",
            "size": 42,
            "in_stock": True,
        },
    )
)

The driver converts the Python dict into JSONB automatically if the column is jsonb.

Basic JSONB Operations

PostgreSQL provides special operators for JSON/JSONB. Many are used constantly in backend queries.

Access operators: `->`, `->>`, `#>`, `#>>`

OperatorReturnsUsage example
->JSON / JSONBAccess field as JSON
->>textAccess field as text
#>JSON / JSONBNested path access as JSON
#>>textNested path access as text

Assume:

sql 
SELECT attributes
FROM products
WHERE name = 'T‑Shirt';

Returns:

json 
{ "color": "blue", "size": "M", "in_stock": true }
Example 1: Get a top level key
sql 
SELECT
    attributes->'color'      AS color_json,   -- JSON
    attributes->>'color'     AS color_text    -- text
FROM products;

Result:

color_jsoncolor_text
"blue"blue
Example 2: Nested keys

Given:

sql 
SELECT metadata FROM products WHERE name = 'T‑Shirt';
json 
{
  "tags": ["clothing", "summer"],
  "created_by": "admin",
  "dimensions": { "width": 40, "height": 60 }
}

Use path operators:

sql 
SELECT
    metadata->'dimensions'->'width'         AS width_json,
    metadata->'dimensions'->>'width'        AS width_text,
    metadata#>'{dimensions,width}'          AS width_json2,
    metadata#>>'{dimensions,width}'         AS width_text2
FROM products;

'{dimensions,width}' represents the path ["dimensions", "width"].

Querying JSONB: Containment and Existence

These operators are extremely useful for filtering records by JSON values.

Containment: `@>`

jsonb @> jsonb is true if the left JSON contains the right JSON.

For example, products whose attributes include "color": "blue":

sql 
SELECT name, attributes
FROM products
WHERE attributes @> '{"color": "blue"}';

This works even if there are additional keys.

Containment for arrays:

sql 
SELECT name, metadata
FROM products
WHERE metadata->'tags' @> '["summer"]';

This returns rows where tags array contains "summer".

Existence: `?`, `?|`, `?&`

OperatorDescription
?Does key (or string in array) exist?
`?`Does any of these keys/strings exist?
?&Do all of these keys/strings exist?

Assume attributes like:

json 
{ "color": "blue", "size": "M", "in_stock": true }

Check if key exists:

sql 
SELECT name
FROM products
WHERE attributes ? 'color';

Check if any of several keys exist:

sql 
SELECT name
FROM products
WHERE attributes ?| array['size', 'weight'];

Check if all keys exist:

sql 
SELECT name
FROM products
WHERE attributes ?& array['color', 'size'];

These also work on JSON arrays of strings.

Updating JSONB

You often need to update only part of a JSON document without replacing the whole value.

Concatenation and merge: `||`

jsonb || jsonb merges objects. Right side overwrites conflicts.

sql 
UPDATE products
SET attributes = attributes || '{"color": "red"}'
WHERE name = 'T‑Shirt';

If attributes was:

json 
{ "color": "blue", "size": "M", "in_stock": true }

After update:

json 
{ "color": "red", "size": "M", "in_stock": true }

You can also add new keys:

sql 
UPDATE products
SET attributes = attributes || '{"material": "cotton"}'
WHERE name = 'T‑Shirt';

Result:

json 
{ "color": "red", "size": "M", "in_stock": true, "material": "cotton" }

Remove keys: `-` and `#-`

OperatorDescription
-Remove top level key or array element
#-Remove nested key or element by path

Remove a top level key:

sql 
UPDATE products
SET attributes = attributes - 'size'
WHERE name = 'T‑Shirt';

Remove multiple keys:

sql 
UPDATE products
SET attributes = attributes - array['size', 'in_stock']
WHERE name = 'T‑Shirt';

Remove a nested key:

sql 
UPDATE products
SET metadata = metadata #- '{dimensions,width}'
WHERE name = 'T‑Shirt';

If dimensions was:

json 
{ "width": 40, "height": 60 }

after this, it becomes:

json 
{ "height": 60 }

JSONB Functions You Will Use Often

PostgreSQL has many JSON functions. Here are some that are very helpful in backend work.

`jsonb_build_object` and `jsonb_build_array`

These build JSONB from SQL values.

sql 
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'attributes', attributes
) AS product_json
FROM products;

Example result:

json 
{
  "id": 1,
  "name": "T‑Shirt",
  "attributes": { "color": "red", "material": "cotton" }
}

You can send this directly to an API client.

Build an array:

sql 
SELECT jsonb_build_array(1, 'two', true, jsonb_build_object('a', 1));

Result:

json 
[1, "two", true, { "a": 1 }]

`to_jsonb`

Convert SQL values and rows to JSONB.

Simple value:

sql 
SELECT to_jsonb(42);       -- 42
SELECT to_jsonb('hello');  -- "hello"

Whole row:

sql 
SELECT to_jsonb(products)
FROM products
WHERE id = 1;

This converts the entire row (all columns) to a JSONB object.

Aggregating to JSON: `json_agg`, `jsonb_agg`

For building JSON arrays of rows:

sql 
SELECT jsonb_agg(to_jsonb(p))
FROM products AS p;

Result:

json 
[
  { "id": 1, "name": "T‑Shirt", "attributes": { ... } },
  { "id": 2, "name": "Sneakers", "attributes": { ... } }
]

This is perfect when your backend needs to send a JSON array of objects.

Another common REST API pattern:

sql 
SELECT jsonb_build_object(
    'total', COUNT(*),
    'items', jsonb_agg(to_jsonb(p))
)
FROM products AS p;

Result:

json 
{
  "total": 2,
  "items": [
    { "id": 1, "name": "T‑Shirt", ... },
    { "id": 2, "name": "Sneakers", ... }
  ]
}

Indexing JSONB for Performance

JSONB queries using @>, ?, etc can be slow without indexes.

GIN indexes

For JSONB you typically use GIN indexes.

Create a GIN index on a JSONB column:

sql 
CREATE INDEX products_attributes_gin_idx
ON products
USING GIN (attributes);

This helps for:

sql 
SELECT *
FROM products
WHERE attributes @> '{"color": "blue"}';

or

sql 
SELECT *
FROM products
WHERE attributes ? 'color';

If you often query specific paths, you can index expressions.

For example, filter by attributes->>'size':

sql 
CREATE INDEX products_attributes_size_idx
ON products ((attributes->>'size'));

Now this query can use an index:

sql 
SELECT *
FROM products
WHERE attributes->>'size' = 'M';

Rule: If you query JSONB columns frequently, add appropriate GIN or expression indexes. Without them, JSON filters can become very slow on large tables.

When to Use JSONB vs Normal Columns

JSONB is powerful, but not always the right tool. Think of it as a complement to relational tables, not a full replacement.

Good use cases

Poor use cases

A common hybrid approach:

sql 
CREATE TABLE orders (
    id            SERIAL PRIMARY KEY,
    user_id       INTEGER NOT NULL REFERENCES users(id),
    status        TEXT NOT NULL,
    total_amount  NUMERIC(10,2) NOT NULL,
    details       JSONB                      -- line items, extra info
);

Core fields are normal columns, the flexible remainder lives in details.

Practical Examples for Backend Work

Example 1: Filtering by JSON attribute

You have an API endpoint: 

GET /products?color=blue&size=M

SQL using JSONB:

sql 
SELECT *
FROM products
WHERE attributes @> format('{"color": "%s", "size": "%s"}', 'blue', 'M')::jsonb;

In real backend code, use parameterized queries instead of string formatting.

Example 2: Sorting by JSON field

Sort products by nested dimensions.height:

sql 
SELECT name, metadata->'dimensions'->>'height' AS height
FROM products
ORDER BY (metadata->'dimensions'->>'height')::INTEGER;

Note the cast to INTEGER to sort numerically instead of lexicographically.

Example 3: Partial update from API

You receive a PATCH request:

json 
{
  "attributes": {
    "color": "green",
    "in_stock": false
  }
}

You want to merge this into existing attributes:

sql 
UPDATE products
SET attributes = attributes || '{"color": "green", "in_stock": false}'
WHERE id = 1;

In application code, you would build the JSON dynamically from the request body.

Summary

With these tools, you can design PostgreSQL schemas that work naturally with JSON based web APIs while still keeping good performance and structure.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3177 / 24h
Impressum / Imprint
Datenschutz / Data Policy