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.

I get what you are trying to do .. But why? Wouldn't using a scripting language to extract the JSON data you need be far easier? It seems that you're trying to frame a house with a chainsaw as your only cutting tool. There are easier, more efficient tools out there .. — Zak
– Zak, Commented Mar 26, 2019 at 22:09
Sure I could do it that way and I am. But I thought since there was the new JSON column that there must be some way of querying this data given all the new JSON functions — user558720
– user558720, Commented Mar 28, 2019 at 16:11

GMB · Accepted Answer · 2019-03-26 22:14:32Z

4

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().

answered Mar 26, 2019 at 22:14

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

user558720 Over a year ago

That is perfect thank you. I must have missed $.trackStatus[*].value as an option. Every example I saw was something like $[*].trackStatus.value. In addition, you will notice that the value is set to 4 not "4", so for me it still returns null. I verified with your DB Fiddle. In doing a quick search I see this is as designed and is now a feature request, so it seems I am stuck with looping through myself. Thanks again!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related