jsonb_array_length в PostgreSQL: как посчитать элементы в JSONB-массиве и не уронить запрос

jsonb_array_length — это функция PostgreSQL, которая считает количество элементов в JSONB-массиве.

Например:

• сколько товаров лежит в корзине под ключом items;
• сколько тегов указано в профиле пользователя;
• сколько файлов прикреплено к документу;
• сколько вариантов ответа хранится в JSONB-поле.

На первый взгляд всё просто: достали массив из JSONB-колонки, передали его в jsonb_array_length, получили число.

Но есть важный подвох: функция работает только с JSONB-массивом.

Если вместо массива в данных окажется объект, строка, число или JSON-значение null, запрос может упасть с ошибкой:

cannot get array length of a non-array

И это неприятно: из-за одной кривой строки база не вернёт даже те строки, где всё заполнено правильно.

А в живых JSONB-данных такое бывает постоянно. Где-то ключа нет. Где-то раньше фронтенд отправлял массив, а потом начал отправлять объект. Где-то вместо пустого массива записали null. Где-то данные импортировали из внешней системы, и формат слегка поплыл.

Поэтому с jsonb_array_length важно не просто знать синтаксис, а писать запросы так, чтобы они спокойно переживали грязные данные.

Что делает jsonb_array_length

Функция jsonb_array_length возвращает длину JSONB-массива.

Например, есть JSONB-значение:

["book", "pen", "notebook"]

В нём три элемента. Значит, jsonb_array_length вернёт 3.

Если массив пустой:

[]

Функция вернёт 0.

Это нормальный результат. Пустой массив — это всё ещё массив, просто без элементов.

Допустим, у нас есть таблица заказов:

CREATE TABLE orders (
  id bigint,
  user_id bigint,
  amount numeric,
  data jsonb
);

В колонке data хранится JSONB-документ. Внутри него под ключом items лежит массив товаров:

{
  "items": [
    {"sku": "A-100", "qty": 2},
    {"sku": "B-200", "qty": 1}
  ],
  "source": "web"
}

Чтобы посчитать количество товаров в заказе, можно написать так:

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

Здесь важно использовать именно оператор ->.

Он достаёт значение как JSONB. А функция jsonb_array_length как раз ждёт JSONB-массив.

Оператор ->> здесь не подходит, потому что он достаёт значение как текст.

SELECT
  id,
  data->>'items' AS items_as_text
FROM orders;

Такой результат уже не будет JSONB-массивом для jsonb_array_length. Это будет текстовое представление значения.

Простое правило:

Для работы с JSONB-функциями обычно нужен ->, а не ->>.

Что считается элементом массива

jsonb_array_length считает элементы только на верхнем уровне массива.

Например:

[1, 2, 3]

Длина равна 3.

А здесь:

[1, [2, 3], 4]

Длина тоже равна 3, а не 4.

Почему? Потому что вложенный массив [2, 3] считается одним элементом верхнего массива.

То же самое с объектами:

[
  {"sku": "A-100"},
  {"sku": "B-200"}
]

Длина равна 2. Функция не заглядывает внутрь объектов и не считает их поля. Она считает только количество элементов в самом массиве.

Почему запрос может упасть

Главная особенность jsonb_array_length: аргумент обязан быть массивом.

Вот это нормально:

SELECT jsonb_array_length('[1, 2, 3]'::jsonb) AS result;

Результат:

result
3

Пустой массив тоже нормально:

SELECT jsonb_array_length('[]'::jsonb) AS result;

Результат:

result
0

А вот объект уже не подходит:

SELECT jsonb_array_length('{"a": 1}'::jsonb) AS result;

Такой запрос завершится ошибкой.

Строка тоже не подходит:

SELECT jsonb_array_length('"hello"'::jsonb) AS result;

Число не подходит:

SELECT jsonb_array_length('123'::jsonb) AS result;

JSON-значение null тоже не массив:

SELECT jsonb_array_length('null'::jsonb) AS result;

Именно поэтому запрос по реальной таблице может внезапно сломаться.

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

Если хотя бы в одной строке data->'items' окажется объектом, строкой, числом или JSON-значением null, весь запрос упадёт.

Отсутствующий ключ и JSON null — не одно и то же

Есть тонкий, но важный момент.

Если ключа items вообще нет, выражение вернёт SQL NULL.

SELECT data->'items'
FROM orders;

Если ключ отсутствует, результат будет SQL NULL.

