JSON_EXTRACT_PATH_TEXT 函数
注意
JSON_PARSE 及其关联函数将 JSON 值解析为 SUPER，Amazon Redshift 解析 SUPER 的效率比 VARCHAR 更高。
我们建议您使用 JSON_PARSE 函数解析 JSON 字符串来获取 SUPER 值，而不是使用 JSON_EXTRACT_PATH_TEXT。然后，使用 value.attribute 语法查询您想要的元素。有关在 SUPER 值中查询数组元素的更多信息，请转到查询半结构化数据。
JSON_EXTRACT_PATH_TEXT 函数返回 JSON 字符串中的一系列路径元素引用的键/值对的值。JSON 路径最深可嵌套至 5 层。路径元素区分大小写。如果 JSON 字符串中不存在路径元素，JSON_EXTRACT_PATH_TEXT 将返回 NULL。
如果 null_if_invalid 参数设置为 TRUE 并且 JSON 字符串无效，函数将返回 NULL 而不是返回错误。
JSON_EXTRACT_PATH_TEXT 的最大数据大小为 64 KB。因此，如果任何 JSON 记录大于 64KB，那么使用 JSON_EXTRACT_PATH_TEXT 处理记录会导致错误。
有关其他 JSON 函数的信息，请参阅 JSON 函数。有关使用 JSON 的更多信息，请参阅从 JSON 格式数据执行的 COPY 操作。
语法
JSON_EXTRACT_PATH_TEXT('json_string', 'path_elem' [,'path_elem'[, …] ] [, null_if_invalid ] )
参数
- json_string
-
格式正确的 JSON 字符串。
- path_elem
-
JSON 字符串中的路径元素。需要一个路径元素。可指定额外的路径元素，最深五层。
- null_if_invalid
-
（可选）一个
BOOLEAN值，指定在输入 JSON 字符串无效时是否返回NULL，而不返回错误。要在 JSON 无效时返回NULL，请指定TRUE（t）。要在 JSON 无效时返回错误，请指定FALSE(f)。默认为FALSE。
在 JSON 字符串中，Amazon Redshift 将 \n 识别为换行符，将 \t 识别为制表符。要加载反斜杠，请使用反斜杠 ( \\ ) 对其进行转义。有关更多信息，请参阅 在 JSON 中转义字符。
返回类型
VARCHAR-
表示路径元素引用的 JSON 值的
VARCHAR字符串。
示例
要返回路径 'f4', 'f6' 的值，请使用以下示例。
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6');+------------------------+ | json_extract_path_text | +------------------------+ | star | +------------------------+
要因为 JSON 无效而返回错误，请使用以下示例。
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"}
要将 null_if_invalid 设置为 TRUE，以便语句在 JSON 无效返回 NULL，而不是返回错误，请使用以下示例。
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}','f4', 'f6',true);+------------------------+ | json_extract_path_text | +------------------------+ | NULL | +------------------------+
在以下示例中，选择路径 'farm', 'barn', 'color' 的值，并且检索到的值位于第三级。为更便于阅读，此示例使用 JSON lint 工具进行格式化。
SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": { "barn": { "color": "red", "feed stocked": true } } }', 'farm', 'barn', 'color');+------------------------+ | json_extract_path_text | +------------------------+ | red | +------------------------+
要因为缺少 'color' 元素而返回 NULL，请使用以下示例。此示例使用 JSON lint 工具进行格式化。
SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": { "barn": {} } }', 'farm', 'barn', 'color');+------------------------+ | json_extract_path_text | +------------------------+ | NULL | +------------------------+
如果 JSON 有效，则尝试提取缺失的元素将返回 NULL。
要返回路径 'house', 'appliances', 'washing machine', 'brand' 的值，请使用以下示例。
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 | +------------------------+
以下示例创建一个示例表并用 SUPER 值填充该表，然后在两行中均返回路径 'f2' 的值。
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 |
考虑以下示例语句。提供的 path_elem 为 NULL，因此 JSON_EXTRACT_PATH_TEXT 会返回 NULL，无论任何其他参数的值如何。
--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
考虑以下示例语句。如果 null_if_invalid 为 TRUE，则当 json_string 为无效 JSON 时，JSON_EXTRACT_PATH_TEXT 会返回 NULL。如果 null_if_invalid 为 FALSE 或未设置，则当 json_string 无效时，该函数会返回错误。
--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
考虑以下示例，其中 json_string 是有效的 JSON，path_elem 指的是 JSON null 值。在此情况下，JSON_EXTRACT_PATH_TEXT 会返回 NULL。同样，当 path_elem 指的是不存在的值时，JSON_EXTRACT_PATH_TEXT 会返回 NULL，无论 null_if_invalid 的值如何。
--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