Массивы JSON в SQL: длина, разворот и соединение

Короткий ответ: три действия с массивом

json_array_length(properties::json -> 'extras') возвращает число элементов. В PostgreSQL CROSS JOIN LATERAL json_array_elements_text(properties::json -> 'extras') AS x(extra) превращает каждый элемент в строку; в DuckDB для той же работы используйте json_each и извлечение value ->> '$'. Пустой массив даёт ноль строк при CROSS и одну сохранённую строку с NULL при LEFT JOIN LATERAL.

Сначала удаляйте дубли доставки события: event_id у повтора новый, а client_id, event_time и properties совпадают. Потом фиксируйте зерно: строка purchases — покупка, строка после разворота — покупка × элемент. Упражнения курса используют весь журнал; здесь для проверки берём только 1–3 июля и другие вопросы.

Какая именно колонка хранит JSON

app_events.properties имеет тип VARCHAR с валидным JSON, поэтому выражение начинается с properties::json. Для списка используем ->: он сохраняет JSON-массив. ->> достаёт текстовое значение и подходит для номера брони либо числового значения перед явным приведением, но не для передачи массива в json_array_length.

Операторы ->, ->>, отсутствующие ключи и различие JSON/JSONB подробнее разобраны в основной статье об операторах JSONB. Здесь важно другое: после разворота уже не одна строка на событие. Если на одной покупке три билета и две услуги, два независимых разворота в одном FROM создадут шесть комбинаций. Для каждого массива считайте свою витрину на нужном зерне.

Длина массива и проверка пустых списков

После удаления повторных доставок у 1 150 покупок extras — пустой массив, а у 2 413 в нём есть хотя бы одна услуга. Всего перечислено 3 405 услуг. Сумма длин массивов равна числу строк при INNER/CROSS-развороте; это простая проверка, что мы не потеряли элемент на пути.

Ноль, NULL и отсутствующий ключ — разные состояния. В данных выбранного периода extras есть у каждой покупки, но это свойство набора, а не контракт функции. Для пустого [] длина ноль; для отсутствующего ключа результат NULL; для JSON null поведение следует проверять отдельно, прежде чем трактовать его как пустой список. При импорте другой версии событий оставьте отдельный счётчик пропусков ключа.

В pandas json.loads разбирает строку, json_normalize выделяет поля, а explode разворачивает список. Пустой [] здесь оставляет строку с NaN: 3 563 покупки превращаются в 4 555 строк, из которых 1 150 имеют пустую услугу. Это ближе к LEFT JOIN LATERAL, чем к CROSS-развороту. Суммировать amount после explode нельзя: на строках с услугами получится 248 598 300 ₽ вместо 178 542 300 ₽ по исходным покупкам.

WITH purchases AS (
  SELECT DISTINCT client_id, event_time, properties
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
)
SELECT count(*) AS purchases,
       count(*) FILTER (WHERE json_array_length(properties::json -> 'extras') = 0) AS empty_extras,
       count(*) FILTER (WHERE json_array_length(properties::json -> 'extras') > 0) AS with_extras,
       sum(json_array_length(properties::json -> 'extras')) AS extra_items
FROM purchases;

import json
import pandas as pd

e = pd.read_parquet('app_events.parquet', columns=['client_id', 'event_time', 'event_name', 'properties'])
e['event_time'] = pd.to_datetime(e['event_time'])
start, end = pd.Timestamp('2026-07-01'), pd.Timestamp('2026-07-04')
p = e.loc[e.event_name.eq('purchase') & e.event_time.ge(start) & e.event_time.lt(end),
          ['client_id', 'event_time', 'properties']].drop_duplicates().copy()
fields = pd.json_normalize(p.properties.map(json.loads))
p['extras'] = fields['extras'].to_list()
p['amount'] = fields['total_amount'].to_list()
lengths = p.extras.str.len()
items = p.explode('extras')
print(len(p), int(lengths.eq(0).sum()), int(lengths.sum()))
print(len(items), int(items.extras.isna().sum()))
print(int(items.loc[items.extras.notna(), 'amount'].sum()),
      int(p.loc[lengths.gt(0), 'amount'].sum()))

Разворот в PostgreSQL: `json_array_elements_text`

