Достаём значение: -> и ->>

Комендант пролистывает документы, которые ты разложил лесенкой, и откладывает их в сторону. Читать каждую посылку целиком ему некогда. Для сводки нужны отдельные значения: какие товары смотрели, по какой цене, из каких городов приходили покупатели.

Ты открываешь событие 4. 11 февраля покупатель 17 смотрит «Мягкую игрушку кот» в приложении версии 3.9.2. Цена в документе есть, "price": "1290", но записана она в кавычках. На следующее утро покупатель 42 смотрит книгу «SQL для профи» из приложения 4.6.0, и в событии 6 та же сумма стоит уже без кавычек: "price": 1290.

Цена одна и та же: 1 290 ₽. Записана по-разному. Для PostgreSQL это две разные вещи.

КВЕРИ: Ради одной цены всю посылку разбирать не придётся. Найди нужную коробочку по подписи и достань только её.

Такой подписью здесь будет ключ. PostgreSQL умеет доставать значение по ключу двумя операторами-стрелками. Между ними всего один символ, но результат получается разного типа, а следом меняется и поведение сравнения.

Стрелка ->: достать значение по ключу

Возьмём событие 1, просмотр наушников. Его payload ты видел в прошлом уроке: ключи app, user, price, product_id, schema_version.

Запись payload -> 'price' читается буквально: возьми документ из payload, найди ключ price и верни значение под ним. Для события 1 получится 5990. А payload -> 'user' вернёт весь вложенный объект {"id": 23, "city": "Санкт-Петербург"}. Размер значения роли не играет: стрелка достаёт всё, что лежит под указанным ключом.

После одинарной стрелки результат остаётся типа jsonb. Представь, что мы вынули маленькую коробку из большой, но крышку пока не открыли. Если внутри была JSON-строка, она так и останется JSON-строкой с двойными кавычками.

Ключ пишется строкой в одинарных кавычках, как обычный текст в SQL: 'price'. Регистр учитывается, поэтому 'Price' и 'price' — разные ключи. Несуществующий ключ даст NULL без ошибки. С такими случаями отдельно поработаем в третьем уроке.

В ячейке ниже стрелка достаёт три значения у трёх событий, а последняя колонка показывает тип, который она вернула. Результат печатается так же, как его выводит PostgreSQL: у JSON-строк кавычки остаются. До запуска попробуй угадать: что будет в колонке app у трёх событий, с кавычками или без? А что увидишь в price?

Двойная стрелка ->>: достать текст

Оператор ->> тоже находит значение по ключу, но возвращает его обычным текстом, типом text. У JSON-строки исчезают кавычки: "4.6.0" становится 4.6.0. JSON-число 5990 превращается в текст из четырёх цифр. Даже вложенный объект станет текстом, который внешне похож на JSON, хотя для SQL это уже строка символов.

Вернёмся к двум посылкам из начала урока. В событии 4 цена лежит JSON-строкой "1290", в событии 6 JSON-числом 1290. С событием 4 всё уже можно предсказать. Одинарная стрелка вернёт jsonb со строкой внутри, поэтому в выводе останутся кавычки: "1290". Двойная стрелка их снимет, и получится текст 1290.

Теперь событие 6. Что вернёт каждая стрелка? Сравни колонки arrow и double_arrow у двух событий ещё до запуска.

Сравнение: jsonb сравнивается с jsonb

Комендант спрашивает: сколько раз смотрели товары по цене 1 290 ₽? В каталоге таких товаров два, книга «SQL для профи» и «Мягкая игрушка кот».

Рука тянется написать payload -> 'price' = '1290'. Запрос выполнится без ошибки, поэтому здесь особенно легко промахнуться. Слева стоит jsonb, и литерал справа PostgreSQL тоже разбирает как jsonb. Текст 1290 без кавычек превращается в JSON-число. Событие 6, где цена записана числом 1290, совпадёт. Событие 4 с JSON-строкой "1290" уже нет: как ты видел в прошлом уроке, строка и число с одинаковыми цифрами для jsonb различаются.

