Counting Elements in a JSON Array

Concepts covered: JSON Extraction, Sums & Averages

The request sounds like an aggregation: 'how many items were in each order', 'find carts with 3 or more products', 'what's the average basket size'. The reflex is to explode the array into one row per element and count them back up. You don't need to, because a JSON array already knows its own length, and every major engine has a function that returns it as an integer on the same row.

Each function is handed the array itself, which ties back to the 2 forms of extraction. In Postgres the argument is payload->'items', the JSON form, because a container has to stay JSON. Pass payload->>'items' and the call fails, since jsonb_array_length takes jsonb and the text form is plain text.

-- Purchases with 2 or more line items (Postgres)
WITH purchases AS (
  SELECT
    event_id,
    payload->>'user_id'                  AS user_id,
    jsonb_array_length(payload->'items') AS item_count
  FROM events
  WHERE payload->>'event_type' = 'purchase'
)
SELECT event_id, user_id, item_count
FROM purchases
WHERE item_count >= 2;

The purchase went in as one row and came out as one row, and the count came from a single function call. The query never changed grain: it started with one row per event and ended with one row per event, so any other event-level column in the CTE stays correct beside item_count.

Why not unnest and count

Unnest, then COUNT(*) GROUP BY event_id

• Event 2 becomes 2 rows, then folds back to 1
• An event with "items": [] produces no rows and drops out
• Other event columns repeat once per item until regrouped
• A GROUP BY over every event column to read one number

Array length on each row

• Event 2 stays 1 row with item_count 2
• An empty array returns 0 and the event stays
• Event-level columns such as amount stay at one per event
• A single call you can put inside a CASE or an AVG as easily as in WHERE

The interviewer is usually listening for the second pair. A count built from unnested rows cannot report 0, because an empty cart contributes no rows to count, so an average basket size computed that way comes out too high. Saying 'the length function returns 0 for an empty array, so empty carts stay in the average' is the line that shows you thought about the empty cart before being asked.

Wrong counts that look plausible

Lengths compose with the rest of the query

Once item_count is an integer column in the CTE, everything relational works on it: AVG(item_count) for average basket size, SUM(item_count) for line items across all purchases, a CASE that buckets carts by size with breaks at 2 items and at 5. The path from the second section and the length function combine freely in the same CTE, as the query below shows.

-- Average basket size per platform (Postgres)
WITH purchases AS (
  SELECT
    payload->'device'->>'platform'       AS platform,
    jsonb_array_length(payload->'items') AS item_count
  FROM events
  WHERE payload->>'event_type' = 'purchase'
)
SELECT platform,
       COUNT(*)        AS purchases,
       AVG(item_count) AS avg_items,
       SUM(item_count) AS line_items
FROM purchases
GROUP BY platform;

Ask one clarifying question before you settle on the length: does the interviewer mean line items or units bought? Line items is the array length, 2 for event 2. Units is the sum of each element's qty, 3 for event 2, and reading every element's qty is a different query from counting the elements.

Do

• Name your engine and its extraction function before writing the first line
• Extract each key once into a named, cast column inside a CTE
• Use the text-returning form for every final value your query compares or groups by
• Pass the JSON form of an array to the engine's length function
• Select from the extraction CTE on a few rows before aggregating

Avoid

• Don't compare a JSON-returning extraction to a string literal
• Don't write [1] when you mean the first element of an array
• Don't explode an array into rows only to count its elements
• Don't call LENGTH on an array's text; it counts characters
• Don't trust a column of NULLs until the path spelling is checked

About this lesson

This section is part of the Semi-Structured Data: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.