Cloud

json_extract_path_text

The json_extract_path_text() function extracts json nested value from a specified json value according to the defined path.

This function may be similar to the json_extract_path(). This function returns a value of type text instead of type json.

Syntax

The json_extract_path_text() syntax is as follows:

JSON_EXTRACT_PATH_TEXT(from_json JSON, path TEXT[])

The required arguments are:

  • from_json: The json value to extract.

  • path: The path to extract.

Another option

Redpanda SQL also provides and supports the use of operators in queries. Here’s the syntax:

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

  1. This example shows how to use the json_extract_path_text() function to extract values ​​from a json object at a specified index.

    Run the query:

    SELECT JSON_EXTRACT_PATH_TEXT('{"a": "Oxla", "b": {"x": 1.234, "y": 4.321}}', 'a') AS "result a";

    or

    SELECT '{"a": "Oxla", "b": {"x": 1.234, "y": 4.321}}'::JSON ->> 'a' AS "result a";

  2. The json_extract_path_text() function extracts the values and returns the output:

    +------------+
| result a   |
+------------+
| Oxla       |
+------------+