Свойства событий в JSON: операторы ->, ->> и разбор массивов

Бран · продюсер «Хроник Пограничья»

Разработчики говорят, что теперь пишут в события «все подробности»: телефон, откуда пришёл игрок, что показали в магазине. Мне нужно три ответа: на чём играют новички, откуда открывают магазин и покупают ли то, что мы показываем на витрине.

В guild.events появился столбец props. У каждого события в нём свой набор подробностей, и набор зависит от события: у установки устройство, у покупки цена, у открытия магазина список показанных офферов. Отдельный столбец под каждую подробность разработчики заводить не стали. Они пишут всё в один столбец типа jsonb.

Анкета внутри строки

Посмотри на одно событие каждого вида:

SELECT DISTINCT ON (name) name, props
FROM events
ORDER BY name, id;

 name                 | props
----------------------+--------------------------------------------------------------
 first_purchase       | {"currency": "RUB", "offer_id": 2, "price_rub": 499,
                      |  "app_version": "1.9.0"}
 first_quest_complete | {"app_version": "1.9.0"}
 install              | {"device": {"os": "ios", "model": "iPad Air"},
                      |  "utm_source": "organic", "app_version": "1.9.0"}
 store_open           | {"source": "main_menu", "app_version": "1.9.0",
                      |  "offers_shown": [1, 14, 18, 4]}
 tutorial_complete    | {"app_version": "1.9.0", "duration_sec": 1034}
 tutorial_start       | {"app_version": "1.9.0"}

Это анкета: слева название поля, справа ответ. Ответ бывает числом (499), строкой ("RUB"), вложенной анкетой (device) или списком (offers_shown). У всех событий есть версия игры app_version, остальное у каждого своё.

Две стрелки: -> и ->>

Достать поле из анкеты можно двумя стрелками:

SELECT props->'app_version'  AS as_json,
       props->>'app_version' AS as_text
FROM events
WHERE id = 1;

 as_json | as_text
---------+---------
 "1.9.0" | 1.9.0

Одинарная стрелка -> возвращает кусок JSON, тип jsonb, поэтому значение в кавычках. Двойная ->> возвращает текст. Запомнить помогает так: вторая угловая скобка «снимает обёртку».

Для вложенной анкеты стрелки идут цепочкой. Шагать внутрь надо через ->, потому что нужен ещё JSON, а последний шаг через ->>, потому что нужен текст:

SELECT props->'device'->>'os' AS os,
       props->'device'->>'model' AS model,
       count(*) AS installs
FROM events
WHERE name = 'install'
GROUP BY 1, 2
ORDER BY installs DESC;

Новички пришли с 2028 устройств Android, 1226 iOS и 838 ПК. Первая модель Windows, 483 установки, из телефонов Galaxy S23, 427. Ту же цепочку короче пишет оператор пути #>>: props #>> '{device,model}' идёт по ключам из массива и возвращает текст.

Число в JSON всё ещё текст

Средний чек первой покупки лежит в props->>'price_rub'. Двойная стрелка вернула текст, а текст не складывают. Приводим к числу, как в уроке про типы:

SELECT count(*) AS purchases,
       round(avg((props->>'price_rub')::numeric), 2) AS avg_price,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY (props->>'price_rub')::numeric) AS median_price
FROM events
WHERE name = 'first_purchase';

514 первых покупок, средняя 426,43 ₽, медиана 149 ₽: первым игроки чаще всего берут «Набор новичка».

Забыть приведение легко, и база не всегда напомнит ошибкой. max(props->>'price_rub') вернёт 999, хотя в данных есть покупки по 4990 ₽: текст сравнивается по буквам, и «9» больше «4». По той же причине ORDER BY props->>'price_rub' DESC поставит 99 ₽ выше 790 ₽.