Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Using the LIKE operator to look for matches in a json-like string

I need to find those notifications that have the eventCode set to REFUND.
Here is the object stored in the DB:

{
    "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"}}]}"
}

I’m using a zsh terminal and trying to write a query that would parse that body string:

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

It does not work. By trial and error I found out that this works:

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

but this does not:

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

Looks like the semicolon breaks it somehow.

What am I doing wrong?

Tags:

2

Answers


  1. 0 
    ... 
WHERE JSON_EXTRACT(
          JSON_UNQUOTE(
              JSON_EXTRACT(value, 
                           '$.body'
                           )
                       ), 
                       '$.notificationItems[0].NotificationRequestItem.eventCode'
                   ) = '"REFUND"' 
...
    Login or Signup to reply.
  2. 0

    I assume your notificationItems array may have more than one element, and you need to search for your REFUND value in any element of the array.

    If so, you need to use 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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search