unnest WITH ORDINALITY — Array Index Preservation & Multi-Array Joins

unnest(array) explodes an array into rows. WITH ORDINALITY adds a position column — the index of each element. Without it, you lose the array order and cannot reconstruct it, zip two arrays by position, or track which element came first.

-- Basic unnest
SELECT unnest(ARRAY['alpha', 'beta', 'gamma']) AS element;

-- WITH ORDINALITY preserves position
SELECT element, position
FROM unnest(ARRAY['alpha', 'beta', 'gamma'])
     WITH ORDINALITY AS t(element, position);
-- Returns: (alpha,1), (beta,2), (gamma,3)

Real patterns

-- Tags ordered by importance: first tag = primary, rest = secondary
WITH tweet_tags AS (
    SELECT
        t.id AS tweet_id,
        t.content,
        -- Simulate an ordered tag array per tweet
        ARRAY(
            SELECT h.tag
            FROM tw_tweet_hashtags th
            JOIN tw_hashtags h ON h.id = th.hashtag_id
            WHERE th.tweet_id = t.id
            ORDER BY h.id
        ) AS tags
    FROM tw_tweets t
    WHERE t.id <= 100
)
SELECT
    tweet_id,
    LEFT(content, 50)   AS preview,
    tag,
    position,
    CASE position
        WHEN 1 THEN 'primary'
        ELSE        'secondary'
    END                 AS tag_role
FROM tweet_tags
CROSS JOIN LATERAL unnest(tags) WITH ORDINALITY AS u(tag, position)
WHERE array_length(tags, 1) > 0
ORDER BY tweet_id, position;