Функция получает JSON-массив и возвращает строку на каждый элемент. Алиас x(extra) даёт извлечённой текстовой колонке понятное имя. LATERAL делает ссылку на p.properties явной: функция справа вычисляется относительно текущей покупки слева. Считаем популярность допуслуг за три дня, не выдавая решения задачи курса, где сопоставляются оформления и покупки за весь период.

В этом срезе услуга выбора места встречается в 1 266 покупках, багаж — в 1 069, страховка — в 650, питание — в 420. Сумма 3 405 — число выбранных услуг, а не число покупок с услугами: одна покупка может попасть в несколько категорий. Для доли покупателей знаменатель должен быть 3 563, а не 3 405.

WITH purchases AS (
  SELECT DISTINCT client_id, event_time, properties
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
)
SELECT x.extra, count(*) AS purchases_with_extra
FROM purchases p
CROSS JOIN LATERAL json_array_elements_text(p.properties::json -> 'extras') AS x(extra)
GROUP BY x.extra ORDER BY purchases_with_extra DESC;

DuckDB: `json_each` вместо функции PostgreSQL

В учебном редакторе курса работает DuckDB. У него json_each(p.properties::json -> 'extras') AS x отдаёт колонку value типа JSON. Чтобы получить текст услуги, пишите x.value ->> '$'. Этот запрос на том же трёхдневном срезе возвращает те же четыре категории и числа. Запись PostgreSQL json_array_elements_text в редакторе DuckDB не выполняется; смешивать её с DuckDB-синтаксисом нельзя.

Есть и unnest для списков DuckDB, но это уже иной тип данных. У JSON-события список сначала извлекается из свойства; json_each показывает элементы непосредственно и сохраняет ключ индекса, если нужна исходная позиция. При переносе запроса в PostgreSQL меняется функция и способ чтения значения, но не определение покупки и не дедупликация.

WITH purchases AS (
  SELECT DISTINCT client_id, event_time, properties
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
)
SELECT x.value ->> '$' AS extra, count(*) AS purchases_with_extra
FROM purchases p, json_each(p.properties::json -> 'extras') AS x
GROUP BY x.value ->> '$' ORDER BY purchases_with_extra DESC;

Ловушка 1: CROSS-разворот выбрасывает покупки без услуг

Неправильно считать count(DISTINCT book_ref) после CROSS JOIN LATERAL json_array_elements_text(...extras) числом всех покупок. Из 3 563 покупок останутся только 2 413 с непустым массивом; 1 150 покупок исчезнут. При этом строк после разворота будет 3 405 — ещё одно число, которое легко ошибочно принять за размер исходного набора.

Исправление — либо сохранить исходную таблицу покупок отдельным CTE для знаменателя, либо использовать LEFT JOIN LATERAL ... ON true, если нужны все покупки в одной таблице. После LEFT-разворота будет 4 555 строк: 3 405 услуг плюс по одной пустой строке на каждую из 1 150 покупок. count(x.extra) и count(*) здесь отвечают на разные вопросы.

WITH purchases AS (
  SELECT DISTINCT client_id, event_time, properties
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
)
SELECT count(*) AS rows_after_join,
       count(x.extra) AS extra_rows,
       count(*) FILTER (WHERE x.extra IS NULL) AS empty_rows
FROM purchases p
LEFT JOIN LATERAL json_array_elements_text(p.properties::json -> 'extras') AS x(extra) ON true;

Ловушка 2: сумма покупки повторяется на каждой услуге

Неправильно после разворота суммировать total_amount как будто каждая строка — новая покупка. На 2 413 покупках с услугами настоящая сумма — 178 542 300 ₽. После CROSS-разворота sum((properties::json ->> 'total_amount')::integer) вернёт 248 598 300 ₽: сумма каждой покупки повторилась столько раз, сколько у неё услуг.

Исправление — суммировать стоимость в CTE purchases до разворота; популярность услуг считать отдельно, а при необходимости соединять агрегаты по номеру брони. SUM(DISTINCT amount) не исправит дело: две разные покупки могут иметь одинаковую сумму. Сначала восстановите идентичность покупки, затем выполняйте денежный расчёт.

Ловушка 3: текст сравнивают как число

