How to Find a Specific Value within a JSON Array in MySQL?

MySQL: Search for Specific Key Value within an Array

In MySQL, you may encounter scenarios where you want to retrieve a specific value from an array of JSON objects. Using the JSON_SEARCH function, you can fetch the path to the desired node. However, combining this with the selection of a specific array element can be a challenge.

Solution: JSON_TABLE Function

MySQL 8.0 provides the JSON_TABLE function, which transforms a JSON document into a virtual derived table, akin to conventional rows and columns. This enables you to perform selection and projection operations on the JSON data.

Consider the following sample JSON array:

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

To find the strength of the knight, you can use the following query:

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

Limitations and Considerations

• The JSON_TABLE function requires explicit specification of attributes, which may not be practical if the attributes are unknown.
• Defining a view using JSON_TABLE can alleviate the repetitive nature of creating this derived table.
• Consider normalization for complex JSON data to simplify and optimize queries.

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