​
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);
  1. 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);
  1. You will get the final output as follows:
+------------+
| f          |
+------------+
| 2.000000   |
+------------+