JSON_EXTRACT_PATH() function extracts JSON nested value from a specified path.


The syntax of the JSON_EXTRACT_PATH() function can be seen below.

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:

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.


These examples display how JSON_EXTRACT_PATH() extracts the “oxla” JSON sub-object from the specified path.

  1. Use the below query:
SELECT JSON_EXTRACT_PATH('{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}', 'f4', 'f6');


SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}'::JSON -> 'f4' -> 'f6';

The query above will return the following result.

| f       |
| "oxla"  |
  1. Run the query below:
    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:

| bx            | by          |
| "subtract"    | "divide"    |