А jsonb_array_length(NULL) вернёт SQL NULL, не ошибку.

То есть отсутствующий ключ сам по себе запрос не роняет.

Но если ключ есть и внутри него лежит JSON-значение null, это уже другое значение:

{
  "items": null
}

В этом случае data->'items' вернёт JSONB-значение null, а не SQL NULL.

Для jsonb_array_length это не массив, поэтому будет ошибка.

Разница выглядит тонко, но для отчётов она важна:

• ключа нет — неизвестно, было ли поле вообще;
• ключ есть и там null — поле явно заполнено пустым JSON-значением;
• ключ есть и там [] — поле заполнено пустым массивом.

Это три разные ситуации.

Как безопасно считать длину массива

Чтобы запрос не падал на плохих данных, сначала нужно проверить тип значения.

Для этого в PostgreSQL есть функция jsonb_typeof.

Она возвращает тип JSONB-значения:

• array;
• object;
• string;
• number;
• boolean;
• null.

Проверим, что под ключом items действительно лежит массив:

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

Теперь запрос не упадёт, даже если в части строк данные кривые.

Логика такая:

1. Если items — массив, считаем его длину.
2. Если это не массив, возвращаем 0.

Иногда вместо 0 лучше вернуть NULL, чтобы не смешивать «пустой массив» и «неправильные данные».

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

Разница важная.

Если вернуть 0, вы как будто говорите:

Элементов нет.

Если вернуть NULL, вы говорите:

Я не могу корректно посчитать длину, потому что значение не является массивом.

Для аналитики второй вариант часто честнее.

Когда использовать 0, а когда NULL

Допустим, в отчёте нужно показать количество товаров в заказе.

Если items отсутствует или имеет неправильный тип, можно поставить 0, чтобы интерфейс выглядел аккуратно.

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

Но для контроля качества данных лучше не прятать проблему.

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

Так вы сразу увидите, где массив, где объект, где строка, где JSON null, а где ключ вообще отсутствует.

Для учебного правила можно запомнить так:

0 подходит, когда не-массивы по бизнес-смыслу можно считать пустыми.
NULL подходит, когда не-массивы означают неизвестное или неправильное значение.

Как найти строки с неправильным типом

Перед тем как строить отчёт, полезно проверить качество JSONB-данных.

Например, найти все строки, где items есть, но это не массив:

SELECT
  id,
  data->'items' AS items_value,
  jsonb_typeof(data->'items') AS items_type
FROM orders
WHERE data ? 'items'
  AND jsonb_typeof(data->'items') <> 'array';

Оператор ? проверяет, есть ли ключ в JSONB-объекте.

Такой запрос помогает быстро найти мусорные значения:

{"items": null}

{"items": {"sku": "A-100"}}

{"items": "empty"}

{"items": 3}

Если вы делаете отчёт для бизнеса, такие строки лучше не игнорировать молча. Возможно, это ошибка приложения, импорта или старой версии API.

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

Классическая задача: найти заказы, где в корзине хотя бы три товара.

Наивный вариант:

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

Он сработает только если во всех строках items всегда массив.

В реальной жизни лучше писать безопаснее:

SELECT
  id,
  user_id,
  amount
FROM orders
WHERE CASE
  WHEN jsonb_typeof(data->'items') = 'array'
    THEN jsonb_array_length(data->'items') >= 3
  ELSE false
END;

Такой запрос не пытается считать длину у не-массива.

Он вернёт только те строки, где:

• items является массивом;
• длина массива больше или равна 3.

Можно было бы написать короче:

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

Так часто пишут на практике, и во многих случаях это работает. Но важно понимать нюанс: SQL не стоит воспринимать как обычный код с гарантированным вычислением слева направо. Планировщик может менять порядок вычислений выражений.

Поэтому если вы хотите максимально безопасную форму, особенно для учебного материала и нестабильных данных, используйте CASE.

Средняя длина массива

Можно считать не только длину в каждой строке, но и агрегаты.

Например, среднее количество товаров в заказе:

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

Здесь мы сначала оставляем только строки, где items — массив, а потом считаем среднюю длину.

Если хочется явно показать безопасную логику, можно использовать подзапрос:

SELECT
  avg(item_count) AS avg_items
FROM (
  SELECT
    CASE
      WHEN jsonb_typeof(data->'items') = 'array'
        THEN jsonb_array_length(data->'items')
      ELSE NULL
    END AS item_count
  FROM orders
) t;

Такой вариант хорошо читается:

