</>
Skip to content
PostgreSQL lessons (31/38)

PostgreSQL — JSON

JSON Data Type

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

INSERT INTO products (name, attributes) 
VALUES ('Phone', '{"color": "black", "storage": 128}');

Query JSON

-- Get value
SELECT attributes->>'color' FROM products;

-- Filter
SELECT * FROM products WHERE attributes->>'color' = 'black';

-- Contains
SELECT * FROM products WHERE attributes @> '{"storage": 128}';

JSON Functions

FunctionDescription
->Get JSON object
->>Get text value
@>Contains
<@Is contained by
jsonb_object_keysGet keys
jsonb_array_elementsUnnest array

Index JSON

CREATE INDEX idx_attributes ON products USING GIN (attributes);

Update JSON

UPDATE products 
SET attributes = attributes || '{"weight": 150}' 
WHERE id = 1;

Mini Practice

  1. Store JSON data
  2. Query JSON values
  3. Filter by JSON
  4. Index JSON columns

Up Next

Continue with Arrays — array support.

Related Topics

TransactionsArraysFull Text SearchExtensions

Frequently Asked Questions about JSON

What is JSON in PostgreSQL?

JSON is a fundamental concept in PostgreSQL. This lesson explains it step by step with clear examples, making it easy for beginners to understand.

How do I learn JSON?

Start by reading the explanation above, then try the code examples. Practice by modifying the examples and experimenting with different values. Hands-on practice is the best way to learn JSON.

Why is JSON important in PostgreSQL?

JSON is essential for PostgreSQL development. Understanding this concept will help you write better code and solve real-world problems more effectively.