Query Semi-structured Data with SQL in PostgreSQL

Environment: PostgreSQL + pgAdmin 4

Table of Contents

1. Introduction to semi structured data
2. Working with JSON data in PostgreSQL

• 2.1 Setting up the environment
• 2.2 JSON vs JSONB — Columns and storage
• 2.3 Extracting nested values with JSON operators
• 2.4 JSON filtering with containance and existence
• 2.5 JSON transformation with built-in functions
• 2.6 JSON data integration with relational queries

3. Query and manipulate Array data

• 3.1 Create and query Array columns
• 3.2 Access the elements and slices of an Array
• 3.3 Expansion and aggregation of Arrays with functions
• 3.4 Filter rows with ANY, ALL and containance
• 3.5 Combine Array and Relational Data

4. Specialized PostgreSQL Data Types

• 4.1 Create and query Range types
• 4.2 Filter Range data with overlap and inclusion operators
• 4.3 Construct and deconstruct Ranges with functions
• 4.4 Modeling with Composite and Enum Types

5. Summary of key operators and functions
6. General diagram and reusable patterns

1. Introduction to semi structured data

Semi-structured data is halfway in the data universe. They are not entirely structured like a classic table, but they are not free text either. They follow a flexible structure based on tables and key-value pairs, remaining flexible like text while being organized enough for PostgreSQL to search and parse them.

The three main categories of semi-structured data in PostgreSQL

Summary example: all three types in a single line

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

CREATE TABLE IF NOT EXISTS user_profile_demo (
    user_id       SERIAL PRIMARY KEY,
    profile       jsonb,
    tags          text[],
    active_period daterange
);

INSERT INTO user_profile_demo (profile, tags, active_period)
VALUES (
    '{
        "user": "Posty",
        "skills": ["SQL", "PostgreSQL", "Data Modeling"],
        "active": true
    }'::jsonb,
    ARRAY['database', 'analytics', 'performance'],
    daterange('2025-01-01', '2025-12-31')
);

SELECT * FROM user_profile_demo;

This INSERT demonstrates how PostgreSQL can store in a single row: a flexible JSONB document, an array of tags, and a date range — three different types, zero complications.

2. Working with JSON data in PostgreSQL

2.1 Setting up the environment

Configuring pgAdmin 4

The working environment is based on:

• pgAdmin 4 as query interface
• Local connection: host=localhost, user=postgres, database=postgres
• Dedicated job schema: semidata

The starting pattern of each demo is systematic:

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

Using TRUNCATE before each INSERT guarantees the reproducibility of the demos.

2.2 JSON vs JSONB — Columns and storage

Fundamental concept

PostgreSQL supports two types for storing JSON:

Practical rule: use jsonb by default except in very special cases (need to preserve the exact order of the keys).

Demo: Create a table with JSON and JSONB side by side

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

CREATE TABLE IF NOT EXISTS customer_contact_formats (
    customer_id   integer PRIMARY KEY,
    legacy_json   json,    -- stockage texte brut
    modern_jsonb  jsonb    -- stockage binaire
);

TRUNCATE customer_contact_formats;

-- Client 1 : ordre normal
-- Client 2 : clés dans un ordre différent (même contenu logique)
INSERT INTO customer_contact_formats (customer_id, legacy_json, modern_jsonb)
VALUES
(
    1,
    '{"email":"mary.smith@example.com",
      "phone":"+1-206-555-0101",
      "preferred_contact":"mobile"}',
    '{"email":"mary.smith@example.com",
      "phone":"+1-206-555-0101",
      "preferred_contact":"mobile"}'::jsonb
),
(
    2,
    -- Clés dans un ordre différent pour la comparaison JSON
    '{"preferred_contact":"mobile",
      "phone":"+1-206-555-0101",
      "email":"mary.smith@example.com"}',
    '{"preferred_contact":"mobile",
      "phone":"+1-206-555-0101",
      "email":"mary.smith@example.com"}'::jsonb
);

SELECT customer_id, legacy_json, modern_jsonb
FROM customer_contact_formats
ORDER BY customer_id;

Storage size comparison

SELECT customer_id,
       pg_column_size(legacy_json)  AS json_bytes,
       pg_column_size(modern_jsonb) AS jsonb_bytes
FROM customer_contact_formats
ORDER BY customer_id;

pg_column_size() returns the number of bytes used by the stored value. json stores the text as is (with key order), jsonb stores the normalized structure in binary.

Equality comparison

SELECT
    (j1.legacy_json::text = j2.legacy_json::text)  AS json_text_equal,
    (j1.modern_jsonb      = j2.modern_jsonb)       AS jsonb_equal
FROM customer_contact_formats j1
JOIN customer_contact_formats j2
  ON j1.customer_id = 1
 AND j2.customer_id = 2;

Expected result:

