Working with JSON data

---

PostgreSQL is powerful because it allows you to combine the reliability of a relational database with the flexibility of a NoSQL document store.

Here is a guide on how to store, query, and optimize JSON data in Postgres.

---

JSON vs. JSONB

Postgres offers two data types for storing JSON. It is critical to choose the right one.

Feature	json	jsonb (Binary JSON)
Storage Format	Text (exact copy of input)	Binary (decomposed)
Speed	Faster to insert, slower to query	Slower to insert, much faster to query
Indexing	No	Yes (GIN indexes)
Formatting	Preserves whitespace/key order	Removes whitespace, reorders keys

Best Practice: almost always use jsonb. It allows for indexing and high-performance querying. Use json only if you need to preserve exact logs or whitespace.

---

Creating and Inserting Data

Let's imagine a flexible e-commerce scenario where products have different attributes (e.g., shirts have sizes, laptops have CPU specs).

Create the table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

Insert data:

You simply pass a valid JSON string into the query.

INSERT INTO products (name, attributes)
VALUES 
    ('T-Shirt', '{"color": "blue", "size": "L", "tags": ["sale", "summer"]}'),
    ('Laptop', '{"brand": "Dell", "specs": {"ram": "16GB", "storage": "512GB"}}'),
    ('Phone', '{"brand": "Apple", "color": "black", "tags": ["electronics"]}');

---

Querying JSON Data

Postgres uses specific operators to "burrow" into the JSON structure.

A. The Access Operators (-> vs ->>)

• -> returns the value as JSON object/array (useful if you want to keep digging deeper).

• ->> returns the value as Text (useful for the final result or comparison).

Example: Get the color of all products.

SELECT 
    name, 
    attributes ->> 'color' as color 
FROM products;

Example: Digging into nested objects (Laptop specs).

SELECT 
    name, 
    attributes -> 'specs' ->> 'ram' as ram 
FROM products 
WHERE name = 'Laptop';

B. Filtering with the "Contains" Operator (@>)

This is the standard way to filter jsonb data efficiently. It checks if the left JSON value contains the right JSON path/value.

Find all products that are Blue:

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

C. Handling Arrays

You can check if a specific string exists within a JSON array using the ? operator.

Find products tagged with "sale":

SELECT * FROM products 
WHERE attributes -> 'tags' ? 'sale';

---

Updating JSON Data

Modifying JSON inside a column can be tricky because you usually have to replace the whole value or use specific functions.

Adding or Replacing a Key (|| or jsonb_set)

The || operator concatenates JSON data. If the key exists, it updates it; if not, it adds it.

-- Update the color to Red for the T-Shirt
UPDATE products 
SET attributes = attributes || '{"color": "red"}' 
WHERE name = 'T-Shirt';

Removing a Key (-)

-- Remove the 'brand' key from the Phone entry
UPDATE products 
SET attributes = attributes - 'brand' 
WHERE name = 'Phone';

---

Performance: Indexing

If you have millions of rows, querying JSONB without an index is slow because Postgres performs a full table scan. You should use a GIN (Generalized Inverted Index).

Create a GIN index on the whole column:

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

This makes queries using @>, ?, ?&, and ?| extremely fast.

---

Summary of Common Operators

Operator	Description	Example
->	Get JSON object/array by key or index	data -> 'key'
->>	Get value as Text	data ->> 'key'
@>	Does left JSON contain right JSON?	data @> '{"a":1}'
?	Does key/element exist?	data ? 'my_key'
`		`
-	Delete key/element	data - 'a'

---