Мне нужно найти те уведомления, у которых 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)
@ysth Я не уверен, что понял, что вы подразумеваете под «сделать эхо». После того, как я выполнил mysql -u qwerty -p
и ввел 12345, я могу выполнять только команды SQL. Команды, которые я выполнил, показаны выше.
вы, наверное, хотите что-то вроде json_search(json_unquote(json_extract(data,'$.body')), ...)
ах, значит, вы не вводите это в zsh, вы вводите это в клиенте командной строки msyql. «использование терминала zsh» звучало так, как будто вы использовали zsh для отправки команды клиенту mysql. не беспокойся в таком случае.
@ysth ааа, да, извини, эта лишняя информация действительно сбивает с толку...
Вы указали на проблему с PingCap TiDB. Вы используете TiDB? Вы отметили свой вопрос MySQL.
@BillKarwin ой, да, есть еще одна статья... Я дал неправильную ссылку, извините, в любом случае я использую MySQL.
Я предполагаю, что ваш массив 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
, и данные кажутся в порядке, я перепробовал многое, но все равно получаю эту ошибку... мне не ясно, что нарушает запрос...
В каждой строке вашей таблицы или только в определенной строке? Что содержится в столбце data
в этой строке? Кроме того, можете ли вы запустить SELECT VERSION();
и сказать мне, что именно он возвращает?
Сделанный. Я отредактировал свое обновление в исходном вопросе и более подробно объяснил проблему, с которой столкнулся сейчас. Похоже, здесь имеет место некоторая неясность. Не могли бы вы взглянуть?
Это происходит через несколько минут. Например, код, который вы опубликовали выше, работает просто великолепно. Но со всеми этими соединениями происходит сбой с этой ошибкой после работы в течение нескольких минут... конечно, эти поля данных в других таблицах, о которых я упоминал выше, содержат другие данные. Но этой двусмысленности быть не должно...
Не имею представления. По моему опыту, MySQL не случайно забывает, на какой столбец ссылаться, даже если вы используете квалифицированные идентификаторы. Я думаю, если бы это была такая ошибка в MySQL, это сразу стало бы большой новостью. Более вероятно, что ваши данные искажены.
Я тоже имел в виду эту идею, мои первые мысли: «некоторые данные искажены», да, чтобы исключить этот сценарий, я запустил проверку достоверности запроса с помощью JSON_VALID
. Данные выглядят достоверными. Как бы тогда проверить валидность? Я также попробовал добавить объединения к этому вашему запросу, и это все равно сработало.
Я сделал это несколькими способами, один из которых — просто добавить AND JSON_VALID(n.data)
.
также добавлено, чтобы проверить, является ли тело «чистым», с помощью AND JSON_VALID(JSON_UNQUOTE(JSON_EXTRACT(n.data, '$.body')))
и да, на этот раз ошибка не возникла, что означает, что вы правы. Затем я подробнее расследую, как эти данные повреждены, поскольку они получены из надежного источника и, предположительно, не должны быть повреждены при записи в БД.
Разве вы не определили столбец data
как тип данных JSON
? Это приведет к тому, что все данные, хранящиеся в этом столбце, будут иметь действительный формат JSON. О, конечно нет — это JSON внутри JSON. Тип данных не может обеспечить этого.
ну этот кодекс восходит к доисторическим временам и существовал задолго до меня, это конечно не было сделано...
покажите, что вы на самом деле делаете в zsh. или, альтернативно, выполните echo перед вашей командой и посмотрите, что происходит после выхода из оболочки, и отладьте эту часть самостоятельно, а затем покажите нам отраженный запрос, чтобы помочь с частью mysql.