# 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:**

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

**Insert data:**

You simply pass a valid JSON string into the query.

```sql
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.

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

**Example:** Digging into nested objects (Laptop specs).

```sql
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:**

```sql
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":**

```sql
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.

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

**Removing a Key (`-`)**

```sql
-- 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:**

```sql
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'`        |

***