sql-server-2016 - 如何查询json数组字段

Question

如何查询 json 数组字段。

我的 sql 表字段是个人数据，示例数据如下

[{"Name":"A","Age":"12"},
{"Name":"B","Age":"22"},
{"Name":"C","Age":"22"}]

所以需要的查询类似于 Name=A

标签： sql-server-2016

Answer 1

这应该将 JSON 置于您可以正常查询的格式：

select 
 SUBSTRING([name], CHARINDEX(':', [name]) + 1, 8000) as [name]
,SUBSTRING([age], CHARINDEX(':', [age]) + 1, 8000) as [name]

from
(select
 SUBSTRING(value, 1, CHARINDEX(',', value) - 1) AS [name]
,SUBSTRING(value, CHARINDEX(':',value)+3,CHARINDEX(',',value)-1) as [age]

from
(select replace(replace(replace(replace(strTest, '[', ''), '}', ''), ']', ''), '"', '') as strMod
from [dbo].[tblTest]
) as a

cross apply string_split(strMod, '{')
where value <> ''
) as b