• json_text_equal = FALSE — same logical content but different key order → different texts
• jsonb_equal = TRUE — JSONB ignores key order, compares structure and values

Analogy: json reads each letter of a sentence; jsonb understands the meaning directly.

2.3 Extracting nested values ​​with JSON operators

Navigation operator table

Structure of the demo table

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

CREATE TABLE IF NOT EXISTS customer_profile_json (
    customer_id integer PRIMARY KEY,
    profile     jsonb NOT NULL
);

TRUNCATE customer_profile_json;

INSERT INTO customer_profile_json (customer_id, profile)
VALUES
(
    1,
    '{
        "name": "Mary Smith",
        "contact": {
            "email": "mary.smith@example.com",
            "phone": "+1-206-555-0101"
        },
        "preferences": {
            "genres": ["Action", "Comedy"],
            "watch_habits": {
                "binge_watcher": true,
                "weekly_rentals": 3
            }
        }
    }'::jsonb
),
(
    2,
    '{
        "name": "John Doe",
        "contact": {
            "email": "john.doe@example.com",
            "phone": "+1-425-555-0110"
        },
        "preferences": {
            "genres": ["Family", "Animation"],
            "watch_habits": {
                "binge_watcher": false,
                "weekly_rentals": 1
            }
        }
    }'::jsonb
);

Use -> and ->>

SELECT
    customer_id,
    profile -> 'name'     AS name_json,   -- retourne du JSON (valeur entre guillemets)
    profile ->> 'name'    AS name_text,   -- retourne du texte brut
    profile -> 'contact'  AS contact_json -- retourne l'objet imbriqué comme JSON
FROM customer_profile_json
ORDER BY customer_id;

Navigate through arrays with ->

SELECT
    customer_id,
    profile -> 'preferences' -> 'genres'       AS genres_json,
    profile -> 'preferences' -> 'genres' -> 0  AS first_genre_json,   -- index 0 = premier élément
    profile -> 'preferences' -> 'genres' ->> 0 AS first_genre_text
FROM customer_profile_json
ORDER BY customer_id;

Important: the -> operator uses the 0-based index for JSON arrays (unlike native PostgreSQL arrays which are 1-based).

Use #> and #>> for deep paths

SELECT
    customer_id,
    profile #>  ARRAY['preferences','watch_habits']                       AS watch_habits_json,
    profile #>> ARRAY['preferences','watch_habits','weekly_rentals']       AS weekly_rentals_text
FROM customer_profile_json
ORDER BY customer_id;

Instead of chaining arrow operators, #> and #>> accept a full path in a single expression.

Use extracted values ​​in filters and aggregates

