Использование оператора LIKE для поиска совпадений в строке типа json

Мне нужно найти те уведомления, у которых eventCode установлен на REFUND. Вот объект, хранящийся в БД:

{
    "target": "target",
    "method": "POST",
    "headers": {
        "x-forwarded-host": "x-forwarded-host",
        "x-real-ip": "x-real-ip",
        "x-forwarded-for": "x-forwarded-for",
        "x-forwarded-proto": "x-forwarded-proto",
        "host": "host",
        "connection": "connection",
        "content-length": "content-length",
        "cf-ipcountry": "cf-ipcountry",
        "accept-encoding": "accept-encoding",
        "cf-ray": "cf-ray",
        "cdn-loop": "cdn-loop",
        "cf-connecting-ip": "cf-connecting-ip",
        "cf-visitor": "cf-visitor",
        "traceparent": "traceparent",
        "content-type": "application/json; charset=utf-8",
        "user-agent": "user-agent"
    },
    "body": "{\"live\":\"true\",\"notificationItems\":[{\"NotificationRequestItem\":{\"additionalData\":{\"hmacSignature\":\"yadayadayada\",\"bookingDate\":\"2023-01-10T11:54:37Z\"},\"amount\":{\"currency\":\"EUR\",\"value\":2500},\"eventCode\":\"REFUND\",\"eventDate\":\"2023-01-10T11:53:43+02:00\",\"merchantAccountCode\":\"yadayada\",\"merchantReference\":\"yadayada\",\"originalReference\":\"yadayada\",\"paymentMethod\":\"mc\",\"pspReference\":\"yadayada\",\"reason\":\"\",\"success\":\"true\"}}]}"
}

Я использую терминал zsh и пытаюсь написать запрос, который будет анализировать эту строку тела:

select * from notifications where order_id=123456 and json_extract(data, '$.body') like '%\"eventCode\":\"REFUND\"%' \G

Это не работает. Методом проб и ошибок я выяснил, что это работает:

select * from notifications where id=123456 and json_extract(data, '$.body') like '%\"REFUND%' \G

но это не так:

select * from notifications where id=123456 and json_extract(data, '$.body') like '%:\"REFUND%' \G

Похоже, точка с запятой каким-то образом нарушает это.

Что я делаю не так?

ОБНОВЛЯТЬ:

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

SELECT concat(date(o.timestamp), ' ', extract(hour FROM o.timestamp), 'h') AS date,
       time_to_sec((timediff(n.timestamp, o.timestamp))) / 60 AS 'timedelay (minutes)',
       o.id AS order_id,
       c.user_id AS user_id
FROM notifications n
         JOIN orders AS o ON n.order_id = o.id
         JOIN credentials AS c ON o.credential_id = c.id
         JOIN merchants AS m ON c.merchant_id = m.id
         JOIN providers AS p ON m.provider_id = p.id
         CROSS JOIN JSON_TABLE(
            JSON_UNQUOTE(JSON_EXTRACT(n.data, '$.body')),
            '$.notificationItems[*]' COLUMNS (
            eventCode VARCHAR(20) PATH '$.NotificationRequestItem.eventCode'
        )) AS j
WHERE p.name = 'adyen'
  AND o.timestamp > '2024-04-11'
  AND time_to_sec((timediff(n.timestamp, o.timestamp))) / 60 > 60
  AND o.type = 'payment'
  AND j.eventCode = 'AUTHORISATION';

За исключением того, что теперь он имеет дело с другим типом событий. Этот запрос выдает ошибку ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.

Одна важная деталь здесь заключается в том, что в таблицах notifications, credentials, merchants есть поле с именем data. Похоже, что запрос путается, несмотря на использование псевдонимов и отсутствие веской причины для неоднозначности.

Еще одно наблюдение:

Этот запрос

SELECT n.id FROM notifications AS n
JOIN orders o ON n.order_id = o.id
WHERE n.provider_id = 1
AND o.type > 'payment'
AND JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(n.data,'$.body')), '$.notificationItems[0].NotificationRequestItem.eventCode') = 'AUTHORISATION'
LIMIT 5;

Ошибки с ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0..

Но этот запрос:

SELECT n.id FROM notifications AS n
WHERE n.order_id IN (
    SELECT o.id FROM orders AS o WHERE o.type = 'payment'
)
AND n.provider_id = 1
AND JSON_EXTRACT(
    JSON_UNQUOTE(JSON_EXTRACT(n.data,'$.body')),
    '$.notificationItems[0].NotificationRequestItem.eventCode'
) = 'AUTHORISATION'
LIMIT 5 \G

работает просто отлично.

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

Что касается версии:

