80.JSON_QUERY
JSON_QUERY(
<JSON_API_common_syntax>
[ <JSON_output_clause> ]
[ <JSON_query_wrapper_behavior> ]
[ <JSON_query_empty_behavior> ON EMPTY ]
[ <JSON_query_error_behavior> ON ERROR ]
)
<JSON_API_common_syntax> ::= <JSON_context_item>, <JSON_path_specification>
JSON_context_item
Specifies the JSON document to operate on, such as a table column, string, or collection.
JSON_path_specification
<JSON_path_specification> ::= <JSON_path_mode> <JSON_path_wff>
<JSON_path_mode> ::= STRICT | LAX
<JSON_path_wff> indicates an actual JSON path (for example, '$.item1').
<JSON_path_specification> does not use double quotes.
<JSON_output_clause> ::= RETURNING <data_type>
返回类型:
Supported data types: NVARCHAR(<length>), VARCHAR(<length>).
<JSON_query_wrapper_behavior> WRAPPER
<JSON_query_wrapper_behavior> ::=
WITHOUT [ ARRAY ]
| WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]
默认WITHOUT ARRAY WRAPPER,如果是WITH,默认WITH UNCONDITIONAL ARRAY WRAPPER
<JSON_query_empty_behavior> ON EMPTY
<JSON_query_empty_behavior> ::=
ERROR | NULL | EMPTY ARRAY
| EMPTY OBJECT
默认NULL ON EMPTY
ERROR ON EMPTY:返回错误,如果没有指定值;
NULL ON EMPTY:返回空;
EMPTY ARRAY ON EMPTY:返回empty array;
EMPTY OBJECT ON EMPTY:返回empty object;
<JSON_query_error_behavior> ON ERROR
<JSON_query_error_behavior> ::=
ERROR | NULL | EMPTY ARRAY
| EMPTY OBJECT
默认NULL ON ERROR,
当query出现error时,返回动作;
ERROR ON ERROR:返回error,当返回值报错
NULL ON ERROR:返回null;
EMPTY ARRAY ON ERROR:返回empty array;
EMPTY OBJECT ON ERROR:返回empty object;
JSON符号:
$:第一个字符;
.:成员运算符,访问成员;
[]:
to:array,示例:$.item[1 to 3];
*:所有成员,示例:$.*.item[*];
JSON_TABLE(
<JSON_API_common_syntax>
<JSON_table_columns_clause>
[ <JSON_table_error_behavior> ON ERROR ]
)
<JSON_table_columns_clause> ::=
COLUMNS ( <JSON_table_column_definition> [,... ] )
<JSON_table_column_definition> ::=
<JSON_table_ordinality_column_definition>
| <JSON_table_regular_column_definition>
| <JSON_table_formatted_column_definition>
| <JSON_table_nested_columns>
<JSON_table_ordinality_column_definition> ::= <column_name> FOR ORDINALITY
生成序号列
<JSON_table_regular_column_definition> ::= <column_name> <data_type>
PATH <JSON_table_column_path_specification>
[ JSON <table_column_empty_behavior> ON EMPTY ]
[ JSON_table_column_error_behavior> ON ERROR ]
生成普通列
<JSON_table_column_path_specification> ::= <JSON_path_specification>
获取json值的路径
<JSON_table_column_empty_behavior> ::= ERROR | NULL | DEFAULT <value_expression>
当创建列为空时,执行操作,ERROR:返回error;NULL:返回null;DEFAULT:返回默认值
<JSON_table_column_error_behavior> ::= ERROR | NULL | DEFAULT <value_expression>
当创建列出错时,执行操作;
<JSON_table_formatted_column_definition> ::=
<column_name> <data_type>
FORMAT <JSON_representation>
PATH <JSON_table_column_path_specification>
[ <JSON_table_formatted_column_wrapper_behavior> WRAPPER ]
[ <JSON_table_formatted_column_empty_behavior> ON EMPTY ]
[ <JSON_table_formatted_column_error_behavior> ON ERROR ]
<data_type>只支持VARCHAR(<n>),NVARCHAR(<n>)
<JSON_representation> ::= JSON | JSON ENCODING { UTF8 } | JSON ENCODING { UTF16 } | JSON ENCODING { UTF32 }
设置格式
<JSON_table_nested_columns> ::= NESTED [ PATH ]
<JSON_table_nested_path_specification>
<JSON_table_columns_clause>
<JSON_table_nested_path_specification> ::= <JSON_path_specification>
JSON_VALUE(
<JSON_API_common_syntax>
[ <JSON_returning_clause> ]
[ <JSON_value_empty_behavior> ON EMPTY ]
[ <JSON_value_error_behavior> ON ERROR ]
)
<JSON_returning_clause> ::= RETURNING <data_type>
<data_type> ::=
INTEGER | BIGINT | DECIMAL
| VARCHAR(<integer>)
| NVARCHAR(<integer>)
*/
--返回 {"item1":1,"item2":2,"item3":3}
--SELECT JSON_QUERY('{"item1":1, "item2":2, "item3":3}', '$') FROM DUMMY;
--返回[1]
--SELECT JSON_QUERY('{"item1":1, "item2":2, "item3":3}', '$.item1' WITH WRAPPER) FROM DUMMY;
CREATE ROW TABLE TEST_JSON (A INT, B NVARCHAR(5000));
INSERT INTO TEST_JSON VALUES (1,
'{
"PONumber": 1,
"Reference": "BSMITH-74635645",
"Requestor": "Barb Smith",
"User": "BSMITH",
"CostCenter": "A50",
"ShippingInstructions":{
"name": "Barb Smith",
"Address":{
"street": "100 Fairchild Ave",
"city": "San Diego",
"state": "CA","zipCode": 23345,
"country": "USA"
},
"Phone": [{"type": "Office", "number": "519-555-6310"}]
},
"SpecialInstructions": "Surface Mail",
"LineItems": [
{
"ItemNumber": 1,
"Part": {"Description": "Basic Kit", "UnitPrice": 19.95, "UPCCode": 73649587162},
"Quantity": 7
},
{
"ItemNumber": 2,
"Part": {"Description": "Base Kit 2", "UnitPrice": 29.95, "UPCCode": 83600229374},
"Quantity": 1
},
{
"ItemNumber": 3,
"Part": {"Description": "Professional", "UnitPrice": 39.95, "UPCCode": 33298003521},
"Quantity": 8
},
{
"ItemNumber": 4,
"Part": {"Description": "Enterprise", "UnitPrice": 49.95, "UPCCode": 91827739856},
"Quantity": 8
},
{"ItemNumber": 5,
"Part": {"Description": "Unlimited", "UnitPrice": 59.95, "UPCCode": 22983303876},
"Quantity": 8
}
]
} '
);
/*
SELECT JT.* FROM JSON_TABLE(TEST_JSON.B, '$.LineItems[*]'
COLUMNS (
RN FOR ORDINALITY,
ITEM_NUMBER INT PATH '$.ItemNumber',
UPC_CODE BIGINT PATH '$.Part.UPCCode'
)
) AS JT;
SELECT * FROM JSON_TABLE(TEST_JSON.B, '$.ShippingInstructions'
COLUMNS ( PHONE VARCHAR(50) FORMAT JSON PATH '$.Phone' )
) AS JT;
*/
--不存在?
--SELECT JSON_VALUE('{"item1":10}', '$.item1') AS "value" FROM DUMMY;
--SELECT JSON_VALUE('[0, 1, 2, 3]', '$[0]') AS "value" FROM DUMMY;
--SELECT JSON_VALUE('{"item":"string"}', '$.item' RETURNING DECIMAL ERROR ON ERROR) AS "Item" FROM DUMMY;
--SELECT JSON_VALUE('{"firstname":"John"}', '$.lastname' DEFAULT 'No last name found' ON EMPTY) AS "Last Name" FROM DUMMY;
--SELECT JSON_VALUE('{"firstname":"John"}', 'strict $.lastname' ERROR ON ERROR) AS "Last Name" FROM DUMMY;