Неправильно написать (properties::json ->> 'total_amount') > '100000' и считать, что это «покупки дороже 100 тысяч». Оператор ->> вернул текст, поэтому сравнение лексикографическое. За 1–3 июля оно выберет 3 558 покупок, а числовое (properties::json ->> 'total_amount')::integer > 100000 — только 852.

Исправление — явно привести тип после извлечения, проверить невозможные и пустые значения до cast. Текстовое сравнение допускает обратный порядок: строка 9000 лексикографически больше 100000, хотя численно меньше. Нельзя исправить это форматированием вывода — ошибка возникает при фильтрации.

Ловушка 4: приоритет `->>` в DuckDB

Неправильно в DuckDB написать t.book_ref = p.properties::json ->> 'book_ref'. Парсер связывает = раньше извлечения JSON и пытается интерпретировать булево выражение как JSON; на данных это заканчивается ошибкой связывания, а не пустым набором. В PostgreSQL тот же текст может разбираться иначе, поэтому перенос без проверки опасен.

Исправление для обоих движков — скобки вокруг извлечения: t.book_ref = (p.properties::json ->> 'book_ref'). На трёхдневном срезе 5 026 элементов tickets после разворота находят 5 026 номеров в таблице tickets. Такая сверка полезнее проверки по двум случайным строкам.

Ловушка 5: пустой массив, пропущенный ключ и JSON null

Неправильно писать coalesce(json_array_length(...), 0) сразу после чтения и объявлять все нули «клиент ничего не выбрал». Для [] это действительно ноль; для отсутствующего ключа NULL говорит, что поле не пришло; для значения JSON null требуется отдельно проверить семантику функции и контракта события. На данном срезе 1 150 пустых extras и ноль отсутствующих ключей; это не гарантия для будущего журнала.

Исправление — вывести три состояния отдельными счётчиками качества перед coalesce. Если источник изменит схему, число «покупок без услуг» не должно внезапно включить события, где услуги вообще не были измерены. В синтетическом примере {"extras":[]}, {} и {"extras":null} выглядят похоже в отчёте с одним нулём, но несут разные причины.

Ловушка 6: два массива дают декартово произведение

Неправильно разворачивать tickets и extras в одном FROM и считать строки числом билетов. Если у одной покупки два билета и три услуги, получится шесть строк. В данных за три дня у 3 563 покупок 5 026 билетов и 3 405 услуг; двойной разворот выдаст 4 704 строки — не 5 026 билетов и не 3 405 услуг. У каждой покупки своё произведение длин, а покупки с пустыми extras пропадут.

Исправление — отдельные CTE на уровне билет × покупка и услуга × покупка, затем агрегация каждой ветки до номера брони. Лишь после этого соединяйте показатели. Если же нужны реальные пары «билет–допуслуга», сначала убедитесь, что JSON задаёт такую связь: два независимых массива сами по себе не указывают, какая услуга относится к какому билету.

Соединить развёрнутые билеты с таблицей билетов

Массив tickets содержит номера билетов строками. После разворота можно присоединить таблицу tickets по ticket_no, но сохраните book_ref из JSON и сравните его с tickets.book_ref отдельным инвариантом. Совпадение 5 026 из 5 026 номеров в выбранном срезе говорит, что элементы найдены; оно не говорит, что каждое событие относится к правильному клиенту без дополнительной проверки.

На полном наборе это уже сверка источников, а не просто синтаксис JSON. Для случая «номер есть в событии, но нет в таблице» нужен LEFT JOIN и фильтр t.ticket_no IS NULL после присоединения. Для проверки обеих сторон — FULL JOIN и три зоны расхождений.

WITH purchases AS (
  SELECT DISTINCT client_id, event_time, properties
  FROM app_events
  WHERE event_name = 'purchase'
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
), exploded AS (
  SELECT p.properties::json ->> 'book_ref' AS book_ref,
         x.ticket_no
  FROM purchases p
  CROSS JOIN LATERAL json_array_elements_text(p.properties::json -> 'tickets') AS x(ticket_no)
)
SELECT count(*) AS ticket_elements,
       count(t.ticket_no) AS found_in_tickets
FROM exploded e LEFT JOIN tickets t ON t.ticket_no = e.ticket_no;

В других СУБД

