存储具有可变值类型的扁平JSON(键值对)

Question

我有没有定义模式的JSON文档。每个文档可能具有不同的模式和值。

{
"location":
{
"latitude": 58.23,
"longitude": 25.11
},
"building": "A1",
"active": true,
"parameters": [ 1, { "scanInterval": 1000 } ]
}

这些JSON文档被扁平化，格式化为键值对。

{
"location.latitude": 58.23,
"location.longitude": 25.11,
"building": "A1",
"active": true,
"parameter[0]": 1,
"parameter[1].scanInterval": 1000
}

键总是字符串。

值可以是字符串、数字和布尔。

这些键值对将存储在SQL表中。要求是能够根据键值的JSON原生值过滤键值。

SELECT .... FROM ... WHERE [Key] = @key AND [Value] > @value; -- [Value] is integer/float
SELECT .... FROM ... WHERE [Key] = @key AND [Value] != @value; -- [Value] is bit/boolean
SELECT .... FROM ... WHERE [Key] = @key AND [Value] = @value; -- [Value] is string

这让我问-如何设计我的表？

选项A(铸造。

CREATE TABLE [dbo].[OptionA](
....
[Key] [nvarchar](max),
[ValueType] [nvarchar](max)
[Value] [nvarchar](max)
)

始终将[Value]存储为String，查询数据时，选择具有匹配[ValueType]和强制转换值的行：

... WHERE [ValueType] = 'Number' AND [Key] = @key AND CAST([Value] AS FLOAT) > @value

选项B(每种值类型的列。

CREATE TABLE [dbo].[OptionB](
....
[Key] [nvarchar](50),
[StringValue] [nvarchar](50) NULL,
[NumericValue] [float] NULL,
[BooleanValue] [bit] NULL
)

共有3列。每列对应一个值类型。在所有3列中，只有1列可以包含值，其余列为NULL。

查询数据时，选择具有适当值类型的列：

SELECT .... FROM ... WHERE [Key] = @key AND [NumericValue] > @value

哪一个选项的效果最好，或者总体上看起来更好？也许还有其他更好的选择？

我更倾向于A(的方法，然而所有的选角可能会增加额外的复杂性，并且可能会带来潜在的性能冲击。

Answer 1

有点难看，还有not fully tested，但也许这会让你朝着正确的方向前进。

我应该注意，SEQ影响是可选的

示例

Declare @JSON varchar(max) = '
{
"location":
{
"latitude": 58.23,
"longitude": 25.11
},
"building": "A1",
"active": true,
"parameters": [ 1, { "scanInterval": 1000 } ]
}
';
with cte0 as (
Select *
,spath = convert(varchar(max),[key])
,seq   = convert(varchar(250),10000+row_number() over(order by 1/0))
From OpenJSON(@json,'$') 
Union All
Select R.*
,spath = convert(varchar(max),concat(P.spath,case when try_convert(int,r.[key]) is not null and P.[type]>3 then quotename(r.[key]) else '.'+r.[key] end))
,seq   = convert(varchar(250),concat(P.seq,'',10000+row_number() over(order by 1/0)))
From  cte0 P 
Cross Apply OpenJSON(P.[Value],'$') R 
Where P.[Type]>3
)
Select [key] = spath
,value
,[Type]  = choose([Type],'string','numeric','bool','array','object')
from cte0 
Where [type]<=3
Order By seq

结果

key                         value   Type
location.latitude           58.23   numeric
location.longitude          25.11   numeric
building                    A1      string
active                      true    bool
parameters[0]               1       numeric
parameters[1].scanInterval  1000    numeric