С двойной стрелкой сравниваются два текста. В обоих событиях слева получится 1290, справа тоже 1290, поэтому совпадут обе строки.

Ниже собраны все просмотры этих двух товаров и обе проверки. Событие 6 мы уже разобрали. До запуска прикинь результат для событий 4, 18 и 35: их прислало старое приложение 3.9.2. Какие значения будут в двух колонках сравнения?

Цепочка: коробка в коробке

Город покупателя в событии 1 лежит не на верхнем уровне, а внутри объекта user. Здесь стрелки ставятся одна за другой: payload -> 'user' ->> 'city'. Первая достаёт объект user, вторая берёт из него city и возвращает город текстом.

Читай такую запись слева направо как маршрут: документ, ключ user, ключ city. Все промежуточные стрелки оставляют результат в jsonb, чтобы по нему можно было идти дальше. Если написать payload ->> 'user' ->> 'city', первая двойная стрелка превратит объект в текст, после чего PostgreSQL ответит operator does not exist: text ->> unknown. Продолжить JSON-путь по обычному тексту нельзя.

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

Посмотрим три просмотра. Для события 1 путь до города даст Санкт-Петербург, а payload -> 'user' ->> 'id' вернёт номер покупателя, 23. Теперь события 6 и 8: какого типа получится колонка buyer? В самом документе номер покупателя записан числом, но последняя стрелка двойная.

Теперь тот же приём на ответах платёжного шлюза. Статус платежа лежит на верхнем уровне, почта покупателя — внутри customer, код отказа — внутри объекта error. В ячейке для платежей 1, 2 и 9 пока выведен только status. Допиши две цепочки, колонки email и error_code, и до запуска реши, что окажется в error_code у платежа 1, который прошёл без ошибки.

Номер элемента массива

У элементов массива нет ключей, зато есть номера. Если справа от одинарной стрелки поставить целое число, она возьмёт элемент с этим номером: payload -> 'items' -> 0. Отсчёт начинается с нуля, как во многих языках программирования: 0 — первый элемент, 1 — второй.

Возьмём заказ 5003, событие 7: в items лежат три книги. Прочитаем payload -> 'items' -> 0 ->> 'name' по шагам. Первая стрелка достаёт массив. Вторая берёт элемент номер 0, объект первой позиции. Третья вытаскивает название текстом. В результате увидим SQL для профи.

Можно считать и с другого конца. Отрицательный номер -1 означает последний элемент, -2 предпоследний. Так не приходится заранее узнавать длину массива, если нужна последняя позиция.

Выход за границы массива тоже не вызывает ошибку. Стрелка просто вернёт NULL, ровно как при обращении к отсутствующему ключу.

Перед запуском заполни четыре колонки в уме. Какая книга находится под номером 2? Что вернёт номер 3? И какое название получится через -1?

Попытки списания в ответе шлюза лежат в массиве charges. Обычно попытка одна, но у платежа 4 их две: первая закончилась таймаутом, вторая прошла. У платежа 11 массив пуст, потому что платёж ещё обрабатывается и списаний пока не было.

В ячейке уже выведен статус первой попытки, first_try. Добавь рядом last_try, статус последней. До запуска предскажи обе колонки для платежей 3, 4 и 11.

Из текста — в число

После двойной стрелки цена становится текстом. Если её нужно складывать, делить или сравнивать по величине, текст надо превратить в число: (payload ->> 'price')::numeric.

Скобки здесь нужны обязательно. Оператор :: связывает сильнее стрелки, поэтому запись payload ->> 'price'::numeric PostgreSQL прочитает совсем не так, как хотелось: сначала попытается превратить в число слово 'price' и упадёт с invalid input syntax for type numeric: "price".