mysql> SELECT Version();
+-----------+
| Version() |
+-----------+
| 8.0.35-27 |
+-----------+
1 row in set (0.00 sec)

покажите, что вы на самом деле делаете в zsh. или, альтернативно, выполните echo перед вашей командой и посмотрите, что происходит после выхода из оболочки, и отладьте эту часть самостоятельно, а затем покажите нам отраженный запрос, чтобы помочь с частью mysql.

ysth 10.04.2024 17:38

@ysth Я не уверен, что понял, что вы подразумеваете под «сделать эхо». После того, как я выполнил mysql -u qwerty -p и ввел 12345, я могу выполнять только команды SQL. Команды, которые я выполнил, показаны выше.

Alex 10.04.2024 17:44

вы, наверное, хотите что-то вроде json_search(json_unquote(json_extract(data,'$.body')), ...)

ysth 10.04.2024 17:45

ах, значит, вы не вводите это в zsh, вы вводите это в клиенте командной строки msyql. «использование терминала zsh» звучало так, как будто вы использовали zsh для отправки команды клиенту mysql. не беспокойся в таком случае.

ysth 10.04.2024 17:47

@ysth ааа, да, извини, эта лишняя информация действительно сбивает с толку...

Alex 10.04.2024 17:48

Вы указали на проблему с PingCap TiDB. Вы используете TiDB? Вы отметили свой вопрос MySQL.

Bill Karwin 12.04.2024 16:36

@BillKarwin ой, да, есть еще одна статья... Я дал неправильную ссылку, извините, в любом случае я использую MySQL.

Alex 12.04.2024 16:50
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
7
74
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я предполагаю, что ваш массив notificationItems может содержать более одного элемента, и вам нужно искать значение REFUND в любом элементе массива.

Если да, вам нужно использовать JSON_TABLE():

SELECT n.id, j.eventCode
FROM notifications AS n
CROSS JOIN JSON_TABLE(
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.body')),
  '$.notificationItems[*]' COLUMNS (
    eventCode VARCHAR(20) PATH '$.NotificationRequestItem.eventCode'
  )) AS j
WHERE n.id=123456 AND j.eventCode = 'REFUND';

Я включил это в свой запрос, но почему-то получаю следующую ошибку: ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.. Я проверил достоверность данных с помощью JSON_VALID, и данные кажутся в порядке, я перепробовал многое, но все равно получаю эту ошибку... мне не ясно, что нарушает запрос...

Alex 11.04.2024 13:51

В каждой строке вашей таблицы или только в определенной строке? Что содержится в столбце data в этой строке? Кроме того, можете ли вы запустить SELECT VERSION(); и сказать мне, что именно он возвращает?

Bill Karwin 11.04.2024 18:58

Сделанный. Я отредактировал свое обновление в исходном вопросе и более подробно объяснил проблему, с которой столкнулся сейчас. Похоже, здесь имеет место некоторая неясность. Не могли бы вы взглянуть?

Alex 12.04.2024 15:43

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

Alex 12.04.2024 15:54

Не имею представления. По моему опыту, MySQL не случайно забывает, на какой столбец ссылаться, даже если вы используете квалифицированные идентификаторы. Я думаю, если бы это была такая ошибка в MySQL, это сразу стало бы большой новостью. Более вероятно, что ваши данные искажены.

Bill Karwin 12.04.2024 16:53

Я тоже имел в виду эту идею, мои первые мысли: «некоторые данные искажены», да, чтобы исключить этот сценарий, я запустил проверку достоверности запроса с помощью JSON_VALID. Данные выглядят достоверными. Как бы тогда проверить валидность? Я также попробовал добавить объединения к этому вашему запросу, и это все равно сработало.

Alex 12.04.2024 17:13

Я сделал это несколькими способами, один из которых — просто добавить AND JSON_VALID(n.data).

Alex 12.04.2024 17:14

также добавлено, чтобы проверить, является ли тело «чистым», с помощью AND JSON_VALID(JSON_UNQUOTE(JSON_EXTRACT(n.data, '$.body'))) и да, на этот раз ошибка не возникла, что означает, что вы правы. Затем я подробнее расследую, как эти данные повреждены, поскольку они получены из надежного источника и, предположительно, не должны быть повреждены при записи в БД.

Alex 12.04.2024 17:37

Разве вы не определили столбец data как тип данных JSON? Это приведет к тому, что все данные, хранящиеся в этом столбце, будут иметь действительный формат JSON. О, конечно нет — это JSON внутри JSON. Тип данных не может обеспечить этого.

Bill Karwin 12.04.2024 17:41

ну этот кодекс восходит к доисторическим временам и существовал задолго до меня, это конечно не было сделано...

Alex 12.04.2024 17:54

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