JSON_EXTRACT_PATH

7min
Overview
JSON_EXTRACT_PATH() function extracts JSON nested value from a specified path. 
Syntax
The syntax of the JSON_EXTRACT_PATH() function can be seen below. 
Syntax
JSON_EXTRACT_PATH(from_json JSON, path TEXT[])
JSON_EXTRACT_PATH(from_json JSON, path TEXT[])
﻿
from_json: the JSON value from which to extract.
path: the path to extract.
Another Option
Besides the syntax above, Oxla provides and supports the use of operators in queries.  See the syntax below:
Syntax
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
These examples display how JSON_EXTRACT_PATH() extracts the "oxla" JSON sub-object from the specified path. 
1) Use the below query:
Json extract path
SELECT JSON_EXTRACT_PATH('{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}', 'f4', 'f6');
SELECT JSON_EXTRACT_PATH('{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}', 'f4', 'f6');
﻿
or
Another option
SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}'::JSON -> 'f4' -> 'f6';
SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}'::JSON -> 'f4' -> 'f6';
﻿
The query above will return the following result.
Output
+---------+
| f       |
+---------+
| "oxla"  |
+---------+
+---------+
| f       |
+---------+
| "oxla"  |
+---------+
﻿
2) Run the query below:
Json extract path
SELECT
    JSON_EXTRACT_PATH('{"a": 1, "b": {"x": "subtract", "y": "plus"}}', 'b', 'x') AS "bx",
    JSON_EXTRACT_PATH('{"a": 1, "b": {"x": "multiply", "y": "divide"}}', 'b', 'y') AS "by";
SELECT
    JSON_EXTRACT_PATH('{"a": 1, "b": {"x": "subtract", "y": "plus"}}', 'b', 'x') AS "bx",
    JSON_EXTRACT_PATH('{"a": 1, "b": {"x": "multiply", "y": "divide"}}', 'b', 'y') AS "by";
﻿
You will get the following output:
Output
+---------------+-------------+
| bx            | by          |
+---------------+-------------+
| "subtract"    | "divide"    |
+---------------+-------------+
+---------------+-------------+
| bx            | by          |
+---------------+-------------+
| "subtract"    | "divide"    |
+---------------+-------------+
﻿
﻿
Updated 09 Oct 2023
Did this page help you?
JSON Functions
JSON_EXTRACT_PATH_TEXT
Docs powered by Archbee
TABLE OF CONTENTS