mysql5.7 json object - query elements of an array
you can use JSON_SEARCH function. This function returns the path to the given string in the json object, but you can use it also to retrieve results as it returns null if the element doesn't exist
In your case, running:
select JSON_SEARCH(json_data, 'all', '1', null, '$.array[*].value') from myTableWill return '$.array[0].value' for the first row, and null for the second.
So use that to do:
select * from myTable where JSON_SEARCH(json_data, 'all', '1', null, '$.array[*].value') is not nullTo get the first row only.