1. Внутри считаем безопасную длину.
2. Снаружи считаем среднее.
3. avg игнорирует NULL, поэтому не-массивы не попадут в расчёт.

Если же по бизнес-логике не-массив нужно считать как пустой массив, можно вернуть 0.

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

Но это уже другая метрика. Среднее станет меньше, потому что плохие или отсутствующие значения будут считаться нулём.

Если массив лежит глубже

JSONB-документы часто бывают вложенными.

Например, в таблице пользователей есть колонка data, а теги лежат по пути profile.tags:

{
  "profile": {
    "tags": ["sql", "backend", "qa"]
  }
}

Тогда путь можно пройти несколькими операторами ->.

SELECT
  id,
  jsonb_array_length(data->'profile'->'tags') AS tag_count
FROM users;

Но безопаснее снова проверить конечный узел:

SELECT
  id,
  CASE
    WHEN jsonb_typeof(data->'profile'->'tags') = 'array'
      THEN jsonb_array_length(data->'profile'->'tags')
    ELSE NULL
  END AS tag_count
FROM users;

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

Не просто profile, а именно profile.tags.

Как найти пользователей без тегов

Допустим, нужно найти пользователей, у которых тегов ровно ноль.

То есть не «ключа нет», не «там null», не «там строка», а именно:

{
  "profile": {
    "tags": []
  }
}

Запрос:

SELECT
  id,
  email
FROM users
WHERE CASE
  WHEN jsonb_typeof(data->'profile'->'tags') = 'array'
    THEN jsonb_array_length(data->'profile'->'tags') = 0
  ELSE false
END;

Этот запрос вернёт только пользователей с настоящим пустым массивом.

Если же нужно считать отсутствующие теги как пустой список, логика будет другой:

SELECT
  id,
  email
FROM users
WHERE CASE
  WHEN jsonb_typeof(data->'profile'->'tags') = 'array'
    THEN jsonb_array_length(data->'profile'->'tags') = 0
  WHEN data->'profile'->'tags' IS NULL
    THEN true
  ELSE false
END;

Здесь мы уже говорим:

Если массива нет, считаем, что тегов тоже нет.

Это может быть правильно для интерфейса, но для проверки качества данных такой подход слишком мягкий.

jsonb_array_length не разворачивает массив

Иногда для работы с JSONB-массивами используют jsonb_array_elements.

Эта функция разворачивает элементы массива в строки.

Например:

SELECT
  o.id,
  item
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.data->'items') AS item;

Если в заказе три товара, после такого запроса получится три строки.

А jsonb_array_length ничего не разворачивает. Она просто возвращает число.

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

Для подсчёта длины это обычно проще и дешевле по смыслу: не нужно превращать каждый элемент массива в отдельную строку, если вам нужна только длина.

Сравните два подхода.

Посчитать длину массива напрямую:

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

Развернуть массив и посчитать строки:

SELECT
  o.id,
  COUNT(*) AS item_count
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.data->'items') AS item
WHERE jsonb_typeof(o.data->'items') = 'array'
GROUP BY o.id;

Второй вариант нужен, когда вы хотите работать с самими элементами: фильтровать товары, доставать sku, суммировать qty, проверять поля внутри объектов.

Если нужна только длина массива, обычно достаточно jsonb_array_length.

jsonb_array_length и json_array_length

В PostgreSQL есть два похожих типа:

• json;
• jsonb.

Для них есть две похожие функции:

SELECT json_array_length('[1, 2, 3]'::json) AS result;

SELECT jsonb_array_length('[1, 2, 3]'::jsonb) AS result;

Если колонка имеет тип jsonb, используйте jsonb_array_length.

Если колонка имеет тип json, используйте json_array_length.

В современных PostgreSQL-проектах чаще выбирают jsonb, потому что он удобнее для индексации и многих операций. Но старые таблицы могут использовать json, поэтому название функции всё же стоит проверять.

jsonb_array_length против cardinality

Не путайте JSONB-массив и обычный SQL-массив PostgreSQL.

Это разные типы данных.

JSONB-массив лежит внутри JSONB-документа:

{
  "items": ["book", "pen", "notebook"]
}

Обычный SQL-массив — это значение типа text[], int[] и так далее.

Например, колонка может быть такой:

CREATE TABLE employees (
  id bigint,
  name text,
  roles text[]
);

Для SQL-массивов используется не jsonb_array_length, а другие функции.

cardinality возвращает общее количество элементов в SQL-массиве:

