JSON_ARRAY_EXTRACT

You will learn how to get a JSON array as JSON values with the JSON_ARRAY_EXTRACT() function.

Overview

The JSON_ARRAY_EXTRACT() function returns the JSON array as a set of JSON values. 

Syntax

The JSON_ARRAY_EXTRACT() has the basic syntax as seen below.

JSON_ARRAY_EXTRACT('json_array'::JSON,id);

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.

Another Option

JSON_ARRAY_EXTRACT can also be achieved with the -> operator, as shown in the syntax below:

SELECT 'from_json'::JSON -> path;

• 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.

Examples

Case #1: Basic JSON_ARRAY_EXTRACT() function

1) In the below example, we will extract a JSON array as a JSON set.

SELECT JSON_ARRAY_EXTRACT('["Bougenvile", 2, 12, "Lily"]'::JSON,3);

or

SELECT ('["Bougenvile", 2, 12, "Lily"]'::JSON -> 3);

2) The extracted array will look like the following.

+------------+
| f          |
+------------+
| "Lily"     |
+------------+

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.

SELECT ('["Bougenvile", 2, 12, "Lily"]'::JSON ->> 1);

2) You will get the final output as follows:

+------------+
| f          |
+------------+
| 2.000000   |
+------------+