You will learn how to get a JSON array as JSON values with the JSON_ARRAY_EXTRACT() function.
The JSON_ARRAY_EXTRACT() function returns the JSON array as a set of JSON values.
The JSON_ARRAY_EXTRACT() has the basic syntax as seen below.
JSON_ARRAY_EXTRACT() requires the following parameters:
- json_array: the array to be extracted.
- ::JSON: argument indicating that the query is of type JSON.
- id: ID of the element that we want to extract. It is read in an array format that starts with 0.
JSON_ARRAY_EXTRACT can also be achieved with the -> operator, as shown in the syntax below:
- from_json: the JSON value from which to extract.
- ::JSON: a symbol that casts the string literal to a JSON type.
- path: key of the field that we want to extract.
Case #1: Basic JSON_ARRAY_EXTRACT() function
1) In the below example, we will extract a JSON array as a JSON set.
Json array extract
2) The extracted array will look like the following.
Case #2: Extract element of JSON array as text
1) In this case, we will extract the element of the JSON array as text with the ->> operator.
Json array as text
2) You will get the final output as follows:
Updated 11 May 2023
Did this page help you?