Learn SQL with PostgreSQL - Advanced Data Types: JSONB & Array Support

#Introduction

Welcome to episode 8 of the Learn SQL with PostgreSQL series! So far we've been working with very structured data: rigid columns, types, and constraints. But the real world isn't always tidy. Products can have different attributes (shoes have "size", laptops have "ram"), or you need to store a list of values in one column (tags on an article). For needs like these, PostgreSQL has a secret weapon: the JSONB and Array data types.

In this episode, we'll cover the difference between JSON and JSONB, JSON query operators like ->, ->>, @>, and ?, manipulation functions like jsonb_set and jsonb_array_elements, then move to the Array data type with the operators ANY, @>, and UNNEST.

#PostgreSQL as a Hybrid Database

Most databases force you to choose: relational (strict structure, joins, transactions) or document store (flexible, schema-less). PostgreSQL answers "why not both?" by supporting JSONB natively: JSON data can be stored, queried, indexed, and even combined with SQL relationships in a single query.

When is JSONB a good fit?

• Product attributes that are variable and change often (laptop specs vs shoe specs are very different).
• Data from external APIs whose format is beyond our control.
• Event payloads or logs with free-form structure.
• Data that is rarely queried, so it doesn't need to be its own column.

When should you not use JSONB? When the data is always queried, must be joined, or needs strict constraints. A practical rule of thumb: if the data is a "column", make it a column. If it's a "document", use JSONB.

#JSON vs JSONB

PostgreSQL has two JSON types: JSON and JSONB.

Modern best practice: always use JSONB unless there's a rare need to preserve the raw text structure.

#Manipulating & Querying JSONB Data

#Basic JSONB Operators

Three operators you must memorize from the start:

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'
);
 
INSERT INTO products (name, attributes) VALUES
('Laptop Pro', '{"ram": "16GB", "warna": "silver"}'),
('Sepatu Lari', '{"ukuran": 42, "warna": "hitam"}');
 
SELECT name, attributes ->> 'ram' AS ram
FROM products;

->> 'ram' returns "16GB" as text for the laptop, and NULL for the shoes that don't have a ram key — this flexibility is the essence of JSONB.

#Filtering with JSONB

JSONB can be used in WHERE directly:

SELECT name
FROM products
WHERE attributes @> '{"warna": "hitam"}';
 
SELECT name
FROM products
WHERE attributes ? 'ram';

The first query finds all products that contain the color black. The second query finds all products that have a ram attribute — whatever its value.

#JSONB Functions: jsonb_set and jsonb_array_elements

jsonb_set(jsonb, path, value) updates the value at a specific path and returns a new JSONB. Useful for updating a small part of a document:

UPDATE products
SET attributes = jsonb_set(attributes, '{warna}', '"merah"')
WHERE name = 'Laptop Pro'
RETURNING attributes;

jsonb_array_elements turns a JSON array into table rows — the opposite of storing a list:

SELECT jsonb_array_elements('["a", "b", "c"]'::JSONB) AS elemen;

This pattern is often used when JSON data must be joined with relational tables or aggregated per element. Combine it with LATERAL to process per row:

SELECT p.name, warna.value
FROM products p,
LATERAL jsonb_array_elements(p.attributes -> 'warna_list') AS warna;

#Working with the Array Data Type

Besides JSONB, PostgreSQL has a native Array type: TEXT[], INT[], and so on. Arrays are ideal for simple value lists that don't need structure.

#Declaration and Insert

CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    tags TEXT[] NOT NULL DEFAULT '{}'
);
 
INSERT INTO articles (title, tags)
VALUES ('Belajar SQL', ARRAY['sql', 'database', 'postgresql']);

Arrays can be written with the ARRAY[...] syntax or the '{...}' literal. Both are equivalent.

#Array Operators: ANY, @>, and UNNEST

SELECT title FROM articles
WHERE 'sql' = ANY(tags);
 
SELECT title FROM articles
WHERE tags @> ARRAY['sql', 'database'];
 
SELECT title, UNNEST(tags) AS tag
FROM articles;

The third query uses UNNEST to "explode" the array into one row per element — very useful for per-tag reports.

#Array vs JSONB: When to Use Which?

#Closing

In this episode 8 we've unlocked PostgreSQL's hybrid side: understanding when to use JSONB and when to use arrays, the difference between JSON and JSONB, the JSONB query operators ->, ->>, @>, and ?, the manipulation functions jsonb_set and jsonb_array_elements, and the array data type with the operators ANY, @>, and UNNEST.

Key takeaways:

• JSONB is the modern choice — fast, indexable, and great for flexible documents.
• The -> operator returns JSON, ->> returns text — don't mix them up.
• jsonb_set updates part of a document; jsonb_array_elements turns arrays into rows.
• Arrays (TEXT[]) are great for lightweight, simple value lists.
• UNNEST is the key to aggregating per array element.

In episode 9, we'll step up to the analytical level: Window Functions (Advanced Data Analytics) — from the difference between GROUP BY aggregation and window functions, the anatomy of the OVER() clause, window framing, to ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE and value functions LAG, LEAD, FIRST_VALUE, LAST_VALUE. This is one of the most beloved topics in advanced SQL.