Cloud

json_array_extract

Overview

The json_array_extract() function returns the json array as a set of json values.

Syntax

The json_array_extract() has the following basic syntax.

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

json_array_extract() requires the following parameters:

  • json_array: The array to extract.

  • ::JSON: Argument indicating that the query is of type json.

  • id: ID of the element 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 following syntax:

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 to extract.

Examples

Basic json_array_extract() function

  1. The following example extracts 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"     |
+------------+

Extract element of JSON array as text

  1. This example extracts the element of the json array as text with the →> operator.

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

  2. Output:

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