MySQL - Search JSON data column

Question

I have a MySQL database column that contains JSON array encoded strings. I would like to search the JSON array where the "Elapsed" value is greater than a particular number and return the corresponding TaskID value of the object the value was found. I have been attempting to use combinations of the JSON_SEARCH, JSON_CONTAINS, and JSON_EXTRACT functions but I am not getting the desired results.

[
    {
        "TaskID": "TAS00000012344", 
        "Elapsed": "25"
    },
    {
        "TaskID": "TAS00000012345",
        "Elapsed": "30"
    },
    {
        "TaskID": "TAS00000012346",
        "Elapsed": "35"
    },
    {
        "TaskID": "TAS00000012347",
        "Elapsed": "40"
    }
]

Referencing the JSON above, if I search for "Elapsed" > "30" then 2 records would return

'TAS00000012346'
'TAS00000012347'

I am using MySQL version 5.7.11 and new to querying json data. Any help would be appreciated. thanks

Can you show your table structure? If I'm not mistaken you have to have your columns defined as actually JSON type or in order to use those functions you need at least to cast your varchar field to a json type, take a look here: dev.mysql.com/doc/refman/5.7/en/json.html#json-paths — Jorge Campos
– Jorge Campos, Commented Mar 12, 2019 at 0:57
"I have a MySQL database column that contains JSON array encoded strings" Why? — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Mar 12, 2019 at 1:21
The column that contains the JSON data is simply a 'text' datatype. The JSON functions do work regardless of the datatype. Probably not as well as they should/could though. — JNicolls
– JNicolls, Commented Mar 12, 2019 at 17:42

GMB · Accepted Answer · 2019-03-12 01:19:39Z

3

With MySQL pre-8.0, there is no easy way to turn a JSON array to a recordset (ie, function JSON_TABLE() is not yet available).

So, one way or another, we need to manually iterate through the array to extract the relevant pieces of data (using JSON_EXTRACT()). Here is a solution that uses an inline query to generate a list of numbers ; another classic approchach is to use a number tables.

Assuming a table called mytable with a column called js holding the JSON content:

SELECT 
    JSON_EXTRACT(js, CONCAT('$[', n.idx, '].TaskID')) TaskID,
    JSON_EXTRACT(js, CONCAT('$[', n.idx, '].Elapsed')) Elapsed
FROM mytable t
CROSS JOIN (
    SELECT 0 idx 
    UNION ALL SELECT 1 
    UNION ALL SELECT 2 
    UNION ALL SELECT 3
) n
WHERE JSON_EXTRACT(js, CONCAT('$[', n.idx, '].Elapsed')) * 1.0 > 30

NB: in the WHERE clause, the * 1.0 operation is there to force the conversion to a number.

Demo on DB Fiddle with your sample data:

| TaskID         | Elapsed |
| -------------- | ------- |
| TAS00000012346 | 35      |
| TAS00000012347 | 40      |

answered Mar 12, 2019 at 1:19

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JNicolls Over a year ago

GMB , thank you very much. It does indeed work. Can you explain what the "n.idx" does ?

GMB Over a year ago

@JNicolls: welcome! n is the alias for the subquery that produces the list of numbers, and idx is the column that contains the number itself. So n.idx produces 0, then 1, and so on.

vishwampandya · Accepted Answer · 2020-06-23 09:05:42Z

Yes , you can definitely to it using JSON_EXTRACT() function in mysql.

lets take a table that contains JSON (table client_services here) :

+-----+-----------+--------------------------------------+
| id  | client_id | service_values                       |
+-----+-----------+------------+-------------------------+
| 100 |      1000 | { "quota": 1,"data_transfer":160000} |
| 101 |      1000 | { "quota": 2,"data_transfer":800000} |
| 102 |      1000 | { "quota": 3,"data_transfer":70000}  |
| 103 |      1001 | { "quota": 1,"data_transfer":97000}  |
| 104 |      1001 | { "quota": 2,"data_transfer":1760}   |
| 105 |      1002 | { "quota": 2,"data_transfer":1060}   |
+-----+-----------+--------------------------------------+

And now lets say we want client_id for all who have quota>1 , then use this query :

SELECT 
    id,client_id,
    JSON_EXTRACT(service_values, '$.quota') AS quota
FROM client_services
WHERE JSON_EXTRACT(service_values, '$.quota') > 1;

And hence it will result into :

+-----+-----------+-------+
| id  | client_id | quota |
+-----+-----------+--------
| 101 |      1000 |     2 |
| 102 |      1000 |     3 |
| 104 |      1001 |     2 |
| 105 |      1002 |     2 |
+-----+-----------+-------+

hope this helps!

Collectives™ on Stack Overflow

MySQL - Search JSON data column

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related