Вернись к событиям 4 и 6. В первом цена хранится JSON-строкой, во втором JSON-числом. До запуска реши, будут ли значения в колонке price одинаковыми после приведения и какой тип покажет последняя колонка.

Теперь деньги из платёжного шлюза. Суммы там записаны в копейках: у платежа 1 это 797000. Чтобы получить рубли, значение приводят к числу и делят на 100. round(…, 2) оставит два знака после запятой, потому что деление numeric без округления даст длинный хвост нулей.

Посчитай до запуска, сколько рублей получится у платежей 1, 2 и 4. Рядом есть номер заказа: по нему можно сверить сумму с уже знакомыми данными заказа.

Ошибка cannot cast jsonb string to type numeric

Здесь часто пытаются срезать один шаг и привести к числу результат одинарной стрелки: (payload -> 'price')::numeric. Такое приведение jsonb поддерживается с PostgreSQL 11. На событии 6 оно действительно сработает, потому что под ключом находится JSON-число.

У события 4 там JSON-строка. jsonb можно напрямую привести к числу, только когда внутри уже лежит JSON-число. На строке PostgreSQL останавливается:

ERROR:  cannot cast jsonb string to type numeric

Текст ошибки здесь хорошо описывает проблему: внутри лежит строка. PostgreSQL не проверяет, похожи ли её символы на число. Через двойную стрелку путь другой: сначала получаем обычный текст 1290, а такой текст уже без проблем приводится к числу.

Проверь это на запросе. Коменданту нужна история просмотров «Мягкой игрушки кот», товара 18: дата и цена числом. Запрос ниже падает на первой же подходящей строке. Исправь его одной заменой.

Сортировка: почему 9 больше 800

Следующая справка коменданта касается бытовой техники. В карточках двух приборов указана мощность: кофеварка на 800 Вт и настольная лампа на 9 Вт. Их нужно поставить от более мощного к менее мощному.

Мощность хранится в attrs под ключом power_w. Двойная стрелка вернёт её текстом, а текст PostgreSQL сравнивает посимвольно слева направо. Для '800' и '9' сначала сравнятся 8 и 9. Девятка больше, на этом всё: оставшиеся два символа в 800 уже не участвуют. Поэтому лампа внезапно оказывается выше кофеварки.

На числах разной длины эта ошибка хорошо заметна: как текст '10' меньше '9', а '1000' меньше '12'. Если все значения одной длины, например 1290 и 5990, запрос некоторое время выглядит совершенно нормальным. Я бы такую сортировку без проверки на коротком и длинном числе в отчёт не выпускал.

Какой прибор окажется первым, если отсортировать по убыванию текст attrs ->> 'power_w'? Сначала ответь, потом запускай.

Перед сортировкой превратим текст в число: ORDER BY (attrs ->> 'power_w')::numeric DESC. Теперь PostgreSQL сравнивает 800 с 9 как числа, и кофеварка оказывается первой.

Та же ошибка встречается в WHERE. Условие attrs ->> 'power_w' > '100' сравнивает две строки. Для лампы оно истинно: в первом символе '9' уже больше '1' из '100'.

Ниже три проверки «мощнее 100 Вт» для тех же приборов. Вариант с одинарной стрелкой, колонку as_jsonb, можно разобрать заранее. attrs -> 'power_w' > '100' сравнивает jsonb с jsonb; оба значения в документах — JSON-числа, поэтому они сравниваются по величине. Кофеварка даст true, лампа false. Остальные две колонки попробуй заполнить сам.

Теперь без готового решения. Число деталей записано у трёх товаров: у набора посуды 12 предметов, у конструктора 120, у пазла 1000. Сначала расставь в уме эти три значения при сортировке текста по возрастанию. Запусти ячейку и проверь себя. Затем исправь ORDER BY прямо в редакторе, чтобы товары пошли по настоящему числу деталей, и запусти запрос ещё раз.

