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.

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.


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);


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   |