MySQL GENERATED VIRTUAL столбец: повторное использование кода части выражения

Исходные данные

У меня есть столбец JSON в MySQL, который содержит что-то вроде этого:

{
  "id": "2a5935557a034db8f030dfbeee4ecc67847f4503",
  "event": {
    "class": "event.http.page.production",
    "request": {
      "server": {
        "SERVER_ADDR": "142.11.8.97",
        "HTTP_REFERER": "https://www.google.es/"

        // many things

      }
    },

    // many more things

    "desiredResponse": {
      "cookies": [
        {
          "name": "hellotrip_a",
          "type": "persistent",
          "value": "1f4aee45bc488563ed589b98550eb3c35265262e",
          "validPeriodInSeconds": 173491200
        },
        {
          "name": "hellotrip_b",
          "type": "persistent",
          "value": "6afdbe1a2bad26ff179a9b37befc7edc1b946727",
          "validPeriodInSeconds": 1800
        },
        {
          "name": "hellotrip_c",
          "type": "session",
          "value": "c52cb48093c854a7a78dd86e75c345fe6ec7ac7a",
          "validPeriodInSeconds": 0
        }
      ]
    },
    "creationTimeStamp": "2017-09-11T18:12:44.761888Z"
  },
  "version": "v1.0.0"
}

Мы генерируем объект, похожий на этот, в каждом HTTP-запросе и храним все данные "многих вещей" для дальнейшего анализа.

Важная часть для извлечения

Ключевой частью этого вопроса является ключ "требуемый ответ".

  • Мы генерируем "как минимум" три куки, которые позволят нам сгруппировать все запросы, принадлежащие одному и тому же пользователю.
  • Я не могу гарантировать, что "hellotrip_a" всегда находится в позиции [0] массива; в любой момент может быть другое печенье.

Создание GENERATED VIRTUAL колонка

Я хочу создать GENERATED VIRTUAL столбец, который извлекает значение "hellotrip_a".

Для этого:

  1. Я ищу "путь" к "hellotrip_a" в пределах известного event.desiredResponse.cookies дорожка. Затем я получаю "путь к имени" куки.
  2. Я поднимаюсь на один уровень и затем спускаюсь на один уровень в поисках "ценности".

Создать табличное выражение

Я использую промежуточный столбец, подобный этому, который напоминает пункты 1 и 2, описанные выше:

`CookieANamePath`
    VARCHAR(255) AS
    (json_unquote(json_search(`FullEvent`,'one','hellotrip_a',NULL,'$.event.desiredResponse.cookies[*].name')))
    VIRTUAL,

`CookieAValue`
    VARCHAR(255) AS
     (json_unquote(json_extract(`FullEvent`,concat(left(`CookieANamePath`,(length(`CookieANamePath`) - 5)),'.value'))))
    VIRTUAL,

причина

Причина этого заключается в следующем:

В выражении concat( left( CookieANamePath, ( length( CookieANamePath ) - 5 ) ) бывает так:

  • Мне нужно получить "левую" часть выражения $.event.desiredResponse.cookies[0].name быть уменьшенным до $.event.desiredResponse.cookies[0], но так как я не знаю, будет ли индекс 10 или более, я не могу обрезать счет слева; Мне нужно сбрасывать со счетов персонажей справа.
  • Чтобы обрезать дисконтированные символы справа, мне нужен аргумент CookieANamePath два раза: один для функции LEFT() и один для функции LENGTH().

Проблемы

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

Вопросы

  • Как я могу повторно использовать часть выражения?
  • Интуитивно я бы использовал переменные. Могу ли я использовать переменные в операторе CREATE TABLE? А в ALTER TABLE?

Конечная нота:

Полный CREATE, который HeidiSQL говорит мне, что у меня в настоящее время есть, является этим:

CREATE TABLE `Logger_Events` (
    `ContextualizedEventId` CHAR(40) NOT NULL COLLATE 'ascii_bin',
    `EventId` CHAR(40) NOT NULL COLLATE 'ascii_bin',
    `UtcDateTime` DATETIME NOT NULL,
    `EventClass` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `FullEvent` JSON NOT NULL,
    `Class` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,'$.event.class'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `Method` VARCHAR(10) AS (json_unquote(json_extract(`FullEvent`,'$.event.request.server.REQUEST_METHOD'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `Host` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,'$.event.request.server.HTTP_HOST'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `Controller` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,'$.event.request.attributes._controller'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `Referrer` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,'$.event.request.server.HTTP_REFERER'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `LeadName` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,'$.event.eventData.lead_name'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `LeadEmail` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,'$.event.eventData.lead_email'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `CookieANamePath` VARCHAR(255) AS (json_unquote(json_search(`FullEvent`,'one','hellotrip_a',NULL,'$.event.desiredResponse.cookies[*].name'))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    `CookieAValue` VARCHAR(255) AS (json_unquote(json_extract(`FullEvent`,concat(left(`CookieANamePath`,(length(`CookieANamePath`) - 5)),'.value')))) VIRTUAL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`ContextualizedEventId`),
    INDEX `EventId` (`EventId`),
    INDEX `UtcDateTime` (`UtcDateTime`),
    INDEX `EventClass` (`EventClass`),
    INDEX `Method` (`Method`),
    INDEX `Host` (`Host`),
    INDEX `Class` (`Class`),
    INDEX `MethodHost` (`Method`, `Host`),
    INDEX `Controller` (`Controller`),
    INDEX `LeadEmail` (`LeadEmail`),
    INDEX `CookieAValue` (`CookieAValue`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Миссия: избавиться от колонны CookieANamePath избегая дублирования кода.

Thnkx!

Источник

0 ответов

Другие вопросы по тегам