What is the method for retrieving values from a JSON array in SQL?

2 years ago
Olivia Parker
1 minute

To extract values from a JSON array in SQL, you can use the following method:

  1. You can utilize the JSON_EXTRACT function to retrieve specific values from a JSON array. The syntax is as follows:
SELECT JSON_EXTRACT(json_column, '$.key') as value
FROM table_name
WHERE condition;

In this case, json_column is the column name that contains a JSON array, key is the key name of the array element to be extracted, table_name is the table name, and condition is the filtering condition.

  1. By using the JSON_TABLE function, you can convert values in a JSON array into a table format and then query the desired values. The syntax is as follows:
SELECT jt.value
FROM table_name,
JSON_TABLE(json_column, '$[*]' COLUMNS (value PATH '$')) AS jt
WHERE condition;

Among them, json_column is the name of the column containing a JSON array, value is the name of the column from which to extract values, table_name is the name of the table, and condition is the filter condition.

Please choose the appropriate method to extract values from a JSON array based on the specific situation.

bannerAds