PostgreSQL 14 различает типы json и jsonb. Для массива в колонке json доступны json_array_elements_text и json_array_length; для jsonb — jsonb_array_elements_text и аналогичная длина. JSONB даёт индексируемые операторы поиска и существования, но переносить колонку VARCHAR в JSONB только ради этого примера нет причины.

DuckDB работает с json_each и JSON-путём '$' для извлечения текста элемента. Оба варианта выполнены на одинаковом срезе и возвращают 1 266/1 069/650/420 по четырём услугам. Для другой СУБД берите её табличную JSON-функцию из официальной документации и отдельно проверяйте, сохраняет ли outer-разворот пустой список. LATERAL JOIN объясняет общий механизм коррелированной табличной функции.

Что именно подтверждает сверка 5 026 билетов

Равенство 5 026 элементов массива и 5 026 найденных строк в таблице tickets — сильная техническая проверка, но не полная проверка бизнес-события. Нужно убедиться, что номер билета из массива относится к тому же book_ref, который записан в событии, и что у номера не несколько строк в tickets. Иначе count(t.ticket_no) может остаться 5 026 по случайной компенсации потерь и повторов. Для диагностики выводите отдельно число разных номеров, число элементов без пары и число элементов с несовпавшей бронью.

Проверьте время. Событие покупки и запись билета могли попасть в выгрузку с разной задержкой; на границе 3 июля одно из двух может оказаться уже после полуночи. Поэтому расхождение по последнему часу не равно автоматически ошибке трекера. В данных учебного среза оба источника уже доступны, но в живом потоке для такой сверки обычно вводят лаг зрелости: сравнивают закрытые дни, а текущий помечают предварительным.

Если нужно понять, в каких покупках есть хотя бы один билет, не разворачивайте массив ради простого предиката «массив непустой»: длина отвечает дешевле и сохраняет одну строку на покупку. Разворот нужен, когда интересен сам номер, позиция в массиве или связь элемента с другой таблицей. Этот выбор заранее удерживает строку на правильном зерне и уменьшает объём промежуточного набора.

Как держать контракт JSON проверяемым

Полезно записать контракт события рядом с запросом: book_ref — текстовый ключ, total_amount — сумма в рублях, tickets — список текстовых номеров, extras — список названий услуг. Отдельно указать, допускаются ли [], отсутствие ключа и JSON null. Без этого один аналитик будет считать пропущенный extras отсутствием покупки, другой — покупкой без услуг, а третий отбросит событие при CROSS-развороте. Их SQL окажется синтаксически верным, но результаты не сравнятся.

Проверки контракта проводят до GROUP BY: доля валидного JSON, тип каждого ожидаемого ключа, доля отсутствующих ключей, количество повторов доставки, число элементов и число разных элементов на покупку. Для нашего трёхдневного среза контрольные числа уже известны: 3 563 покупки, 1 150 пустых extras, 3 405 элементов extras и 5 026 элементов tickets. После изменения схемы любое отклонение — повод пересмотреть расчёт, а не молча поправить итоговую таблицу.

Если поле регулярно участвует в соединениях и фильтрах, вынесите извлечение в именованный слой purchases: там один раз приведите тип, отметьте ошибочные значения и задайте ключ. Не повторяйте properties::json ->> ... десятки раз в каждом отчёте с разными правилами пустоты. Но и не превращайте всё событие в набор неявных колонок без описания исходного JSON: для расследования понадобится оригинальное значение.

Частые вопросы

**Чем -> отличается от ->>?** Первый сохраняет JSON-значение, второй возвращает текст. Для длины и разворота нужен массив JSON, для сравнения номера брони — текст.

**Почему json_array_elements_text уменьшил число покупок?** CROSS-разворот возвращает ноль строк для []. Используйте отдельный знаменатель или LEFT JOIN LATERAL, если пустые покупки нужны в результате.

Как считать деньги после разворота? Не суммируйте сумму покупки на уровне элементов. Сначала агрегируйте на уровне покупки, потом присоединяйте статистику элементов.

**Как сравнить элемент JSON с tickets.ticket_no?** Разверните массив в строки, извлеките текстовый номер и соедините по нему. Сверьте не только число совпадений, но и номер брони.

Что делать с отсутствующим ключом? Считать его отдельно от пустого массива и выяснить, был ли он измерен. COALESCE используйте только после решения о семантике пропуска.