How to extract id from json in string in SQL?

Question

MySQL

There is the following line in json format in a column in the database:

{"name": "Metallica", "@type": "Event", "image": ["https://site.com/data/images/54837687508image.jpeg"], "offers": {"url": "https://site.com/event/36302", "@type": "Offer", "price": "0", "validFrom": "2024-05-21T12:00", "availability": "https://schema.org/InStock", "priceCurrency": "BRL"}, "endDate": "2022-05-12UTC00:00", "@context": "https://schema.org", "location": {"name": "Estádio Mineirão", "@type": "Place", "address": {"@type": "PostalAddress", "streetAddress": "", "addressCountry": "UA", "addressLocality": "Belo Horizonte"}}, "organizer": {"url": "https://site.com", "name": "site.com", "@type": "Organization"}, "performer": {"name": "Metallica", "@type": "PerformingGroup"}, "startDate": "2022-05-12UTC00:00", "description": "jueves, 12 de mayo de 2022 | Estádio Mineirão", "eventStatus": "https://schema.org/EventScheduled", "eventAttendanceMode": "https://schema.org/OfflineEventAttendanceMode"}

From this line, you need to extract the event id, which is hidden in the url: "http s:/ / site.com/event/36302".
There are more than 20k such lines, please help in creating the procedure.

Answer 1

select replace(data->'$.offers.url', 'https://site.com/event/', '') from test