JSON_EXTRACT_PATH_TEXT function

Note

JSON_PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.

Instead of using JSON_EXTRACT_PATH_TEXT, we recommend that you parse your JSON strings using the JSON_PARSE function to get a SUPER value. Then, query the element you want using the value.attribute syntax. For more information on querying array elements in SUPER values, go to Querying semistructured data.

The JSON_EXTRACT_PATH_TEXT function returns the value for the key-value pair referenced by a series of path elements in a JSON string. The JSON path can be nested up to five levels deep. Path elements are case-sensitive. If a path element does not exist in the JSON string, JSON_EXTRACT_PATH_TEXT returns NULL.

If the null_if_invalid argument is set to true and the JSON string is invalid, the function returns NULL instead of returning an error.

JSON_EXTRACT_PATH_TEXT has a 64KB data-size maximum. Thus, if any JSON record is larger than 64KB, processing it with JSON_EXTRACT_PATH_TEXT results in an error.

For information about additional JSON functions, see JSON functions. For more information about working with JSON, see COPY from JSON format.

Syntax

JSON_EXTRACT_PATH_TEXT('json_string', 'path_elem' [,'path_elem'[, …] ] [, null_if_invalid ] )

Arguments

json_string

A properly formatted JSON string.

path_elem

A path element in a JSON string. One path element is required. Additional path elements can be specified, up to five levels deep.

null_if_invalid

(Optional) A BOOLEAN value that specifies whether to return NULL if the input JSON string is invalid instead of returning an error. To return NULL if the JSON is invalid, specify true (t). To return an error if the JSON is invalid, specify false (f). The default is false.

In a JSON string, Amazon Redshift recognizes \n as a newline character and \t as a tab character. To load a backslash, escape it with a backslash (\\). For more information, see Escape characters in JSON.

Return type

VARCHAR

A VARCHAR string representing the JSON value referenced by the path elements.

Examples

To return the value for the path 'f4', 'f6', use the following example.

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

+------------------------+
| json_extract_path_text |
+------------------------+
| star                   |
+------------------------+

To return an error because the JSON is invalid, use the following example.

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

ERROR: invalid json object {"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}

To set null_if_invalid to true, so the statement returns NULL for invalid JSON instead of returning an error, use the following example.

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}','f4', 'f6',true);

+------------------------+
| json_extract_path_text |
+------------------------+
| NULL                   |
+------------------------+

To return the value for the path 'farm', 'barn', 'color', where the value retrieved is at the third level, use the following example. This sample is formatted with a JSON lint tool, to make it easier to read.

SELECT JSON_EXTRACT_PATH_TEXT('{
    "farm": {
        "barn": {
            "color": "red",
            "feed stocked": true
        }
    }
}', 'farm', 'barn', 'color');
+------------------------+
| json_extract_path_text |
+------------------------+
| red                    |
+------------------------+

To return NULL because the 'color' element is missing, use the following example. This sample is formatted with a JSON lint tool.

SELECT JSON_EXTRACT_PATH_TEXT('{
    "farm": {
        "barn": {}
    }
}', 'farm', 'barn', 'color');

+------------------------+
| json_extract_path_text |
+------------------------+
| NULL                   |
+------------------------+

If the JSON is valid, trying to extract an element that's missing returns NULL.

To return the value for the path 'house', 'appliances', 'washing machine', 'brand', use the following example.

SELECT JSON_EXTRACT_PATH_TEXT('{
  "house": {
    "address": {
      "street": "123 Any St.",
      "city": "Any Town",
      "state": "FL",
      "zip": "32830"
    },
    "bathroom": {
      "color": "green",
      "shower": true
    },
    "appliances": {
      "washing machine": {
        "brand": "Any Brand",
        "color": "beige"
      },
      "dryer": {
        "brand": "Any Brand",
        "color": "white"
      }
    }
  }
}', 'house', 'appliances', 'washing machine', 'brand');  

+------------------------+
| json_extract_path_text |
+------------------------+
| Any Brand              |
+------------------------+

The following example creates a sample table and populates it with SUPER values, then returns the value for the path 'f2' for both rows.

CREATE TABLE json_example(id INT, json_text SUPER);

INSERT INTO json_example VALUES
(1, JSON_PARSE('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}')),
(2, JSON_PARSE('{
    "farm": {
        "barn": {
            "color": "red",
            "feed stocked": true
        }
    }
}'));

SELECT * FROM json_example;
id          | json_text
------------+--------------------------------------------
1           | {"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}
2           | {"farm":{"barn":{"color":"red","feed stocked":true}}}
 

SELECT id, JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json_text), 'f2') FROM json_example;
         
id          | json_text
------------+--------------------------------------------
1           | {"f3":1}
2           |