一、JSON_PATH_EXISTS
返回 1 或 0 的位值,或返回 NULL。 如果 value_expression 或输入为 SQL NULL 值,则返回 NULL。 如果输入中存在给定的 SQL/JSON 路径,则返回 1,或者返回非空序列。 否则将返回 0。
看起来好像用处不大,下面给个例子:
DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';
SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.address'); -- 1
SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';
SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.addresses'); -- 0
二、JSON_OBJECT
SELECT s.session_id,
JSON_OBJECT('security_id':s.security_id, 'login':s.login_name, 'status':s.status) as info
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1;
返回
session_id | info |
---|---|
51 | {"security_id":"AQYAAAAAAAVQAAAAY/0dmFnai5oioQHh9eNArBIkYd4=","login":"NT SERVICE\\SQLTELEMETRY$SQL22" ,"status":"sleeping"} |
52 | {"security_id":"AQUAAAAAAAUVAAAAoGXPfnhLm1/nfIdwAMgbAA==","login":WORKGROUP\\sqluser","status":"running"} |
三、JSON_ARRAY
SELECT s.session_id, JSON_ARRAY(s.host_name, s.program_name, s.client_interface_name)
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1;
返回
session_id | info |
---|---|
52 | ["WIN16-VM","Microsoft SQL Server Management Studio - Query",".Net SqlClient Data Provider"] |
55 | ["WIN16-VM","Microsoft SQL Server Management Studio - Query",".Net SqlClient Data Provider"] |
56 | ["WIN19-VM","SQLServerCEIP",".Net SqlClient Data Provider"] |
二、三貌似还可以,等我想到有什么用处再和大家交流