--取某个key的值
SELECT jsonValue=JSON_VALUE('{"a":{"b":"xxxx"}}','$.a.b')  

--取所有key和value
SELECT * FROM OPENJSON('{"a":{"b":"xxxx"}}')
SELECT * FROM OPENJSON('{"a":{"b":["xxxx"]}}','$.a.b')
--解析数组
DECLARE @fDataValue NVARCHAR(max)='[{\"Id\":\"879a0193-64fd-453d-9385-09ae433615d8\",\"MultipleShopProjectDistributorSignId\":\"e6202274-5789-46b4-8508-e3da48adf16b\",\"Name\":\"158\",\"Sex\":\"女\",\"Role\":\"副控\",\"LiveNeed\":\"单人一间\",\"IsDeleted\":false,\"CreationTime\":\"2022-11-17 16:25:19\",\"CreatorId\":\"8fd3c9a4-c2b2-9635-d330-39ffeb39599a\",\"LastModificationTime\":\"2022-11-17 16:34:48\",\"LastModifierId\":\"8fd3c9a4-c2b2-9635-d330-39ffeb39599a\",\"RoomCount\":1,\"Tag\":\"\",\"IsLeader\":\"\",\"SourceType\":20,\"DingUserId\":\"\",\"PhoneNumber\":\"15882023696\"}]'
set @fDataValue=REPLACE(@fDataValue,'\"','"') --清除\符号

SELECT * 
FROM OPENJSON(@fDataValue)
WITH (
No NVARCHAR(150) '$.No', 
Id UNIQUEIDENTIFIER '$.Id',
Name NVARCHAR(150) '$.Name',
Sex NVARCHAR(150) '$.Sex', 
Role NVARCHAR(150) '$.Role', 
LiveNeed NVARCHAR(150) '$.LiveNeed', 
IsLeader bit '$.IsLeader', 
DingUserId NVARCHAR(150) '$.DingUserId', 
PhoneNumber NVARCHAR(150) '$.PhoneNumber', 
SourceType INT '$.SourceType'
)