Cloud

json_extract_path

json_extract_path() function extracts json nested value from a specified path.

Syntax

The syntax of the json_extract_path() function is:

JSON_EXTRACT_PATH(from_json JSON, path TEXT[])

  • from_json: The json value from which to extract.

  • path: The path to extract.

Another option

Redpanda SQL also provides and supports the use of operators in queries:

SELECT 'from_json'::JSON -> 'path';

  • from_json: The json value from which to extract.

  • ::JSON: A symbol that casts the text literal to a json type.

  • path: Key of the field to extract.

Examples

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

  1. Use the query:

    SELECT JSON_EXTRACT_PATH('{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}', 'f4', 'f6');

    or

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

    The query results:

    +---------+
| f       |
+---------+
| "oxla"  |
+---------+

  2. Run the query:

    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";

    The query returns:

    +---------------+-------------+
| bx            | by          |
+---------------+-------------+
| "subtract"    | "divide"    |
+---------------+-------------+