SELECT
  id,
  cardinality(roles) AS role_count
FROM employees;

А array_length возвращает длину массива по конкретному измерению:

SELECT
  id,
  array_length(roles, 1) AS role_count
FROM employees;

Есть важная ловушка: для пустого SQL-массива array_length может вернуть NULL, а не 0.

SELECT
  cardinality(ARRAY[]::text[]) AS count_by_cardinality,
  array_length(ARRAY[]::text[], 1) AS count_by_array_length;

Для новичка проще запомнить так:

• для JSONB-массива используйте jsonb_array_length;
• для SQL-массива используйте cardinality;
• array_length нужен, когда важна длина конкретного измерения массива.

Когда лучше JSONB, а когда SQL-массив

Если данные приходят как гибкий документ, структура может меняться, часть полей может отсутствовать, а внутри лежит смесь разных вложенных значений — это территория jsonb.

Например:

{
  "items": [
    {"sku": "A-100", "qty": 2},
    {"sku": "B-200", "qty": 1}
  ],
  "delivery": {
    "city": "Berlin",
    "method": "courier"
  }
}

Здесь JSONB удобен: можно хранить сложный документ и доставать из него нужные части.

Но если у вас просто список однородных значений, с которыми вы постоянно работаете как с массивом, иногда лучше использовать настоящий SQL-массив.

Например:

CREATE TABLE employees (
  id bigint,
  roles text[]
);

Тогда запрос будет проще:

SELECT
  id,
  cardinality(roles) AS role_count
FROM employees;

У SQL-массива нет проблемы «в этой строке вместо массива внезапно объект». Тип колонки уже гарантирует, что это массив.

JSONB даёт гибкость, но за гибкость приходится платить проверками.

Как это выглядит в других СУБД

В других базах данных правила отличаются.

В MySQL для JSON используют JSON_LENGTH.

Пример:

SELECT JSON_LENGTH(data, '$.items') AS item_count
FROM orders;

Но поведение на объектах, скалярах и отсутствующих путях отличается от PostgreSQL, поэтому переносить запросы копипастой нельзя.

В ClickHouse для JSON может использоваться JSONLength.

SELECT JSONLength(data, 'items') AS item_count
FROM orders;

А если данные хранятся в нативном массиве ClickHouse, обычно используют length.

SELECT length(items) AS item_count
FROM orders;

Главная мысль: название функции — это только половина дела. Важно ещё понимать, что конкретная база делает с отсутствующим ключом, null, объектом и скаляром.

Практический шаблон для безопасного запроса

Если вы работаете с PostgreSQL и JSONB-данные могут быть неоднородными, используйте такой шаблон:

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

Для фильтрации:

SELECT
  id,
  user_id,
  amount
FROM orders
WHERE CASE
  WHEN jsonb_typeof(data->'items') = 'array'
    THEN jsonb_array_length(data->'items') >= 3
  ELSE false
END;

Для поиска грязных данных:

SELECT
  id,
  data->'items' AS items_value,
  jsonb_typeof(data->'items') AS items_type
FROM orders
WHERE data ? 'items'
  AND jsonb_typeof(data->'items') <> 'array';

Эти три запроса закрывают большую часть повседневных задач:

• безопасно посчитать длину;
• безопасно отфильтровать по длине;
• найти строки, где формат данных нарушен.

Главное

jsonb_array_length считает количество элементов в JSONB-массиве.

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

Функция работает только с массивами вида [...].

Пустой массив [] даёт 0.

Вложенный массив считается одним элементом верхнего массива.

Если ключ отсутствует, выражение data->'items' вернёт SQL NULL, и функция тоже вернёт NULL.

Если под ключом лежит объект, строка, число или JSON null, запрос может упасть с ошибкой.

Чтобы защититься, проверяйте тип через jsonb_typeof.

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

Для фильтрации по длине массива безопаснее использовать CASE, а не надеяться на порядок вычисления условий в WHERE.

SELECT
  id,
  user_id,
  amount
FROM orders
WHERE CASE
  WHEN jsonb_typeof(data->'items') = 'array'
    THEN jsonb_array_length(data->'items') >= 3
  ELSE false
END;

Не путайте JSONB-массивы с обычными SQL-массивами PostgreSQL. Для JSONB используйте jsonb_array_length, для SQL-массивов чаще подходит cardinality.

Хороший запрос к JSONB — это не только «достать значение». Это ещё и аккуратно проверить, что в данных действительно лежит то, что вы ожидаете.