可以在MSSQL查询中使用多个JSON_VALUE-s损害性能 [英] Can using multiple JSON_VALUE-s in MSSQL query harm performance

问题描述

我在sql server数据库中有一个表，其中的表具有json类型的列.

I have table in sql server database which have column of json type.

Table - SomeTable
Id | Properties
1  | {"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4"}
2  | {"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4"}
...|...

我编写了select查询，它分别选择每个字段的值:

I wrote select query which selects value of each field separately:

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的文档中找到了这种方法(

I found this approach in Microsoft's docs (https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15)

能否在查询中写入许多JSON_VALUE-损害性能? SQL是否对查询中写入的每个JSON_VALUE进行字符串反序列化.

Can writing many JSON_VALUE-s in query harm performance ? Is SQL doing deserialization of string for each JSON_VALUE written in query.

推荐答案

您可以尝试将OPENJSON()与显式架构一起使用，以通过一个函数调用(针对四个或更多JSON_VALUE()通话):

You may try to use OPENJSON() with an explicit schema to parse the JSON stored in the Properties column with one function call (against four or more JSON_VALUE() calls):

表格:

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()的结果是类型为nvarchar(4000)的标量值.使用OPENJSON()和显式架构，您可以为返回的列定义适当的数据类型.

As an additional note, the result from the JSON_VALUE() is a scalar value of type nvarchar(4000). With OPENJSON() and explicit schema you may define the appropriate data type for the returned columns.

这篇关于可以在MSSQL查询中使用多个JSON_VALUE-s损害性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！