您可以尝试使用OPENJSON()
显式架构,Properties
通过一个函数调用(针对四个或更多JSON_VALUE()
调用)来解析存储在列中的JSON :
桌子:
CREATE TABLE SoMetable (
Id int,
Properties varchar(1000)
)
INSERT INTO SoMetable (Id, Properties)
VALUES
(1, '{"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4"}'),
(2, '{"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4"}')
陈述:
SELECT s.Id, j.*
FROM SoMetable s
CROSS APPLY OPENJSON(s.Properties) WITH (
field1 varchar(100) '$.field1',
field2 varchar(100) '$.field2',
field3 varchar(100) '$.field3',
field4 varchar(100) '$.field4'
) j
结果:
Id field1 field2 field3 field4
----------------------------------
1 value1 value2 value3 value4
2 value1 value2 value3 value4
另外请注意,的结果JSON_VALUE()
是type的标量值nvarchar(4000)
。使用OPENJSON()
显式架构,您可以为返回的列定义适当的数据类型。