-- Filtrer : clients qui louent au moins 2 films par semaine
SELECT
    customer_id,
    profile ->> 'name' AS name,
    (profile #>> ARRAY['preferences','watch_habits','weekly_rentals'])::int
        AS weekly_rentals
FROM customer_profile_json
WHERE (profile #>> ARRAY['preferences','watch_habits','weekly_rentals'])::int >= 2
ORDER BY customer_id;

-- Agrégat : moyenne des locations hebdomadaires
SELECT
    avg(
        (profile #>> ARRAY['preferences','watch_habits','weekly_rentals'])::int
    ) AS avg_weekly_rentals
FROM customer_profile_json;

The ::int cast is necessary because #>> always returns text. We extract the text, then cast it to the desired digital type.

2.4 JSON filtering with containance and existence

The two JSONB filtering mechanisms

Demo table with 3 varied profiles

CREATE TABLE IF NOT EXISTS customer_profile_json (
    customer_id integer PRIMARY KEY,
    profile     jsonb NOT NULL
);

TRUNCATE customer_profile_json;

INSERT INTO customer_profile_json (customer_id, profile)
VALUES
(1, '{
    "name": "Mary Smith",
    "contact": {"email": "mary.smith@example.com", "phone": "+1-206-555-0101"},
    "preferences": {
        "genres": ["Action", "Comedy"],
        "watch_habits": {"binge_watcher": true, "weekly_rentals": 3}
    }
}'::jsonb),
(2, '{
    "name": "John Doe",
    "contact": {"email": "john.doe@example.com", "phone": "+1-425-555-0110"},
    "preferences": {
        "genres": ["Family", "Animation"],
        "watch_habits": {"binge_watcher": false, "weekly_rentals": 1}
    }
}'::jsonb),
(3, '{
    "name": "Family Account",
    "contact": {"email": "family@example.com", "phone": "+1-425-555-0200"},
    "preferences": {
        "genres": ["Family", "Animation"],
        "kids_profile": true
    }
}'::jsonb);

Containment with @>

-- Clients qui aiment le genre "Action"
SELECT
    customer_id,
    profile ->> 'name' AS name,
    profile -> 'preferences' -> 'genres' AS genres
FROM customer_profile_json
WHERE profile @> '{"preferences":{"genres":["Action"]}}'
ORDER BY customer_id;
-- Résultat attendu : seulement Mary (customer_id = 1)

-- Clients avec binge_watcher = false
SELECT
    customer_id,
    profile ->> 'name' AS name,
    profile -> 'preferences' -> 'watch_habits' AS watch_habits
FROM customer_profile_json
WHERE profile @> '{"preferences":{"watch_habits":{"binge_watcher": false}}}'
ORDER BY customer_id;
-- Résultat attendu : seulement John
-- (Mary = true, Family Account n'a pas watch_habits)

Warning: The container @> is strict. If the pattern does not match exactly (including Boolean values), the row is excluded.

Existence of key with ?

-- Documents qui ont une clé "kids_profile" dans preferences
SELECT
    customer_id,
    profile ->> 'name' AS name,
    profile -> 'preferences' AS preferences
FROM customer_profile_json
WHERE (profile -> 'preferences') ? 'kids_profile'
ORDER BY customer_id;
-- Résultat attendu : seulement Family Account (customer_id = 3)

Multiple existence with ?|

-- Clients qui ont soit "kids_profile" soit "watch_habits"
SELECT
    customer_id,
    profile ->> 'name' AS name,
    profile -> 'preferences' AS preferences
FROM customer_profile_json
WHERE (profile -> 'preferences') ?| ARRAY['kids_profile', 'watch_habits']
ORDER BY customer_id;
-- Résultat attendu : les 3 clients
-- (Mary et John ont watch_habits, Family Account a kids_profile)

Filter combination

-- Profils family-friendly avec un indicateur kids_profile
SELECT
    customer_id,
    profile ->> 'name' AS name,
    profile -> 'preferences' -> 'genres' AS genres,
    profile -> 'preferences' -> 'kids_profile' AS kids_profile
FROM customer_profile_json
WHERE profile @> '{"preferences":{"genres":["Family"]}}'
  AND (profile -> 'preferences') ? 'kids_profile'
ORDER BY customer_id;
-- Résultat attendu : seulement Family Account

2.5 JSON transformation with built-in functions

The three JSONB transformation functions

Demo table: movie metadata

CREATE TABLE IF NOT EXISTS film_extra_json (
    film_id   integer PRIMARY KEY,
    metadata  jsonb NOT NULL
);

TRUNCATE film_extra_json;

INSERT INTO film_extra_json (film_id, metadata)
VALUES
(1, '{
    "title": "Action Blast",
    "genres": ["Action", "Thriller"],
    "rating_breakdown": {"5": 120, "4": 250, "3": 60},
    "total_reviews": 430
}'::jsonb),
(2, '{
    "title": "Happy Friends",
    "genres": ["Family", "Animation"],
    "rating_breakdown": {"5": 80, "4": 190, "3": 90},
    "total_reviews": 370
}'::jsonb);

jsonb_each() — Row key-value expansion

SELECT
    fe.film_id,
    rb.key   AS star_value,
    rb.value AS review_count
FROM film_extra_json fe,
     jsonb_each(fe.metadata -> 'rating_breakdown') AS rb(key, value)
ORDER BY fe.film_id, rb.key;

Expected result:

film_id | star_value | review_count
--------+------------+-------------
      1 | 3          | 60
      1 | 4          | 250
      1 | 5          | 120
      2 | 3          | 90
      2 | 4          | 190
      2 | 5          | 80

Each rating bucket (e.g. "5": 120) becomes its own line. Ideal for pivoting from JSON to relational.

jsonb_array_elements() — Expansion of arrays into rows

SELECT
    fe.film_id,
    g.genre AS genre
FROM film_extra_json fe,
     jsonb_array_elements(fe.metadata -> 'genres') AS g(genre)
ORDER BY fe.film_id, genre;

Expected result:

film_id | genre
--------+----------
      1 | "Action"
      1 | "Thriller"
      2 | "Animation"
      2 | "Family"

Each genre becomes its own row, making it easier to analyze by genre.

jsonb_build_object() — Building a new JSONB document

SELECT
    film_id,
    jsonb_build_object(
        'film_id',      film_id,
        'title',        metadata ->> 'title',
        'main_genre',   (metadata -> 'genres' -> 0),
        'total_reviews', metadata ->> 'total_reviews'
    ) AS summary_json
FROM film_extra_json
ORDER BY film_id;

jsonb_build_object() assembles a new JSON document from extracted values ​​— useful for creating API responses or summaries.

2.6 Integrating JSON data with relational queries

Concept: the hybrid model

In real systems, JSONB does not live alone. The hybrid model combines:

• Relational table: stores stable information (identifier, title, year)
• JSONB table: stores flexible attributes (genres, notes, statistics)

Create both tables

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

-- Table relationnelle
CREATE TABLE IF NOT EXISTS film_relational (
    film_id      integer PRIMARY KEY,
    title        text NOT NULL,
    release_year integer
);

TRUNCATE film_relational;

INSERT INTO film_relational (film_id, title, release_year)
VALUES
    (1, 'Action Blast', 2006),
    (2, 'Happy Friends', 2008);

-- Table JSONB des métadonnées
CREATE TABLE IF NOT EXISTS film_extra_json (
    film_id   integer PRIMARY KEY,
    metadata  jsonb NOT NULL
);

TRUNCATE film_extra_json;

INSERT INTO film_extra_json (film_id, metadata)
VALUES
(1, '{
    "genres": ["Action", "Thriller"],
    "rating_breakdown": {"5": 120, "4": 250, "3": 60},
    "total_reviews": 430
}'::jsonb),
(2, '{
    "genres": ["Family", "Animation"],
    "rating_breakdown": {"5": 80, "4": 190, "3": 90},
    "total_reviews": 370
}'::jsonb);

relational JOIN + JSONB extraction

SELECT
    f.film_id,
    f.title,
    f.release_year,
    (fe.metadata -> 'genres' ->> 0)                  AS main_genre,
    jsonb_array_length(fe.metadata -> 'genres')      AS genre_count,
    (fe.metadata -> 'rating_breakdown' ->> '5')::int AS five_star_count,
    (fe.metadata ->> 'total_reviews')::int           AS total_reviews
FROM film_relational f
JOIN film_extra_json fe
  ON fe.film_id = f.film_id
ORDER BY f.film_id;

Naming the extracted columns with clear aliases (main_genre, five_star_count, etc.) makes the query readable and the result usable.

Structured aggregation + JSONB together

-- Résumé par genre : expansion des genres et somme des reviews
SELECT
    g.genre::text                               AS genre,
    count(*)                                    AS film_count,
    sum((fe.metadata ->> 'total_reviews')::int) AS total_reviews_sum
FROM film_relational f
JOIN film_extra_json fe
  ON fe.film_id = f.film_id
CROSS JOIN LATERAL jsonb_array_elements(fe.metadata -> 'genres') AS g(genre)
GROUP BY g.genre
ORDER BY genre;

The CROSS JOIN LATERAL pattern with jsonb_array_elements() is the key: it unrolls the array for each film, then allowing a standard GROUP BY on genres.

3. Query and manipulate Array data

Concept: Arrays vs JSON

3.1 Create and query Array columns

Create a table with Array columns

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

CREATE TABLE IF NOT EXISTS film_arrays_demo (
    film_id  integer PRIMARY KEY,
    title    text NOT NULL,
    tags     text[] NOT NULL,   -- array de texte
    ratings  int[]  NOT NULL    -- array d'entiers
);

TRUNCATE film_arrays_demo;

INSERT INTO film_arrays_demo (film_id, title, tags, ratings)
VALUES
    (1, 'Action Blast',
        ARRAY['Action', 'Adventure', 'Thriller'],
        ARRAY[5, 4, 4, 5, 3]
    ),
    (2, 'Happy Friends',
        ARRAY['Family', 'Comedy', 'Animation'],
        ARRAY[4, 4, 3, 5]
    ),
    (3, 'Action Heroes',
        ARRAY['Action', 'Comedy'],
        ARRAY[5, 5, 4]
    );

PostgreSQL displays arrays with the notation {value1,value2,...}.

Basic access: 1-based indexing

-- Accès au premier tag (index 1, pas 0 !)
SELECT
    film_id,
    title,
    tags[1] AS first_tag,
    tags
FROM film_arrays_demo
ORDER BY film_id;

Important: Native PostgreSQL arrays use 1-based indexing (the first element is at position 1).

Count elements with array_length()

SELECT
    film_id,
    title,
    array_length(ratings, 1) AS rating_count,
    ratings
FROM film_arrays_demo
ORDER BY film_id;

The second argument 1 specifies the dimension (PostgreSQL arrays can be multidimensional).

Convert Array to JSONB for comparison

SELECT
    film_id,
    title,
    tags,
    to_jsonb(tags) AS tags_as_json
FROM film_arrays_demo
ORDER BY film_id;

3.2 Accessing elements and slices of an Array

Access by position: first and last element

SELECT
    film_id,
    title,
    tags[1]                          AS tag_first,
    tags[array_length(tags, 1)]      AS tag_last,
    tags
FROM film_arrays_demo
ORDER BY film_id;

Slice notation: array[start:end]

SELECT
    film_id,
    title,
    tags[1:2]    AS first_two_tags,   -- sous-array des 2 premiers éléments
    ratings[2:4] AS mid_ratings,      -- éléments aux positions 2 à 4
    ratings
FROM film_arrays_demo
ORDER BY film_id;

The slice returns a subarray that maintains the original order.

Dynamic extraction of last elements

-- Extraire les 2 dernières notes (pattern reporting)
SELECT
    film_id,
    title,
    ratings[
        GREATEST(array_length(ratings, 1) - 1, 1)
        :
        array_length(ratings, 1)
    ] AS last_two_ratings,
    ratings
FROM film_arrays_demo
ORDER BY film_id;

This pattern allows you to extract the “tail” of an array without knowing its length in advance.

3.3 Expansion and aggregation of Arrays with functions

The three main functions

unnest() — Row expansion

-- Chaque tag devient sa propre ligne
SELECT
    film_id,
    title,
    unnest(tags) AS tag
FROM film_arrays_demo
ORDER BY title, tag;

-- Calculer la note moyenne par film en décomposant les ratings
SELECT
    f.film_id,
    f.title,
    avg(r.rating) AS avg_rating
FROM film_arrays_demo f
CROSS JOIN LATERAL unnest(f.ratings) AS r(rating)
GROUP BY f.film_id, f.title
ORDER BY f.film_id;

unnest() + array_agg() — Decompose then reconstruct

-- Pour chaque tag : compter les films et lister leurs titres
SELECT
    tag,
    count(*) AS film_count,
    array_agg(title ORDER BY title) AS films_with_tag
FROM (
    SELECT
        title,
        unnest(tags) AS tag
    FROM film_arrays_demo
) AS expanded
GROUP BY tag
ORDER BY tag;

Powerful pattern: we go from list → lines → list in a single SQL query.

array_append() — Add an element

SELECT
    film_id,
    title,
    tags,
    array_append(tags, 'Staff Pick') AS tags_with_staff_pick,
    ratings,
    array_append(ratings, 5) AS ratings_with_bonus
FROM film_arrays_demo
ORDER BY film_id;

array_append() returns a new array without modifying the source table.

3.4 Filter rows with ANY, ALL and containance

Array filter operator table

Demo table with 4 films

INSERT INTO film_arrays_demo (film_id, title, tags, ratings)
VALUES
    (1, 'Action Blast',  ARRAY['Action', 'Adventure'], ARRAY[5, 4, 4, 5]),
    (2, 'Action Heroes', ARRAY['Action', 'Comedy'],    ARRAY[5, 5, 4]),
    (3, 'Quiet Drama',   ARRAY['Drama'],               ARRAY[3, 3, 4]),
    (4, 'Family Night',  ARRAY['Family', 'Comedy'],    ARRAY[4, 4, 5]);

ANY on text tags

-- Films qui ont le tag 'Action' (au moins une occurrence)
SELECT film_id, title, tags
FROM film_arrays_demo
WHERE 'Action' = ANY(tags)
ORDER BY film_id;
-- Résultat : Action Blast, Action Heroes

ANY on numerical ratings

-- Films qui ont reçu au moins une note parfaite de 5
SELECT film_id, title, ratings
FROM film_arrays_demo
WHERE 5 = ANY(ratings)
ORDER BY film_id;
-- Résultat : Action Blast, Action Heroes, Family Night

ALL for strict conditions

-- Films dont TOUTES les notes sont >= 4
SELECT film_id, title, ratings
FROM film_arrays_demo
WHERE 4 <= ALL(ratings)
ORDER BY film_id;
-- Résultat : Action Blast, Action Heroes, Family Night
-- (Quiet Drama est exclu car il a des 3)

@> for container

-- Films qui sont à la fois "Action" ET "Comedy"
SELECT film_id, title, tags
FROM film_arrays_demo
WHERE tags @> ARRAY['Action', 'Comedy']
ORDER BY film_id;
-- Résultat : Action Heroes uniquement

3.5 Combine Array and Relational Data

Many-to-many model without junction table

Arrays allow you to model many-to-many relationships directly in a column, without a junction table.

-- Films avec tags et revenus
CREATE TABLE IF NOT EXISTS films_with_tags_revenue (
    film_id       integer PRIMARY KEY,
    title         text    NOT NULL,
    tags          text[]  NOT NULL,
    ticket_sales  numeric NOT NULL
);

-- Table de lookup genre → catégorie
CREATE TABLE IF NOT EXISTS genre_category_lookup (
    genre    text PRIMARY KEY,
    category text NOT NULL
);

TRUNCATE films_with_tags_revenue;
TRUNCATE genre_category_lookup;

INSERT INTO genre_category_lookup (genre, category)
VALUES
    ('Action',    'Blockbuster'),
    ('Adventure', 'Blockbuster'),
    ('Thriller',  'Blockbuster'),
    ('Comedy',    'Light'),
    ('Family',    'Light'),
    ('Animation', 'Light'),
    ('Drama',     'Serious');

INSERT INTO films_with_tags_revenue (film_id, title, tags, ticket_sales)
VALUES
    (1, 'Action Blast',  ARRAY['Action', 'Adventure', 'Thriller'], 320.0),
    (2, 'Action Heroes', ARRAY['Action', 'Comedy'],                 210.0),
    (3, 'Quiet Drama',   ARRAY['Drama'],                             85.0),
    (4, 'Family Night',  ARRAY['Family', 'Comedy', 'Animation'],   190.0);

Unnest + JOIN with lookup table

-- Joindre chaque tag à sa catégorie
SELECT
    f.film_id,
    f.title,
    g.genre,
    l.category,
    f.ticket_sales
FROM films_with_tags_revenue f
CROSS JOIN LATERAL unnest(f.tags) AS g(genre)
LEFT JOIN genre_category_lookup l
       ON g.genre = l.genre
ORDER BY f.film_id, g.genre;

Aggregation by gender

SELECT
    g.genre,
    count(*) AS film_count,
    sum(f.ticket_sales) AS total_ticket_sales
FROM films_with_tags_revenue f
CROSS JOIN LATERAL unnest(f.tags) AS g(genre)
GROUP BY g.genre
ORDER BY total_ticket_sales DESC, g.genre;

Aggregation by category (top level)

SELECT
    l.category,
    count(DISTINCT f.film_id) AS film_count,
    sum(f.ticket_sales)       AS total_ticket_sales
FROM films_with_tags_revenue f
CROSS JOIN LATERAL unnest(f.tags) AS g(genre)
JOIN genre_category_lookup l
  ON g.genre = l.genre
GROUP BY l.category
ORDER BY total_ticket_sales DESC, l.category;

The CROSS JOIN LATERAL unnest(tags) pattern is fundamental for transforming many-to-many relationships stored in an array into aggregable relational results.

4. Specialized PostgreSQL Data Types

Overview of specialized types

4.1 Create and query Range types

Range types available in PostgreSQL

Syntax of inclusive/exclusive bounds

PostgreSQL behavior: For int4range, PostgreSQL always normalizes the upper bound to exclusive. [100, 110] is stored as [100, 111).

Create the demo table

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

CREATE TABLE IF NOT EXISTS film_ranges_demo (
    film_id        integer PRIMARY KEY,
    title          text       NOT NULL,
    runtime_band   int4range  NOT NULL,
    release_window daterange  NOT NULL
);

TRUNCATE film_ranges_demo;

INSERT INTO film_ranges_demo (film_id, title, runtime_band, release_window)
VALUES
    (1, 'Action Blast',
        '[100,130)'::int4range,
        '[2025-06-01,2025-06-15)'::daterange
    ),
    (2, 'Quiet Drama',
        '(80,100]'::int4range,
        '(2025-06-10,2025-06-20]'::daterange
    ),
    (3, 'Family Night',
        '[100,110]'::int4range,
        '[2025-06-05,2025-06-30]'::daterange
    );

SELECT * FROM film_ranges_demo ORDER BY film_id;

Inspect terminals with lower() and upper()

SELECT
    film_id,
    title,
    runtime_band,
    lower(runtime_band) AS band_start,
    upper(runtime_band) AS band_end,
    runtime_band @> lower(runtime_band) AS includes_start,
    runtime_band @> upper(runtime_band) AS includes_end
FROM film_ranges_demo
ORDER BY film_id;

Check if a date is included

SELECT
    film_id,
    title,
    release_window,
    release_window @> DATE '2025-06-10' AS includes_jun10
FROM film_ranges_demo
ORDER BY film_id;

Filter by terminals

-- Films dont la release commence après le 5 juin
SELECT film_id, title, release_window
FROM film_ranges_demo
WHERE lower(release_window) > DATE '2025-06-05'
ORDER BY film_id;

-- Films dont le runtime_band commence à 100 ou plus
SELECT film_id, title, runtime_band
FROM film_ranges_demo
WHERE lower(runtime_band) >= 100
ORDER BY film_id;

4.2 Filter Range data with overlap and inclusion operators

Range Operator Table

&& — Overlap

-- Films dont la release_window chevauche la période 16-18 juin
SELECT film_id, title, release_window
FROM film_ranges_demo
WHERE release_window && '[2025-06-16,2025-06-18)'::daterange
ORDER BY film_id;
-- Quiet Drama et Family Night chevauchent cette fenêtre

-- Films dont le runtime_band chevauche le créneau 101-115 min
SELECT film_id, title, runtime_band
FROM film_ranges_demo
WHERE runtime_band && '[101,115)'::int4range
ORDER BY film_id;
-- Action Blast et Family Night partagent des minutes avec ce créneau

@> — Containment of a value

-- Films disponibles le 1er juin
SELECT
    film_id,
    title,
    release_window,
    release_window @> DATE '2025-06-01' AS plays_on_jun1
FROM film_ranges_demo
ORDER BY film_id;

@> — Containment of a range

-- Films dont la release_window est entièrement dans la campagne marketing 5-25 juin
SELECT film_id, title, release_window
FROM film_ranges_demo
WHERE '[2025-06-05,2025-06-25]'::daterange @> release_window
ORDER BY film_id;

<@ — Is-inside check

-- Est-ce qu'un runtime de 105 min est dans chaque runtime_band ?
SELECT
    film_id,
    title,
    runtime_band,
    105 <@ runtime_band AS runtime_105_fits
FROM film_ranges_demo
ORDER BY film_id;

-- Films dont le runtime_band est entièrement dans le créneau [90, 140]
SELECT film_id, title, runtime_band
FROM film_ranges_demo
WHERE runtime_band <@ '[90,140]'::int4range
ORDER BY film_id;

Summary query — all three operators side by side

SELECT
    film_id,
    title,
    release_window,
    release_window && '[2025-06-16,2025-06-18)'::daterange AS overlaps_target,
    release_window @> DATE '2025-06-01'                    AS contains_jun1,
    release_window <@ '[2025-06-01,2025-06-30]'::daterange AS inside_june
FROM film_ranges_demo
ORDER BY film_id;

4.3 Constructing and deconstructing Ranges with functions

Key range functions

Deconstruct into simple terminals

SELECT
    film_id,
    title,
    runtime_band,
    lower(runtime_band)   AS runtime_start_min,
    upper(runtime_band)   AS runtime_end_min,
    release_window,
    lower(release_window) AS release_start_date,
    upper(release_window) AS release_end_date
FROM film_ranges_demo
ORDER BY film_id;

Calculate durations from terminals

SELECT
    film_id,
    title,
    runtime_band,
    upper(runtime_band) - lower(runtime_band) AS runtime_duration_min,
    release_window,
    upper(release_window) - lower(release_window) AS release_duration_days
FROM film_ranges_demo
ORDER BY film_id;

Expected results:

• Action Blast: 30 minutes of runtime, 14 days of release
• Quiet Drama: 20 minutes, 10 days
• Family Night: 11 minutes, 26 days

range_merge() — Merge overlapping ranges

CREATE TABLE IF NOT EXISTS screen_availability_demo (
    slot_id      integer PRIMARY KEY,
    screen_name  text      NOT NULL,
    availability daterange NOT NULL
);

TRUNCATE screen_availability_demo;

INSERT INTO screen_availability_demo (slot_id, screen_name, availability)
VALUES
    (1, 'Screen A', '[2025-06-01,2025-06-05)'::daterange),
    (2, 'Screen A', '[2025-06-04,2025-06-10)'::daterange),  -- chevauche slot 1
    (3, 'Screen A', '[2025-06-10,2025-06-12)'::daterange),  -- touche slot 2
    (4, 'Screen A', '[2025-06-20,2025-06-25)'::daterange),  -- bloc séparé
    (5, 'Screen B', '[2025-06-03,2025-06-08)'::daterange),
    (6, 'Screen B', '[2025-06-07,2025-06-15)'::daterange);  -- chevauche slot 5

SELECT
    screen_name,
    range_agg(availability)              AS raw_slots,
    range_merge(range_agg(availability)) AS merged_availability
FROM screen_availability_demo
GROUP BY screen_name
ORDER BY screen_name;

Expected results:

• Screen A: two merged blocks [2025-06-01,2025-06-12) and [2025-06-20,2025-06-25)
• Screen B: a single block [2025-06-03,2025-06-15)

Combine merge and duration calculation

WITH merged AS (
    SELECT
        screen_name,
        range_merge(range_agg(availability)) AS merged_availability
    FROM screen_availability_demo
    GROUP BY screen_name
)
SELECT
    screen_name,
    merged_availability,
    lower(merged_availability) AS available_from,
    upper(merged_availability) AS available_to,
    upper(merged_availability) - lower(merged_availability)
        AS availability_days
FROM merged
ORDER BY screen_name;

4.4 Modeling with Composite and Enum types

Enum Types — Controlled Fixed Values

An ENUM defines a finite list of allowed values. Any INSERT of a value outside of this list causes an error.

DROP TYPE IF EXISTS film_status CASCADE;

CREATE TYPE film_status AS ENUM (
    'draft',
    'editing',
    'released'
);

Any INSERT with a value like 'in_review' or 'pending' (not in the list) will raise a PostgreSQL error immediately.

Composite Types — Group related fields

A composite type groups several fields into a single structured object. This keeps the schema clean while still storing some structure.

DROP TYPE IF EXISTS director_info CASCADE;

CREATE TYPE director_info AS (
    full_name        text,
    experience_years integer,
    primary_genre    text
);

Create a table with Composite and Enum

DROP TABLE IF EXISTS film_composite_demo CASCADE;

CREATE TABLE film_composite_demo (
    film_id     integer PRIMARY KEY,
    title       text NOT NULL,
    director    director_info,         -- type composite
    status      film_status NOT NULL,  -- type enum
    box_office  numrange               -- type range
);

TRUNCATE film_composite_demo;

INSERT INTO film_composite_demo (film_id, title, director, status, box_office)
VALUES
    (1, 'Action Blast',
        ROW('Rita Collins', 15, 'Action')::director_info,
        'released',
        numrange(50000000, 120000000, '[)')
    ),
    (2, 'Quiet Drama',
        ROW('Evan Patel', 8, 'Drama')::director_info,
        'editing',
        numrange(2000000, 9000000, '[)')
    ),
    (3, 'Family Night',
        ROW('Sara Lin', 5, 'Family')::director_info,
        'draft',
        numrange(NULL, NULL)  -- plage vide pour un film non sorti
    );

SELECT * FROM film_composite_demo ORDER BY film_id;

Use ROW(...)::director_info to construct a composite value.

Access fields of a Composite type

SELECT
    f.film_id,
    f.title,
    (f.director).full_name        AS director_name,
    (f.director).experience_years AS years_experience,
    (f.director).primary_genre    AS genre_specialty
FROM film_composite_demo AS f
ORDER BY f.film_id;

The syntax is (alias.composite_column).field_name. Parentheses around f.director are required to avoid ambiguity.

Filter by Enum value

-- Films qui ne sont pas encore sortis
SELECT film_id, title, status
FROM film_composite_demo AS f
WHERE f.status IN ('draft', 'editing')
ORDER BY f.film_id;

Update a field in a Composite type

-- Augmenter l'expérience du directeur du film 3 d'un an
UPDATE film_composite_demo AS f
SET director = ROW(
        (f.director).full_name,
        (f.director).experience_years + 1,
        (f.director).primary_genre
    )::director_info
WHERE f.film_id = 3;

SELECT film_id, director FROM film_composite_demo ORDER BY film_id;

To modify a field of a composite type, you must rebuild the entire object with ROW(...)::type.

Combine Composite and Enum in a filter

-- Films sortis dirigés par des directeurs très expérimentés (>= 10 ans)
SELECT
    f.film_id,
    f.title,
    f.status,
    (f.director).full_name        AS director_name,
    (f.director).experience_years AS years_experience
FROM film_composite_demo AS f
WHERE f.status = 'released'
  AND (f.director).experience_years >= 10
ORDER BY f.film_id;

5. Summary of key operators and functions

JSON / JSONB operators

JSON / JSONB functions

Array Operators

Array functions

Range Operators

Range Functions

6. General schema and reusable patterns

Pattern 1: Standard setup of each demo

CREATE SCHEMA IF NOT EXISTS semidata;
SET search_path TO semidata, public;

CREATE TABLE IF NOT EXISTS ma_table (...);
TRUNCATE ma_table;   -- garantit la reproductibilité
INSERT INTO ma_table (...) VALUES (...);
SELECT * FROM ma_table ORDER BY id;

Pattern 2: CROSS JOIN LATERAL to unroll the arrays

-- Dérouler un array et joindre chaque élément à une table de lookup
SELECT f.film_id, f.title, g.tag, l.category
FROM ma_table f
CROSS JOIN LATERAL unnest(f.tags) AS g(tag)
LEFT JOIN lookup_table l ON g.tag = l.tag;

This pattern is the foundation of many-to-many analyzes with arrays.

Pattern 3: CROSS LATERAL JOIN to unroll JSONB arrays

-- Dérouler un array JSONB pour GROUP BY
SELECT genre, count(*), sum(total_reviews)
FROM ma_table f
CROSS JOIN LATERAL jsonb_array_elements(f.metadata -> 'genres') AS g(genre)
GROUP BY genre;

Pattern 4: JSONB extraction with explicit casting

-- Toujours caster les valeurs extraites vers le bon type
(profile #>> ARRAY['preferences','watch_habits','weekly_rentals'])::int
(metadata ->> 'total_reviews')::int
(metadata -> 'rating_breakdown' ->> '5')::int

->> and #>> always return text. Casting to int, numeric, boolean, etc. is the responsibility of the developer.

Pattern 5: CTE + range_merge for schedules

WITH merged AS (
    SELECT
        screen_name,
        range_merge(range_agg(availability)) AS merged
    FROM disponibilites
    GROUP BY screen_name
)
SELECT
    screen_name,
    lower(merged) AS debut,
    upper(merged) AS fin,
    upper(merged) - lower(merged) AS duree_jours
FROM merged;

Pattern 6: Updating a Composite field

UPDATE ma_table AS t
SET ma_colonne_composite = ROW(
    (t.ma_colonne_composite).champ1,
    (t.ma_colonne_composite).champ2 + 1,   -- seul ce champ change
    (t.ma_colonne_composite).champ3
)::mon_type_composite
WHERE t.id = 3;

Summary: when to use which type?

Search Terms

query · semi-structured · data · sql · postgresql · databases · jsonb · array · functions · json · range · types · composite · filter · operators · arrays · pattern · enum · aggregation · expansion · join · terminals · access · columns