Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# JSON functions
**Topics**
+ [JSON\$1PARSE function](JSON_PARSE.md)
+ [CAN\$1JSON\$1PARSE function](CAN_JSON_PARSE.md)
+ [JSON\$1SERIALIZE function](JSON_SERIALIZE.md)
+ [JSON\$1SERIALIZE\$1TO\$1VARBYTE function](JSON_SERIALIZE_TO_VARBYTE.md)
+ [Text-based JSON functions](text-json-functions.md)
**Note**
We recommend that you use the following functions for working with JSON:
[JSON\$1PARSE function](JSON_PARSE.md)
[CAN\$1JSON\$1PARSE function](CAN_JSON_PARSE.md)
[JSON\$1SERIALIZE function](JSON_SERIALIZE.md)
[JSON\$1SERIALIZE\$1TO\$1VARBYTE function](JSON_SERIALIZE_TO_VARBYTE.md)
With JSON\$1PARSE, you only need to convert JSON text to a SUPER type value once at ingestion, after which you can operate on the SUPER values. Amazon Redshift parses SUPER values more efficiently than VARCHAR, which is the output for the text-based JSON functions. For more information on working with the SUPER data type, go to [Semi-structured data in Amazon Redshift](super-overview.md).
When you need to store a relatively small set of key-value pairs, you might save space by storing the data in JSON format. Because JSON strings can be stored in a single column, using JSON might be more efficient than storing your data in tabular format. For example, suppose you have a sparse table, where you need to have many columns to fully represent all possible attributes, but most of the column values are NULL for any given row or any given column. By using JSON for storage, you might be able to store the data for a row in key:value pairs in a single JSON string and eliminate the sparsely-populated table columns.
In addition, you can easily modify JSON strings to store additional key:value pairs when your JSON schema changes without needing to add columns to a table.
We recommend using JSON sparingly. JSON isn't a good choice for storing larger datasets because, by storing disparate data in a single column, JSON doesn't use the Amazon Redshift column store architecture. Though Amazon Redshift supports JSON functions over CHAR and VARCHAR columns, we recommend using SUPER for processing data in JSON serialization format. SUPER uses a post-parse schemaless representation that can efficiently query hierarchical data. For more information about the SUPER data type, see [Semi-structured data in Amazon Redshift](super-overview.md).
JSON uses UTF-8 encoded text strings, so JSON strings can be stored as CHAR or VARCHAR data types.
JSON strings must be properly formatted JSON, according to the following rules:
+ The root level JSON can either be a JSON object or a JSON array. A JSON object is an unordered set of comma-separated key:value pairs enclosed by curly braces.
For example, `{"one":1, "two":2} `
+ A JSON array is an ordered set of comma-separated values enclosed by brackets.
An example is the following: `["first", {"one":1}, "second", 3, null] `
+ JSON arrays use a zero-based index; the first element in an array is at position 0. In a JSON key:value pair, the key is a string in double quotation marks.
+ A JSON value can be any of the following:
+ JSON object
+ array
+ string
+ Represented using double quotation marks
+ number
+ Includes integers, decimals, and floats
+ boolean
+ null
+ Empty objects and empty arrays are valid JSON values.
+ JSON fields are case-sensitive.
+ White space between JSON structural elements (such as `{ }, [ ]`) is ignored.
The Amazon Redshift JSON functions and the Amazon Redshift COPY command use the same methods to work with JSON-formatted data. For more information about working with JSON, see [COPY from JSON format](copy-usage_notes-copy-from-json.md)
# JSON\$1PARSE function
The JSON\$1PARSE function parses data in JSON format and converts it into the `SUPER` representation.
To ingest into `SUPER` data type using the INSERT or UPDATE command, use the JSON\$1PARSE function. When you use JSON\$1PARSE() to parse JSON strings into `SUPER` values, certain restrictions apply. For additional information, see [Parsing options for SUPER](super-configurations.md#parsing-options-super).
## Syntax
```
JSON_PARSE( {json_string | binary_value} )
```
## Arguments
*json\$1string*
An expression that returns serialized JSON as a `VARBYTE` or `VARCHAR` type.
*binary\$1value*
A VARBYTE type binary value.
## Return type
`SUPER`
## Examples
To convert the JSON array `[10001,10002,"abc"]` into the `SUPER` data type, use the following example.
```
SELECT JSON_PARSE('[10001,10002,"abc"]');
+---------------------+
| json_parse |
+---------------------+
| [10001,10002,"abc"] |
+---------------------+
```
To make sure that the function converted the JSON array into the `SUPER` data type, use the following example. For more information, see [JSON\$1TYPEOF function](r_json_typeof.md)
```
SELECT JSON_TYPEOF(JSON_PARSE('[10001,10002,"abc"]'));
+-------------+
| json_typeof |
+-------------+
| array |
+-------------+
```
# CAN\$1JSON\$1PARSE function
The CAN\$1JSON\$1PARSE function parses data in JSON format and returns `true` if the result can be converted to a `SUPER` value using the JSON\$1PARSE function.
## Syntax
```
CAN_JSON_PARSE( {json_string | binary_value} )
```
## Arguments
*json\$1string*
An expression that returns serialized JSON in `VARCHAR` form.
*binary\$1value*
A VARBYTE type binary value.
## Return type
`BOOLEAN`
## Usage notes
+ CAN\$1JSON\$1PARSE returns false for empty strings. It returns NULL when the input argument is null.
## Examples
The following example shows CAN\$1JSON\$1PARSE running on a properly formed JSON array using a CASE condition. It returns true, so Amazon Redshift runs the JSON\$1PARSE function on the example value.
```
SELECT CASE
WHEN CAN_JSON_PARSE('[10001,10002,"abc"]')
THEN JSON_PARSE('[10001,10002,"abc"]')
END;
case
---------------------
'[10001,10002,"abc"]'
```
The following example shows CAN\$1JSON\$1PARSE running on a value that isn’t JSON format using a CASE condition. It returns false, so Amazon Redshift returns the segment in the ELSE clause of the CASE condition instead.
```
SELECT CASE
WHEN CAN_JSON_PARSE('This is a string.')
THEN JSON_PARSE('This is a string.')
ELSE 'This is not JSON.'
END;
case
---------------------
"This is not JSON."
```
# JSON\$1SERIALIZE function
The JSON\$1SERIALIZE function serializes a `SUPER` expression into textual JSON representation to follow RFC 8259. For more information on that RFC, see [The JavaScript Object Notation (JSON) Data Interchange Format](https://tools.ietf.org/html/rfc8259).
The `SUPER` size limit is approximately the same as the block limit, and the `VARCHAR` limit is smaller than the `SUPER` size limit. Therefore, the JSON\$1SERIALIZE function returns an error when the JSON format exceeds the VARCHAR limit of the system. To check the size of a `SUPER` expression, see the [JSON\$1SIZE](r_json_size.md) function.
## Syntax
```
JSON_SERIALIZE(super_expression)
```
## Arguments
*super\$1expression*
A `SUPER` expression or column.
## Return type
`VARCHAR`
**Note**
The returned VARCHAR value is always a non-null JSON string. If *super\$1expression* is NULL, JSON\$1SERIALIZE returns the JSON string `'null'`.
## Examples
To serialize a `SUPER` value to a string, use the following example.
```
SELECT JSON_SERIALIZE(JSON_PARSE('[10001,10002,"abc"]'));
+---------------------+
| json_serialize |
+---------------------+
| [10001,10002,"abc"] |
+---------------------+
```
# JSON\$1SERIALIZE\$1TO\$1VARBYTE function
The JSON\$1SERIALIZE\$1TO\$1VARBYTE function converts a `SUPER` value to a JSON string similar to JSON\$1SERIALIZE(), but stored in a `VARBYTE` value instead.
## Syntax
```
JSON_SERIALIZE_TO_VARBYTE(super_expression)
```
## Arguments
*super\$1expression*
A `SUPER` expression or column.
## Return type
`VARBYTE`
## Examples
To serialize a `SUPER` value and returns the result in `VARBYTE` format, use the following example.
```
SELECT JSON_SERIALIZE_TO_VARBYTE(JSON_PARSE('[10001,10002,"abc"]'));
+----------------------------------------+
| json_serialize_to_varbyte |
+----------------------------------------+
| 5b31303030312c31303030322c22616263225d |
+----------------------------------------+
```
To serialize a `SUPER` value and casts the result to `VARCHAR` format, use the following example. For more information, see [CAST function](r_CAST_function.md).
```
SELECT CAST((JSON_SERIALIZE_TO_VARBYTE(JSON_PARSE('[10001,10002,"abc"]'))) AS VARCHAR);
+---------------------------+
| json_serialize_to_varbyte |
+---------------------------+
| [10001,10002,"abc"] |
+---------------------------+
```
# Text-based JSON functions
The functions in this section parse JSON values as VARCHAR. For parsing JSON, we recommend you instead use the following functions, which parse JSON values as SUPER. Amazon Redshift parses SUPER values more efficiently than VARCHAR.
+ [JSON\$1PARSE function](JSON_PARSE.md)
+ [CAN\$1JSON\$1PARSE function](CAN_JSON_PARSE.md)
+ [JSON\$1SERIALIZE function](JSON_SERIALIZE.md)
+ [JSON\$1SERIALIZE\$1TO\$1VARBYTE function](JSON_SERIALIZE_TO_VARBYTE.md)
**Topics**
+ [IS\$1VALID\$1JSON function](IS_VALID_JSON.md)
+ [IS\$1VALID\$1JSON\$1ARRAY function](IS_VALID_JSON_ARRAY.md)
+ [JSON\$1ARRAY\$1LENGTH function](JSON_ARRAY_LENGTH.md)
+ [JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT function](JSON_EXTRACT_ARRAY_ELEMENT_TEXT.md)
+ [JSON\$1EXTRACT\$1PATH\$1TEXT function](JSON_EXTRACT_PATH_TEXT.md)
# IS\$1VALID\$1JSON function
**Note**
CAN\$1JSON\$1PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.
Instead of using IS\$1VALID\$1JSON, we recommend that you validate your JSON strings using the [CAN\$1JSON\$1PARSE function](CAN_JSON_PARSE.md).
The IS\$1VALID\$1JSON function validates a JSON string. The function returns Boolean `true` if the string is properly formed JSON or `false` if the string is malformed. To validate a JSON array, use [IS\$1VALID\$1JSON\$1ARRAY function](IS_VALID_JSON_ARRAY.md)
For more information, see [JSON functions](json-functions.md).
## Syntax
```
IS_VALID_JSON('json_string')
```
## Arguments
*json\$1string*
A string or expression that evaluates to a JSON string.
## Return type
`BOOLEAN`
## Examples
To create a table and insert JSON strings for testing, use the following example.
```
CREATE TABLE test_json(id int IDENTITY(0,1), json_strings VARCHAR);
-- Insert valid JSON strings --
INSERT INTO test_json(json_strings) VALUES
('{"a":2}'),
('{"a":{"b":{"c":1}}}'),
('{"a": [1,2,"b"]}');
-- Insert invalid JSON strings --
INSERT INTO test_json(json_strings) VALUES
('{{}}'),
('{1:"a"}'),
('[1,2,3]');
```
To validate the strings in the preceding example, use the following example.
```
SELECT id, json_strings, IS_VALID_JSON(json_strings)
FROM test_json
ORDER BY id;
+----+---------------------+---------------+
| id | json_strings | is_valid_json |
+----+---------------------+---------------+
| 0 | {"a":2} | true |
| 4 | {"a":{"b":{"c":1}}} | true |
| 8 | {"a": [1,2,"b"]} | true |
| 12 | {{}} | false |
| 16 | {1:"a"} | false |
| 20 | [1,2,3] | false |
+----+---------------------+---------------+
```
# IS\$1VALID\$1JSON\$1ARRAY function
**Note**
JSON\$1PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.
Instead of using IS\$1VALID\$1JSON\$1ARRAY, we recommend that you parse your JSON strings using the [JSON\$1PARSE function](JSON_PARSE.md) to get a SUPER value. Then, use the [IS\$1ARRAY function](r_is_array.md) function to confirm that the array is properly formed.
The IS\$1VALID\$1JSON\$1ARRAY function validates a JSON array. The function returns Boolean `true` if the array is properly formed JSON or `false` if the array is malformed. To validate a JSON string, use [IS\$1VALID\$1JSON function](IS_VALID_JSON.md)
For more information, see [JSON functions](json-functions.md).
## Syntax
```
IS_VALID_JSON_ARRAY('json_array')
```
## Arguments
*json\$1array*
A string or expression that evaluates to a JSON array.
## Return type
`BOOLEAN`
## Examples
To create a table and insert JSON strings for testing, use the following example.
```
CREATE TABLE test_json_arrays(id int IDENTITY(0,1), json_arrays VARCHAR);
-- Insert valid JSON array strings --
INSERT INTO test_json_arrays(json_arrays)
VALUES('[]'),
('["a","b"]'),
('["a",["b",1,["c",2,3,null]]]');
-- Insert invalid JSON array strings --
INSERT INTO test_json_arrays(json_arrays)
VALUES('{"a":1}'),
('a'),
('[1,2,]');
```
To validate the strings in the preceding example, use the following example.
```
SELECT json_arrays, IS_VALID_JSON_ARRAY(json_arrays)
FROM test_json_arrays ORDER BY id;
+------------------------------+---------------------+
| json_arrays | is_valid_json_array |
+------------------------------+---------------------+
| [] | true |
| ["a","b"] | true |
| ["a",["b",1,["c",2,3,null]]] | true |
| {"a":1} | false |
| a | false |
| [1,2,] | false |
+------------------------------+---------------------+
```
# JSON\$1ARRAY\$1LENGTH function
**Note**
JSON\$1PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.
Instead of using JSON\$1ARRAY\$1LENGTH, we recommend that you parse your JSON strings using the [JSON\$1PARSE function](JSON_PARSE.md) to get a SUPER value. Then, use the [GET\$1ARRAY\$1LENGTH function](get_array_length.md) to get the length of your array.
The JSON\$1ARRAY\$1LENGTH function returns the number of elements in the outer array of a JSON string. If the *null\$1if\$1invalid* argument is set to `true` and the JSON string is invalid, the function returns `NULL` instead of returning an error.
For more information, see [JSON functions](json-functions.md).
## Syntax
```
JSON_ARRAY_LENGTH('json_array' [, null_if_invalid ] )
```
## Arguments
*json\$1array*
A properly formatted JSON array.
*null\$1if\$1invalid*
(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`.
## Return type
`INTEGER`
## Examples
To return the number of elements in the array, use the following example.
```
SELECT JSON_ARRAY_LENGTH('[11,12,13,{"f1":21,"f2":[25,26]},14]');
+-------------------+
| json_array_length |
+-------------------+
| 5 |
+-------------------+
```
To return an error because the JSON is invalid, use the following example.
```
SELECT JSON_ARRAY_LENGTH('[11,12,13,{"f1":21,"f2":[25,26]},14');
ERROR: invalid json array object [11,12,13,{"f1":21,"f2":[25,26]},14
```
To set *null\$1if\$1invalid* to *true*, so the statement the returns `NULL` instead of returning an error for invalid JSON, use the following example.
```
SELECT JSON_ARRAY_LENGTH('[11,12,13,{"f1":21,"f2":[25,26]},14',true);
+-------------------+
| json_array_length |
+-------------------+
| NULL |
+-------------------+
```
# JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT function
**Note**
JSON\$1PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.
Instead of using JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT, we recommend that you parse your JSON strings using the [JSON\$1PARSE function](JSON_PARSE.md) to get a SUPER value. Then, query the element you want using its array index, using the `value[element position]` syntax. For more information on querying array elements in SUPER values, go to [Querying semi-structured data](query-super.md).
The JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT function returns a JSON array element in the outermost array of a JSON string, using a zero-based index. The first element in an array is at position 0. If the index is negative or out of bounds, JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT returns `NULL`. If the *null\$1if\$1invalid* argument is set to `TRUE` and the JSON string is invalid, the function returns `NULL` instead of returning an error.
For more information, see [JSON functions](json-functions.md).
## Syntax
```
JSON_EXTRACT_ARRAY_ELEMENT_TEXT('json string', pos [, null_if_invalid ] )
```
## Arguments
*json\$1string*
A properly formatted JSON string.
*pos*
An `INTEGER` representing the index of the array element to be returned, using a zero-based array index.
*null\$1if\$1invalid*
(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`.
## Return type
`VARCHAR`
A `VARCHAR` string representing the JSON array element referenced by *pos*.
## Examples
To return array element at position 2, which is the third element of a zero-based array index, use the following example.
```
SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('[111,112,113]', 2);
+---------------------------------+
| json_extract_array_element_text |
+---------------------------------+
| 113 |
+---------------------------------+
```
To return an error because the JSON is invalid, use the following example.
```
SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('["a",["b",1,["c",2,3,null,]]]',1);
ERROR: invalid json array object ["a",["b",1,["c",2,3,null,]]]
```
To set *null\$1if\$1invalid* to *true*, so the statement returns `NULL` instead of returning an error for invalid JSON, use the following example.
```
SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('["a",["b",1,["c",2,3,null,]]]',1,true);
+---------------------------------+
| json_extract_array_element_text |
+---------------------------------+
| NULL |
+---------------------------------+
```
Consider the following example statements. If the provided JSON string or the index is NULL, JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT returns NULL regardless of the value of any other parameters.
```
--Statement where json_string is NULL.
SELECT json_extract_array_element_text(NULL, 0)
json_extract_array_element_text
---------------------------------
NULL
--Statement where pos is NULL and json_string is invalid JSON.
SELECT json_extract_array_element_text('invalid_json', NULL);
json_extract_array_element_text
---------------------------------
NULL
--Statement where json_string is NULL and null_if_invalid is FALSE.
SELECT json_extract_array_element_text(NULL, 0, FALSE);
json_extract_array_element_text
---------------------------------
NULL
```
Consider the following example statements. When *null\$1if\$1invalid* is TRUE, JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT returns NULL when *json\$1string* is invalid JSON. If *null\$1if\$1invalid* is FALSE or isn’t set, the function returns an error when *json\$1string* is invalid.
```
--Statement with invalid JSON where null_if_invalid is TRUE.
SELECT json_extract_array_element_text('invalid_json', 0, TRUE);
json_extract_array_element_text
---------------------------------
NULL
--Statement with invalid JSON where null_if_invalid is FALSE.
SELECT json_extract_array_element_text('invalid_json', 0);
ERROR: JSON parsing error
```
Consider the following example, where *json\$1string* is valid JSON, and *pos* refers to a JSON `null` value. In this case, JSON\$1EXTRACT\$1ARRAY\$1ELEMENT\$1TEXT returns NULL, regardless of the value of *null\$1if\$1invalid*.
```
--Statement selecting a null value.
SELECT json_extract_array_element_text('[null]', 0);
json_extract_array_element_text
----------------------------------
NULL
```
# JSON\$1EXTRACT\$1PATH\$1TEXT function
**Note**
JSON\$1PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.
Instead of using JSON\$1EXTRACT\$1PATH\$1TEXT, we recommend that you parse your JSON strings using the [JSON\$1PARSE function](JSON_PARSE.md) 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 semi-structured data](query-super.md).
The JSON\$1EXTRACT\$1PATH\$1TEXT 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\$1EXTRACT\$1PATH\$1TEXT returns `NULL`.
If the *null\$1if\$1invalid* argument is set to `TRUE` and the JSON string is invalid, the function returns `NULL` instead of returning an error.
JSON\$1EXTRACT\$1PATH\$1TEXT has a 64KB data-size maximum. Thus, if any JSON record is larger than 64KB, processing it with JSON\$1EXTRACT\$1PATH\$1TEXT results in an error.
For information about additional JSON functions, see [JSON functions](json-functions.md). For more information about working with JSON, see [COPY from JSON format](copy-usage_notes-copy-from-json.md).
## Syntax
```
JSON_EXTRACT_PATH_TEXT('json_string', 'path_elem' [,'path_elem'[, …] ] [, null_if_invalid ] )
```
## Arguments
*json\$1string*
A properly formatted JSON string.
*path\$1elem*
A path element in a JSON string. One path element is required. Additional path elements can be specified, up to five levels deep.
*null\$1if\$1invalid*
(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](copy-usage_notes-copy-from-json.md#copy-usage-json-escape-characters).
## 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\$1if\$1invalid* 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 |
+------------------------+
```
Consider the following example, which selects 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 |
```
Consider the following example statements. The provided *path\$1elem* is NULL, so JSON\$1EXTRACT\$1PATH\$1TEXT returns NULL regardless of the value of any other parameters.
```
--Statement where path_elem is NULL and json_string is valid JSON.
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}',NULL);
json_extract_path_text
------------------------
NULL
--Statement where only one path_elem is NULL.
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4',NULL);
json_extract_path_text
------------------------
NULL
--Statement where path_elem is NULL and json_string is invalid JSON.
SELECT json_extract_path_text('invalid_json', NULL);
json_extract_path_text
------------------------
NULL
--Statement where path_elem is NULL and null_if_invalid is FALSE.
SELECT json_extract_path_text(NULL, 0, FALSE);
json_extract_path_text
------------------------
NULL
```
Consider the following example statements. When *null\$1if\$1invalid* is TRUE, JSON\$1EXTRACT\$1PATH\$1TEXT returns NULL when *json\$1string* is invalid JSON. If *null\$1if\$1invalid* is FALSE or isn’t set, the function returns an error when *json\$1string* is invalid.
```
--Statement with invalid JSON where null_if_invalid is TRUE.
SELECT json_extract_path_text('invalid_json', 0, TRUE);
json_extract_path_text
------------------------
NULL
--Statement with invalid JSON where null_if_invalid is FALSE.
SELECT json_extract_path_text('invalid_json', 0, FALSE);
ERROR: JSON parsing error
```
Consider the following examples, where *json\$1string* is valid JSON, and *path\$1elem* refers to a JSON `null` value. In this case, JSON\$1EXTRACT\$1PATH\$1TEXT returns NULL. Similarly, when *path\$1elem* refers to a non-existing value, JSON\$1EXTRACT\$1PATH\$1TEXT returns NULL, regardless of the value of *null\$1if\$1invalid*.
```
--Statement selecting a null value.
SELECT json_extract_path_text('[null]', 0);
json_extract_path_text
-------------------------
NULL
--Statement selecting a non-existing value.
SELECT json_extract_path_text('{}', 'a');
json_extract_path_text
-------------------------
NULL
```