Advanced SQL - Guide to JSON

Storing semi-structured JSON data directly in a relational database like PostgreSQL offers incredible flexibility. The native JSONB data type is key, as it stores JSON in an optimized, binary format that's highly efficient to index and query. This guide covers the complete CRUD (Create, Read, Update, Delete) lifecycle for managing JSONB data.

---

1. Setup: The Sample Table

All examples will use the following products table. The details column will store various product attributes as a JSONB object.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSONB
);

---

2. Create (INSERT)

To create a record, you simply insert a valid JSON string. The database validates the format and stores it in the efficient JSONB type.

Example: Insert a New Product

INSERT INTO products (name, details)
VALUES (
    'Laptop',
    '{
        "brand": "TechCorp",
        "specs": {
            "cpu": "i7",
            "ram": 16,
            "storage": 512
        },
        "tags": ["powerful", "portable"],
        "in_stock": true
    }'
);

INSERT INTO products (name, details)
VALUES (
    'Keyboard',
    '{
        "brand": "ClickyKeys",
        "specs": { "type": "mechanical" },
        "tags": ["ergonomic", "gaming"],
        "in_stock": false
    }'
);

---

3. Read (SELECT)

Specialized operators are used to navigate and extract data directly within your queries.

Key Query Operators

Query Examples

A. Retrieve a Top-Level Field as Text

Use the ->> operator to get the brand.

SELECT name, details ->> 'brand' AS brand
FROM products
WHERE name = 'Laptop';

Result:

B. Retrieve a Nested Field as Text

You can chain operators or use the path operator #>>. The path '{specs, ram}' means "go into specs, then get ram".

-- Chaining method
SELECT name, details -> 'specs' ->> 'ram' AS ram
FROM products
WHERE name = 'Laptop';

-- Path method (more concise)
SELECT name, details #>> '{specs, ram}' AS ram
FROM products
WHERE name = 'Laptop';

Result (for both queries):

C. Retrieve a Nested Object

Use the path operator #> to extract an entire object or array.

SELECT name, details #> '{specs}' AS specifications
FROM products
WHERE name = 'Laptop';

Result:

D. Filter Based on a JSON Value

The WHERE clause can query JSON values. Remember to cast the text output of ->> to the correct type (e.g., ::integer) for numeric or boolean comparisons.

SELECT name, details ->> 'brand' AS brand
FROM products
WHERE (details -> 'specs' ->> 'ram')::integer > 8;

Result:

E. Querying an Array

Use the "contains" operator (@>) to check if an array includes a specific value.

SELECT name, details ->> 'brand' AS brand
FROM products
WHERE details -> 'tags' @> '["gaming"]';

Result:

---

4. Update (UPDATE)

You can modify parts of a JSONB document without rewriting the entire thing.

A. Add or Update a Property with JSONB_SET()

The JSONB_SET(target, path, new_value, create_if_missing) function is the primary tool for modifications.

• Add a top-level key (warranty_years):

    UPDATE products
    SET details = JSONB_SET(details, '{warranty_years}', '2'::jsonb, true)
    WHERE name = 'Laptop';
  

• Add a nested key (gpu inside specs):

    UPDATE products
    SET details = JSONB_SET(details, '{specs, gpu}', '"RTX 4060"'::jsonb, true)
    WHERE name = 'Laptop';
  

Laptop JSON After Updates:

{
    "brand": "TechCorp",
    "specs": {
        "cpu": "i7",
        "ram": 16,
        "storage": 512,
        "gpu": "RTX 4060"
    },
    "tags": ["powerful", "portable"],
    "in_stock": true,
    "warranty_years": 2
}

B. Add a Top-Level Key with the || Operator

The concatenation operator (||) is a simpler way to add or overwrite top-level keys.

UPDATE products
SET details = details || '{"shipping_weight_kg": 2.5}'::jsonb
WHERE name = 'Laptop';

C. Delete a Key-Value Pair

Use the - operator for top-level keys and the #- operator for nested keys.

• Remove the top-level in_stock key:

    UPDATE products
    SET details = details - 'in_stock'
    WHERE name = 'Laptop';
  

• Remove the nested cpu key from specs:

    UPDATE products
    SET details = details #- '{specs, cpu}'
    WHERE name = 'Laptop';
  

---

5. Delete (DELETE)

This is a standard SQL DELETE operation, where the filter condition can be based on a JSON value.

Example: Delete a Product Row

DELETE FROM products
WHERE name = 'Keyboard';

Result: The row for the 'Keyboard' product is permanently removed from the table.

---

6. Performance: Indexing JSONB

To make queries on JSONB columns fast, especially on large tables, you must use a Generalized Inverted Index (GIN).

General-Purpose GIN Index

This index speeds up most queries, including key lookups and the "contains" (@>) operator.

CREATE INDEX idx_products_details ON products USING GIN (details);

With this index, filtering by tags or any other key inside the JSON becomes dramatically faster.