jsonb_array_length in PostgreSQL: Counting JSON Array Elements Safely

jsonb_array_length в PostgreSQL делает ровно одно: возвращает число элементов в JSONB-массиве — длину корзины под ключом items, количество тегов в профиле, размер списка вложений в документе. Вызываете её, когда нужно посчитать или отфильтровать строки по длине массива, спрятанного внутри jsonb-колонки, не разворачивая его в строки через jsonb_array_elements.

Подвох в том, что функция строго требует на входе именно массив. Стоит в колонке вместо ожидаемого массива оказаться объекту, числу или JSON-у null — и она роняет весь запрос ошибкой cannot get array length of a non-array, не вернув даже корректные строки. А живые JSONB-данные почти никогда не бывают идеально однородными: где-то ключа нет, где-то под ним лежит скаляр, где-то — объект. Разберём, как считать длину так, чтобы запрос не падал, как фильтровать строки по размеру массива через jsonb_typeof и почему это совсем не то же самое, что cardinality над нативным SQL-массивом.

Базовый счёт элементов

Пусть у заказов есть колонка data jsonb, и под ключом items лежит массив позиций корзины. Стрелка data->'items' достаёт сам JSONB-массив — именно ->, а не ->>, которая вернула бы текст, — а его длину уже снимает jsonb_array_length:

SELECT id,
       jsonb_array_length(data->'items') AS item_count
FROM orders;

Несколько фактов, которые сэкономят вам пару часов отладки:

• Аргумент обязан быть именно JSONB-массивом ([...]), а не объектом и не скаляром.
• Считается только верхний уровень: вложенный массив идёт как один элемент.
• Пустой массив [] честно даёт 0 — это валидный ответ, а не сбой.
• Для типа json (без b) есть зеркальная json_array_length.

Ошибка non-array и как от неё прикрыться

Беда начинается, когда items есть не в каждой строке или хранит вовсе не массив. Хватит одной такой строки, чтобы весь запрос завалился целиком — не вернув даже корректные строки:

-- ERROR: cannot get array length of a non-array
SELECT jsonb_array_length(data->'items') FROM orders;

Причин ровно три, и каждая встречается в реальных данных:

• Ключа items нет вовсе — стрелка вернёт SQL NULL, и вот тут ошибки как раз не будет: функция получит NULL и тихо отдаст NULL.
• Под items спрятался объект {...} или строка — вот это и валит функцию.
• Значение — JSON null ('null'::jsonb), который массивом тоже не является.

Надёжное лекарство — проверить тип через jsonb_typeof до того, как звать длину. Чище всего завернуть проверку в CASE:

SELECT id,
       CASE
         WHEN jsonb_typeof(data->'items') = 'array'
           THEN jsonb_array_length(data->'items')
         ELSE 0
       END AS item_count
FROM orders;

Грабли: в PostgreSQL ни WHERE, ни SELECT не гарантируют порядок вычисления предикатов, поэтому конструкция WHERE jsonb_typeof(...) = 'array' AND jsonb_array_length(...) > 2 в теории может попытаться посчитать длину раньше, чем отработает проверка типа. На практике планировщик обычно ставит дешёвую проверку первой, и всё выглядит надёжно — ровно до того дня, когда смена плана вскроет мину. Не закладывайтесь на короткое замыкание AND там, где побочный эффект — это исключение: берите CASE или подзапрос, где порядок задан явно.

Фильтрация строк по размеру массива

Классическая задача — выловить заказы с «жирной» корзиной. Сначала отсекаем не-массивы, и только потом сравниваем длину:

SELECT id, user_id, amount
FROM orders
WHERE jsonb_typeof(data->'items') = 'array'
  AND jsonb_array_length(data->'items') >= 3;

Тот же приём работает и в агрегатах. Скажем, средняя длина корзины, посчитанная только по валидным заказам:

SELECT avg(jsonb_array_length(data->'items')) AS avg_items
FROM orders
WHERE jsonb_typeof(data->'items') = 'array';

Если массив зарыт глубже, путь просто наращивается стрелками — а защита по типу остаётся ровно та же, проверяется конечный узел:

SELECT u.id, u.email
FROM users AS u
WHERE jsonb_typeof(u.data->'profile'->'tags') = 'array'
  AND jsonb_array_length(u.data->'profile'->'tags') = 0;

Этот запрос вытаскивает пользователей, у которых тегов ровно ноль — но именно пустой массив, а не отсутствующий ключ. Разница тонкая, но в отчётах она решает.

jsonb_array_length против cardinality

Не путайте JSON-массив внутри документа с нативным массивом PostgreSQL вроде text[] или int[]. Это разные миры, и длину у них снимают разные функции:

• jsonb_array_length(x) — для JSONB-значения, которое является массивом.
• cardinality(x) — для SQL-массива; отдаёт общее число элементов.
• array_length(x, 1) — длина SQL-массива по первому измерению; на пустом массиве возвращает NULL, а не 0 (ещё одна ловушка для невнимательных).

-- SQL array stored in a real array column
SELECT cardinality(roles) AS role_count
FROM employees;          -- assumes roles text[]

-- JSON array stored inside a jsonb column
SELECT jsonb_array_length(data->'items') AS item_count
FROM orders;

Практический ориентир такой. Если данные прилетают как документ и форма плавает от строки к строке — это территория JSONB, считайте через jsonb_array_length под прикрытием jsonb_typeof. Если же массив однороден и вы фильтруете по нему постоянно — это явный кандидат на нативную колонку-массив с cardinality: она лаконичнее, индексируется иначе и в принципе не спотыкается на скалярах.

В других СУБД свои правила, и переносить запросы вслепую не стоит. В MySQL длину JSON-массива даёт JSON_LENGTH(data, '$.items'), и она куда покладистее: на не-массиве вернёт длину значения, а не исключение. В ClickHouse для JSON берут JSONLength(data, 'items') либо вообще работают с нативными Array(...) и функцией length(). Главное помнить: на «не-массиве» и на отсутствующем ключе каждый движок ведёт себя по-своему, так что переписывайте запрос осознанно, а не копипастом.