How to Efficiently Extract Specific Key Values from JSON Arrays in MySQL?

Finding Specific Key Values in MySQL JSON Array

In MySQL, finding a specific node within an array of JSON objects can be challenging. To address this, you may consider utilizing the JSON_SEARCH and JSON_CONTAINS functions. However, combining these functions to simplify the query is not always feasible.

MySQL 8.0 offers a solution with the JSON_TABLE() function, which creates a virtual derived table from a JSON document, effectively treating it as rows and columns. This enables you to perform operations like selection and projection on the JSON data.

To demonstrate, let's insert your example data into a table:

<code class="sql">create table mytable ( mycol json );

insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';</code>

You can now use JSON_TABLE() to create a virtual table:

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

This will produce the following result:

race	strength
Orc	14
Knight	7

To retrieve the strength of a specific race, you can use a WHERE clause:

<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 = 'Orc';</code>

This will return the following result:

strength

14

However, this approach requires you to specify the attribute fields in your query, which may not be suitable for datasets with unknown attribute definitions.

The above is the detailed content of How to Efficiently Extract Specific Key Values from JSON Arrays in MySQL?. For more information, please follow other related articles on the PHP Chinese website!