21.1. Extracting a Value from JSON Data (json_extract, json_extract_string) #
The json_extract and json_extract_string stored procedures extract values located at the specified path from JSON data. The json_extract procedure returns the JSON data type, and the json_extract_string procedure returns the VARCHAR data type.
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute one of the following commands on the Postgres Pro AXE server:
SELECT json_extract(JSON,path) ON CONFLICT DO NOTHING;
SELECT json_extract_string(JSON,path) ON CONFLICT DO NOTHING;
Where:
JSON: The JSON data from which a value must be extracted.You must specify a JSON string literal or a column with JSON data.
path: The path to the value that must be extracted.You can use JSONPath (e.g.,
$.family) or JSON Pointer (e.g.,/family). A list of paths can also be specified, in which case a list of values is returned.
Postgres Pro AXE returns the extracted values.
Example 21.1. Executing the json_extract and json_extract_string Stored Procedures
JSON data in the j column of the example table:
{
"family": "anatidae",
"species": ["duck", "goose", "swan", null]
}
Extracting a field:
SELECT json_extract(j, '$.family') FROM example;
"anatidae"
SELECT json_extract_string(j, '$.family') FROM example;
anatidae
Extracting an array element:
SELECT json_extract(j, '$.species[0]') FROM example;
"duck"
SELECT json_extract_string(j, '$.species[0]') FROM example;
duck
The JSON data type uses 0-based indexing so $.species[0] returns the first element.