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

1. 'TAS00000012346'
2. 'TAS00000012347'

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

Answer 1

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      |