2

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

  • 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 Mar 12 at 0:57
  • 1
    "I have a MySQL database column that contains JSON array encoded strings" Why? – Lightness Races in Orbit Mar 12 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 Mar 12 at 17:42
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      |
  • GMB , thank you very much. It does indeed work. Can you explain what the "n.idx" does ? – JNicolls Mar 12 at 17:37
  • @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. – GMB Mar 12 at 22:33

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.