JSON EXTRACT Search Key use LIKE

Asked Dec 28 '20 at 11:04

Active Jan 13 '21 at 10:01

Viewed 116 times

So I got JSON and Choice column on MySQL (10.3.15-MariaDB-1:10.3.15+maria~bionic)

This is the JSON =

{"04:17":62,"05:16":31,"06:15":18,"07:14":12,"08:13":8,"09:12":7,"10:11":6}

And the choice is half of the key like 04 / 17 / 08 / 12 etc.

I already try this:

SELECT JSON_EXTRACT('{"04:17":62,"05:16":31,"06:15":18,"07:14":12,"08:13":8,"09:12":7,"10:11":6}','$.08:13');

return 8, it's work fine but the value I have only 13...

SELECT JSON_EXTRACT('{"04:17":62,"05:16":31,"06:15":18,"07:14":12,"08:13":8,"09:12":7,"10:11":6}','$.13');

then it will return null because key is not found.

Is there anyway to search key use like "LIKE"? already try $.%13% or $%.13% {"04:17":62,"05:16":31,"06:15":18,"07:14":12,"08:13":8,"09:12":7,"10:11":6} LIKE '%13%'

edited Jan 13 '21 at 10:01

marc_s

732,580
175
1,330
1,459

asked Dec 28 '20 at 11:04

Hasyidan Paramananda

What is precise MySQL version? – Akina Dec 28 '20 at 11:07
I use MySQL 10.3.15 – Hasyidan Paramananda Dec 28 '20 at 11:19
No such version. Query `SELECT @@version;` and post complete answer. – Akina Dec 28 '20 at 11:20
10.3.15-MariaDB-1:10.3.15+maria~bionic – Hasyidan Paramananda Dec 28 '20 at 11:31
In your DBMS version I see the only method. Use recursive CTE, parse your JSON to separate key-value pairs, test each pair against your condition, and return matched values. – Akina Dec 28 '20 at 11:34

JSON EXTRACT Search Key use LIKE

0 Answers0