Есть у меня одно неписаное правило: если задача по BI кажется лёгкой, считай — внизу айсберга затаилась подстава. Вот и в этот раз история началась со знакомого: «Подумаешь, парсить JSON в Datalens, да на полчаса работы!» Наивный оптимизм, который в BI-среде заканчивается всегда одинаково: поджидает матрёшка, внутри которой прячется не одна неожиданность.

Когда DataLens не хочет видеть твой JSON, а ты уже переписал сотню запросов

Классическое заблуждение: «Сейчас за 30 минут сделаю»

Появилась у меня свежая задачка: клиент прислал данные, и надо бы обработать эти строки, чтобы потом строить красивые витрины. Захожу в Datalens — бодро, с энтузиазмом, настраиваю датасет... И тут DataLens строго возвращает мне: — Неподдерживаемый тип данных. Мда.

По сценарию, я уже должен погрузиться в дебри, но пока ещё надеюсь на победу простым SELECT. Пробую через QL-чарт посмотреть содержимое — снова ошибка. В голове ещё вертится уверенность: «Ну не может быть, что задание на полчаса станет историей на весь вечер…»

Понятно, надо лезть в базу. Смотрю поле и там указан тип JSON. Значит, поле должно парситься обычными запросами, например функцией JSON_UNQUOTE или (CAST(... AS CHAR). Но есть нюанс.

Содержание очень похоже на валидный JSON, хотя лучше убедиться. Через View Data копирую содержание строку. Это означает, что в поле хранится не настоящий объект JSON, а строка с дополнительным экранированием — по сути, JSON внутри строки, то есть:

"{\"successStatus\":true,\"leadDetail\":{\"smsValidationDatetime\":null,\"smsValidated\":false,\"smokerDeclaration\":true,\"mobilePhone\":\"+79234707710\",\"mobileOptIn\":true,\"marketingOptIn\":true,\"marketCode\":\"KRU\",\"leadSource\":\"Affiliate\",\"leadId\":\"00QNM00000CvtJIQOQ\",\"lastName\":\"NA\",\"language\":null,\"gender\":null,\"firstName\":\"Misha\",\"email\":\"misha98@icloud.com\",\"databaseOptIn\":true,\"country\":\"Russia\",\"city\":\"Novokuznetsk\",\"birthdate\":null,\"ageVerification\":true,\"affiliateLeadSource\":\"paid_russia\",\"acceptPN\":true},\"errorMessage\":\"\"}"

а не «трушный»:

{"successStatus":true,"leadDetail":{"city":"Novokuznetsk", ...}}

Моя ситуация — классический пример «скрытого двойного экранирования». Решение — очистить структуру до настоящего объекта JSON перед аналитикой. В идеале можно было бы еще использовать HEX-дамп, чтобы точно получить «сырое содержимое», которое может проходить валидность через JSON_VALID.

Почистим json

Выглядит сложно, но надо всего лишь (ха-ха) очистить кавычки и слэши:

• внешние кавычки “...”,
• внутри — кавычки экранированы через \”.

Самое простое — это очистить экранирование и извлечь данные:

• Используем TRIM(BOTH ‘“’ FROM ...), чтобы срезать внешние кавычки
• Используем REPLACE(..., ‘\\\“’, ‘\”’), чтобы превратить все экранированные кавычки обратно в обычные кавычки.

И вот оно — почти победа! С двумя этими операциями «луковица» нашего JSON стала похожа на подходящий объект. В тот момент, когда у меня получилось из этих частей собрать валидный JSON, я почти поверил в чудеса.

Архитектурные ограничения MySQL+DataLens

Но DataLens не так прост: даже после парсинга полей могут возникнуть ограничения. Далеко не всегда можно сразу строить визуализации по ключам, пока не приведёшь столбцы к строчному типу или не изобретёшь промежуточный «велосипед» — то есть выгрузишь-импортируешь результат, или построишь вычисляемые столбцы руками. И финальное:

SELECT
  id,
  CAST(JSON_UNQUOTE(
    JSON_EXTRACT(
      REPLACE(TRIM(BOTH '"' FROM <название_поля>), '\\\"', '"'),
      '$.successStatus'
    )) AS CHAR(4)) AS status
FROM <название_таблицы>

В CHAR(n) задаем только длину строки, с $ задаем нужно поле.

Эту же задачу также можно было бы решить с помощью функции SUBSTRING:

SELECT
  id,
  SUBSTRING(
    JSON_UNQUOTE(
      JSON_EXTRACT(
        REPLACE(
          TRIM(BOTH '"' FROM <название_поля>),
          '\\\"', '"'
        ),
        '$.successStatus'
      )
    ),
    1, 50
  ) AS status
FROM <название_таблицы>

В MySQL функция SUBSTRING принимает три аргумента: строку, позицию начала и длину подстроки. Значения 1 и 50 в конструкции означают:

• 1 — начальная позиция в строке, с которой начинается извлечение (нумерация символов в MySQL начинается с 1, то есть первый символ строки),
• 50 — максимальное количество символов для извлечения.

То есть такой запрос вернёт первые 50 символов строки (или меньше, если длина строки меньше 50) начиная с первого символа.

Почему таких кейсов много? Агентство, темп, техдолг

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

• — Проекты стартуют максимально быстро, постоянно что-то «выгружается», мокапится, подгоняется «налету».
• — Между специалистами агентства и клиентом — минимум стыковки, иногда скорость важнее точности.
• — И каждый раз такие хаки, скрипты и обходные костыли — прямой плод технического долга: один не успел отладить процесс до конца, другому прилетело «разгрести это счастье», а BI-аналитику остаётся сочинять длинные SQL, чтобы хоть что-то зашевелилось.

Сейчас уже даже не удивляет, что подобные «микро-подвиги» кажутся чем-то исключительным — на самом деле у большинства агентских BI-команд их целый табун, просто болит за всё сразу не у каждого.

В сухом остатке

И вот так час, другой... А задача, обещавшаяся уложиться «в полчаса», оборачивается:

• разгадыванием загадок экранирования;
• неожиданными трюками SQL;
• и осознанием, что архитектурные ограничения платформы всё равно тебя догонят.

Парадокс — простая BI-задача снова оказалась спиралью: чем дальше, тем больше находок и лайфхаков для будущего себя (и такого же бодрого оптимиста из другой команды). Такие json возникают в микро-проектах

«Нечестный» JSON в данных встречается чаще, чем хочется, но «быстро разобрать» можно только после нескольких попыток. Это как c автоматизацией — кажется, экономишь пару минут, но тратишь вечер.