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;