SQL Server根据指定GUID从表存储的JSON结构中查询对应Link值

你存储的JSON包含Internal、External两个顶层对象数组，目标GUID可能出现在任意一个数组中，通过CROSS APPLY配合OPENJSON拆解数组元素后匹配即可，可直接运行的语句如下：

SELECT  parsedItem.Link
FROM    [dbo].[Vorgaenge] v
LEFT JOIN [dbo].[Vorgangsschritte] s ON v.id = s.Vorgang_ID
LEFT JOIN [dbo].[Vorgangsmassnahmen] m ON s.id = m.Vorgangsschritt_ID
-- 拆解Internal数组下的所有元素
CROSS APPLY OPENJSON(m.Formulardata, '$.Internal')
WITH (
    GUID UNIQUEIDENTIFIER '$.GUID',
    Link INT '$.Link'
) AS internalItems
-- 拆解External数组下的所有元素
CROSS APPLY OPENJSON(m.Formulardata, '$.External')
WITH (
    GUID UNIQUEIDENTIFIER '$.GUID',
    Link INT '$.Link'
) AS externalItems
-- 合并两个数组的解析结果做匹配
CROSS APPLY (
    SELECT GUID, Link FROM internalItems
    UNION ALL
    SELECT GUID, Link FROM externalItems
) AS parsedItem
WHERE   m.Vorgangsmassnahme_ID = 21
    AND v.id = @Vorgang_ID
    AND parsedItem.GUID = '49383921-5bd8-4cf9-a104-67f957b7fdc7'

关键说明 #

• 语句兼容SQL Server 2016及以上支持JSON解析的版本，OPENJSON的第二个参数指定JSON路径，可直接定位到对应数组，WITH子句会自动把数组内对象的字段映射为指定数据类型的列，无需额外类型转换
• 如果你提前明确目标GUID只会出现在Internal/External其中一个数组里，可以删除对应另一个数组的解析、合并逻辑，查询性能会更好
• 如果存在同一个GUID在两个数组中同时存在的情况，语句会返回多条匹配结果，可根据业务需求添加TOP 1或DISTINCT去重
• 原有查询的WHERE条件中直接限定m.Vorgangsmassnahme_ID = 21，会将m表的LEFT JOIN等效为INNER JOIN，如果需要保留未匹配到m表记录的结果，请将该条件移动到m表的JOIN ON子句中

内容的提问来源于stack exchange，提问作者test9999