SQL Server:从包含json字符串的nvarchar(max)变量获取所有字符串出现(标记) [英] SQL Server : get all string occurences (tags) from nvarchar(max) variable containing a json string

问题描述

我有一个变量，其中包含一个很长的json字符串.因此，数据类型为NVARCHAR(MAX).在这个json字符串中，有许多相同的标签多次出现.我对标记IncidentName之后的所有值都感兴趣.

I have a variable that contains a json string which is quite long. Datatype therefore is NVARCHAR(MAX). Within this json string there are many of the same tags occurring multiple times. I am interested in all values AFTER the tag IncidentName.

在JSON中看起来像这样(但随后嵌套在有时存在而有时不存在的变量数组中):

It looks something like this within the JSON (but then nested in variable arrays that sometimes exist and sometimes don't):

"IncidentName":"Value1",
"IncidentName":"Value2",
"IncidentName":"Value3"

有人可以帮助我建立一个返回所有这些值的循环/查询吗?

Could someone help me with building a loop/query that returns all of these values?

推荐答案

您可以尝试创建SQL CLR函数，并使用正则表达式来匹配所需的数据. 在这里，您可以找到什么是SQL CLR集成的详细信息，Mircosoft提供的regex匹配功能示例以及如何创建此类示例的说明.功能.

You can try to create SQL CLR function and using regex expression to match the data you need. Here you can find details of what is SQL CLR integration, examples of regex match function provided by Mircosoft and instructions how to create such functions.

因此，完成上述设置后，您可以执行以下操作:

So, having the above setup, you can do something like this:

DECLARE @data NVARCHAR(MAX) = N'
{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "IncidentName":"Value1",
                    "IncidentName":"Value2",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "IncidentName":"Value3",
                        "GlossSeeAlso": ["GML", "XML"]
                    },
                    "GlossSee": "markup"
                }
            },
        "IncidentName":"Value4"
        }
    }
}';

SELECT *
FROM [dbo].[fn_Utils_RegexMatches] (@data, '(?i)(?<="IncidentName":")[^"]+(?=")');

我正在使用的正则表达式正在执行以下操作:

The regex I am using is doing the following:

• (?i)-不区分大小写的搜索
• (?< ="IncidentName":)-搜索此模式，但此模式不包括在最终捕获值中(零宽度正向后断言)
• [^] +-匹配每个字符的不同形式"
• (?=)-使用零宽度正向超前断言来确保匹配值包含在"
中

• (?i) - case insensitive search
• (?<="IncidentName":") - searching for this pattern, but this pattern is excluded from the final capture value (Zero-width positive lookbehind assertion)
• [^"]+ - matching every character different form "
• (?=") - using Zero-width positive lookahead assertion to ensure the match value is enclosed by "

基本上，您可以使用

Basically, you can use this quick reference to help you build regular expression that is going to work for your case.

困难的部分是了解什么是SQL CLR并实现Microsoft的String Utility Pack.然后，您可以解决使用普通T-SQL很难解决或效率不高的问题.

The hard part is understanding what SQL CLR is and to implement the Microsoft's String Utility Pack. Then, you can solve issues, which are very difficult or not efficient to solve using plain T-SQL.

这篇关于SQL Server:从包含json字符串的nvarchar(max)变量获取所有字符串出现(标记)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！