How to get a json object with specific key values, from a json array column?

Question

In my MySQL 8.0 table, I have a JSON ARRAY column. It is an array of JSON objects. I want to pick one object out of each row's array, based on the key value pairs in the objects.

Example row:

[{bool:false, number:0, value:'hello'}, 
{bool:true, number:1, value:'world'},
{bool:true, number:2, value:'foo'},
{bool:false, number:1, value:'bar'}]

What I am trying to do is get the 'value' WHERE bool=true, AND number=1. So I want a query that in this example returns 'world'.

What would also work is if I could get the index of the object where bool=true and number=1, in this example it would return '$[1]'.

I am trying to run a query across the whole column, setting a new column to the value returned from the query. Is this possible with MySQL JSON functions? I've looked at the references but none have objects inside arrays like my example.

EDIT: If I do

SELECT JSON_SEARCH(column->"$[*]", 'all', '1');
SELECT JSON_SEARCH(names->"$[*]", 'all', 'true');

I get the paths/indexes of objects where number=1, and where bool=true, respectively. I would like the overlap of these two results.

Nick · Accepted Answer · 2019-01-18 23:23:47Z

1

You can use JSON_TABLE to convert the JSON into a derived table which you can then extract values from:

SELECT j.value
FROM test t
JOIN JSON_TABLE(t.jsonStr, 
        '$[*]'
        COLUMNS(bool BOOLEAN PATH '$.bool',
                number INT PATH '$.number',
                value VARCHAR(20) PATH '$.value')) j                    
WHERE j.bool = true AND j.number = 1

Output:

value
world

If you also want to get the index within each JSON value of the value which matched, you can add a FOR ORDINALITY clause to your JSON_TABLE e.g.:

SELECT j.idx, j.value
FROM test t
JOIN JSON_TABLE(t.jsonStr, 
        '$[*]'
        COLUMNS(idx FOR ORDINALITY,
                bool BOOLEAN PATH '$.bool',
                number INT PATH '$.number',
                value VARCHAR(20) PATH '$.value')) j
WHERE j.bool = true AND j.number = 1

Output:

idx     value
2       world

Demo on dbfiddle

edited Jan 18, 2019 at 23:23

answered Jan 18, 2019 at 20:20

Nick

147k23 gold badges67 silver badges106 bronze badges

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

3 Comments

Jordan Youngs Over a year ago

Thank you! This worked and your examples were very helpful

rdkrosan Over a year ago

THe above code runs successfully only on Mysql 8.0 though. What about greater than mysql 5.7 and less than mysql 8.0

Nick Over a year ago

@rdkrosan If you mean for versions 8.0.1 to 8.0.3 (which don't support JSON_TABLE), you would need to use the same solution as you would use for 5.7. If you need such a solution, you should ask a question.

Collectives™ on Stack Overflow

How to get a json object with specific key values, from a json array column?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related