How to Efficiently Search for a Specific Value within a JSON Array in MySQL 8.0.15?

MySQL Search JSON Value by Key in Array

In MySQL 8.0.15, finding a specific value in an array of JSON objects can be challenging. However, the JSON_TABLE() function provides a solution by converting the JSON document into a virtual derived table.

Consider the following array:

<code class="json">[
    {"Race": "Orc", "strength": 14},
    {"Race": "Knight", "strength": 7},
]</code>

To retrieve the strength of the Knight, we can use:

<code class="sql">SELECT j.strength
FROM mytable,
JSON_TABLE(mycol, '$[*]' 
  COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j 
WHERE j.race = 'Knight'</code>

This converts the JSON array into a table with columns "race" and "strength," allowing us to apply a WHERE clause to find the desired value.

However, the downside of using JSON_TABLE() is the need to specify the desired attributes in the query, which may not be possible if the attributes are unknown. This highlights the importance of defining attributes when working with JSON data to simplify queries and avoid complex joins.

The above is the detailed content of How to Efficiently Search for a Specific Value within a JSON Array in MySQL 8.0.15?. For more information, please follow other related articles on the PHP Chinese website!