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.
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
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加