How can I query a value from a JSON array in MySQL?

In MySQL, you can use the JSON_EXTRACT function to query a value from a JSON array. This function takes two parameters: the first parameter is the JSON column to be queried, and the second parameter is the path to the value to be extracted.

For example, if we have a JSON column named data that contains an array {“fruits”: [“apple”, “banana”, “orange”]}, we can use the following query to extract the first value from the array.

SELECT JSON_EXTRACT(data, '$.fruits[0]') AS first_fruit FROM table_name;

This will yield results:

+-------------+
| first_fruit |
+-------------+
| "apple"     |
+-------------+

Please note that the indices of a JSON array start at 0. Therefore, $.fruits[0] means extracting the first element of the array.

To extract other elements from the array, simply modify the index in the path. For example, $.fruits[1] represents extracting the second element of the array, $.fruits[2] represents extracting the third element of the array, and so on.