Финальная задача урока — уже без подсказки в основном тексте. Комендант собирает справку по игрушкам: с какого возраста их можно дарить, от самых младших к старшим. Запрос выполняется и даже начинает список правильно, с мягкой игрушки для самых маленьких. Но дальше порядок ломается: 0, 12, 3, 8. Пазл для двенадцатилетних почему-то встал вторым.

Частые ошибки

Сравнивать одинарную стрелку с числом

Строка: просмотры двух товаров по 1 290 ₽.

Ожидание: payload -> 'price' = '1290' найдёт все просмотры по этой цене.

На деле запрос находит только события, где цена записана числом. JSON-строка "1290" и JSON-число 1290 для PostgreSQL разные, поэтому три просмотра из четырёх тихо пропадают из результата.

Как проверить: если сравниваешь с текстом, бери ->>, а для расчётов приводи результат к числу. Если тип вызывает сомнение, выведи рядом jsonb_typeof(payload -> 'price').

Поставить двойную стрелку в середину цепочки

Строка: событие 1, payload ->> 'user' ->> 'city'.

Ожидание: город покупателя.

На деле получаем ошибку operator does not exist: text ->> unknown. Первая двойная стрелка уже превратила объект user в текст, а у текста продолжать JSON-путь некуда.

Как проверить: пока путь продолжается, используй одинарные стрелки; двойную ставь только на последнем шаге.

Забыть скобки вокруг стрелки

Строка: событие 6.

Ожидание: payload ->> 'price'::numeric вернёт цену числом, а payload -> 'price' + 1 прибавит к ней рубль.

На деле падают обе записи. :: и + связываются сильнее стрелок, поэтому PostgreSQL первым делом берётся не за результат извлечения. Ошибки разные, invalid input syntax for type numeric: "price" и invalid input syntax for type integer: "price", но причина одна: база пытается обработать само слово 'price'.

Как проверить: если после стрелки нужна ещё одна операция, сначала заключи результат извлечения в скобки: (payload ->> 'price')::numeric + 1.

Номер элемента в кавычках

Строка: заказ 5003, payload -> 'items' -> '0'.

Ожидание: первая позиция заказа.

На деле приходит NULL. '0' в кавычках — текст, поэтому PostgreSQL ищет ключ объекта с таким именем. У массива такого ключа нет, и база без ошибки возвращает пустое значение.

Как проверить: номер элемента пиши целым числом без кавычек: -> 0.

Сортировать числа текстом

Строка: кофеварка на 800 Вт и лампа на 9 Вт.

Ожидание: ORDER BY attrs ->> 'power_w' DESC поставит кофеварку первой.

На деле первой оказывается лампа: при текстовом сравнении первый символ 9 больше 8.

Как проверить: добавь в тестовую выборку числа разной длины. На четырёхзначных ценах ошибка легко прячется, а товар за 990 ₽ сразу её выдаст.

Перенос: как доставать значения из своих документов

Перед стрелкой полезно ответить на два вопроса. Что я буду делать с найденным значением дальше? Как оно записано в живом JSON?

Для текста в отчёте или сравнения со строкой обычно нужен ->>. Пока продолжаешь путь по документу или действительно хочешь сравнивать JSON-значения, оставляй ->. Для расчётов, числовой сортировки и сравнений с числами сначала достань текст и приведи его: (… ->> 'ключ')::numeric. С датами тот же приём работает через ::date или ::timestamp.

Фактический тип можно проверить через jsonb_typeof. Я бы делал это хотя бы один раз на настоящих данных, даже если документация API обещает число. В наших посылках старое приложение присылало цену строкой "1290", а новое числом 1290; одной схемы для такого вывода было бы мало.

Для быстрой проверки держи два неудобных примера: 9 рядом с 800 и "1290" рядом с 1290. Первая пара ловит текстовую сортировку чисел разной длины, вторая — смешение JSON-строки и JSON-числа. На них ошибка обычно видна сразу.