How to query MySQL JSON column that contains an array of objects

Question

I have a MySQL (v5.7) database that has a table called deals which contains a JSON column called status.

Status contains data (in deals id = 29) in the following format (apologies for the formatting):

{
    "trackStatus":
    [
        {
            "date":[],
            "notes":[],
            "value":"0",
            "isChecked":false
        },
        {
            "date":["2019-03-25T17:42:45-04:00"],
            "notes":[],
            "value":4,
            "isChecked":true
         }
    ],
    "nextStatusIndex":0, 
    "currentDealStatus":4
}

I am trying to query the trackStatus array where value = 4to find out if itisChecked`.

My understanding from reading the MySQL reference that I need to use the JSON_EXTRACT function to get the data.

In following some of the examples, I tested the query as follows to see if I could return trackStatus:

SELECT JSON_EXTRACT(status, '$.trackStatus') 
FROM deals 
WHERE deals.id = 29

This works and returns trackStatus. However, when I try to expand on this to query within trackStatus specifically for isChecked, it does not return a value (not null but blank).

SELECT JSON_EXTRACT(status, '$.trackStatus', '$.isChecked') 
FROM deals 
WHERE deals.id = 29 AND JSON_EXTRACT(status, '$.trackStatus', '$.value') = 4

I have tried a myriad of different queries to the point where I am going in circles.

I realize the issue is with trackStatus because if I remove that array, I can query nextStatusIndex and it works. However with the array there, it does not.

So I am hoping someone can show me how/if I can query this JSON using MySQL queries (both within trackStatus and nextStatusIndex) given the way the data is formatted.

Answer 1

In MySQL 8.0, it would have been possble to turn the inner JSON array to a recordset with function JSON_TABLE(), and then inspect it.

Here is a solution for version 5.7, which relies on JSON_SEARCH() and JSON_EXTRACT().

This query will give you the value of attribute isChecked for the (first) element in the trackStatus array that has attribute value set to 4:

SELECT JSON_EXTRACT(
    status, 
    REPLACE(
        REPLACE(
            JSON_SEARCH(status, 'all', '4', '', '$.trackStatus[*].value'), 
        '"', ''), 
    '.value', '.isChecked')
) isCheckedAtValue4
FROM deals WHERE deals.id = 29;

With your sample data in this DB fiddle, this returns:

| isCheckedAtValue4 |
| ----------------- |
| true              |

---

Details

You can use JSON_SEARCH to find the path to the (first) element in array trackStatus that has attribute value set to 4:

SELECT JSON_SEARCH(status, 'one', '4', '', '$.trackStatus[*].value')
FROM deals 
WHERE deals.id = 29;

With your test data, this would typically return: "$.trackStatus[1].value".

We can manipulate the path string point it towards attribute isChecked of the same array element. Surrounding double quotes also need to be removed:

SELECT REPLACE(
    REPLACE(
        JSON_SEARCH(status, 'one', '4', '', '$.trackStatus[*].value'), 
    '"', ''), 
'.value', '.isChecked'),
FROM deals WHERE deals.id = 29;

This returns: $.trackStatus[1].isChecked.

Finally, this expression can be be given as an argument to JSON_EXTRACT().