按JSON列中的值进行搜索

Question

我有一个表，其中包含JSON值的列，例如:

<表类> id 价值 tbody><<tr>1{"components"[{"label"问候1","components":[{"label"hello","value": 10},{"label"hi","value": 20}]}]}2{"components"[{"label"问候2","components":[{"label"aloha","value": 30},{"label"hola","value": 40}]}]}

因为您有许多层嵌套的JSON对象，您将需要向SQL Server指定您需要挖掘所有这些层。每一层都有一个label和components属性，这是一个额外的(可能是不必要的)复杂性，也需要处理。在你的"简化JSON"示例中，有5层需要查询，要注意在适当的级别应用过滤器，以确保返回所有嵌套的属性。

可以这么说，这是不理想的，如果JSON模式不那么复杂的话，效果会更好。

查询

declare @t table(id int,[value] nvarchar(1000));
insert into @t values
(1,'{"components": [{"label": "greeting 1", "components": [{"label": "hello", "value": 10}, {"label":"hi", "value": 20}]}]}')
,(2,'{"components": [{"label": "greeting 2", "components": [{"label": "aloha", "value": 30}, {"label":"hola", "value": 40}]}]}')
;
select g.Greeting
,v.GreetingLabel
,v.GreetingValue
from @t as t
cross apply openjson(t.[value],'$')
with(Greetings nvarchar(max) 'strict $.components' as json) as j    -- Use of optional strict keyword requires that the property exists within the JSON object
cross apply openjson(j.Greetings,'$')
with(Greeting nvarchar(50) 'strict $.label'
,Components nvarchar(max) 'strict $.components' as json
) as g
cross apply openjson(g.Components,'$')
with(GreetingLabel nvarchar(50) 'strict $.label'
,GreetingValue int 'strict $.value'
) as v
where g.Greeting like 'greeting%'
and v.GreetingValue = 10;

---

输出

tbody> <<tr>

Greeting	GreetingLabel	GreetingValue
问候1	你好	10