JSON_ARRAY_EXTRACT

8min

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.
Syntax
JSON_ARRAY_EXTRACT('json_array'::JSON,id);
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:
Another option
SELECT 'from_json'::JSON -> path;
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.
Json array extract
SELECT JSON_ARRAY_EXTRACT('["Bougenvile", 2, 12, "Lily"]'::JSON,3);
SELECT JSON_ARRAY_EXTRACT('["Bougenvile", 2, 12, "Lily"]'::JSON,3);
﻿
or
Another option
SELECT ('["Bougenvile", 2, 12, "Lily"]'::JSON -> 3);
SELECT ('["Bougenvile", 2, 12, "Lily"]'::JSON -> 3);
﻿
2) The extracted array will look like the following.
Output
+------------+
| f          |
+------------+
| "Lily"     |
+------------+
+------------+
| 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.
Json array as text
SELECT ('["Bougenvile", 2, 12, "Lily"]'::JSON ->> 1);
SELECT ('["Bougenvile", 2, 12, "Lily"]'::JSON ->> 1);
﻿
2) You will get the final output as follows:
Output
+------------+
| f          |
+------------+
| 2.000000   |
+------------+
+------------+
| f          |
+------------+
| 2.000000   |
+------------+
﻿
﻿

Updated 09 Oct 2023

Did this page help you?

JSON_ARRAY_LENGTH

Other Functions

Docs powered by Archbee

TABLE OF CONTENTS

Case #1: Basic JSON_ARRAY_EXTRACT() function

Case #2: Extract element of JSON array as text