我在sql server数据库中有json类型列的表。
Table - SomeTable Id | Properties 1 | {"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4"} 2 | {"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4"} ...|...
我编写了select查询,它分别选择每个字段的值:
SELECT JSON_VALUE(Properties, '$.field1') as field1, JSON_VALUE(Properties, '$.field2') as field2, JSON_VALUE(Properties, '$.field3') as field3, JSON_VALUE(Properties, '$.field4') as field4 FROM SomeTable
我在Microsoft的文档中找到了这种方法(https://docs.microsoft.com/zh-cn/sql/relational- databases/json/json-data-sql-server?view=sql-server- ver15)
可以在查询中编写许多JSON_VALUE-s损害性能吗?SQL是否对查询中写入的每个JSON_VALUE进行字符串反序列化。
您可以尝试使用OPENJSON()显式架构,Properties通过一个函数调用(针对四个或更多JSON_VALUE()调用)来解析存储在列中的JSON :
OPENJSON()
Properties
JSON_VALUE()
桌子:
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()显式架构,您可以为返回的列定义适当的数据类型。
nvarchar(4000)