SQL SERVER 2022-11-JSON 확장

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.address'); — 1 반환
GO

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.addresses'); — 0 반환

SELECT JSON_OBJECT();

SELECT JSON_OBJECT('name':'value', 'type':1)

SELECT JSON_OBJECT('name':'value', 'type':NULL ABSENT ON NULL) — NULL/ABSENT

SELECT JSON_OBJECT('name':'value', 'type':JSON_ARRAY(1, 2))

SELECT JSON_OBJECT('name':'value', 'type':JSON_OBJECT('type_id':1, 'name':'a'))
GO

DECLARE @id_key nvarchar(10) = N'id',@id_value nvarchar(64) = NEWID();
SELECT JSON_OBJECT('user_name':USER_NAME(), @id_key:@id_value, 'sid':(SELECT @@SPID))
GO

SELECT TOP(2)
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;