sql - 在 SQL Server 2012 中使用 JSON

Question

我正在从事一个项目，我需要能够从收到的 JSON 中接收和使用(即提取信息)。我当前使用的 SQL Server 版本( future 几年不会更改)是 2012，不包括对此的支持(相对于版本 2016)。

我确实记得看到某人的帖子，他温和地为此提供了源代码，但遗憾的是找不到了。

这个想法是有类似的东西:

具有以下 JSON:

{
    "Obj1": {
        "Obj1_S_1": [{
            "Obj1_S_1_S_1": "Blabla_1"
        }, {
            "Obj1_S_1_S_1": "Blabla_2"
        }, {
            "Obj1_S_1_S_1": "Blabla_3"
        }, {
            "Obj1_S_1_S_1": "Blabla_4"
        }, {
            "Obj1_S_1_S_1": "Blabla_5"
        }, {
            "Obj1_S_1_S_1": "Blabla_6"
        }]
    },
    "Obj2": "This is a simple string",
    "Obj3": "456.33"
}

我可以使用以下调用:

SET @My_Param = GET_JSON(@Source_JSON, '*.Obj1.Obj1_S_1[3].Obj1_S_1_S_1') ;

我会将值 'Blabla_4' 放入变量 @My_Param 中。

顺便说一句，这与 Oracle 和 MySQL 中使用的语法完全相同。

Answer 1

可以通过一些策略性的解析/拆分操作来完成

示例数据

Declare @S varchar(max) ='
{
    "Obj1": {
        "Obj1_S_1": [{
            "Obj1_S_1_S_1": "Blabla_1"
        }, {
            "Obj1_S_1_S_1": "Blabla_2"
        }, {
            "Obj1_S_1_S_1": "Blabla_3"
        }, {
            "Obj1_S_1_S_1": "Blabla_4"
        }, {
            "Obj1_S_1_S_1": "Blabla_5"
        }, {
            "Obj1_S_1_S_1": "Blabla_6"
        }]
    },
    "Obj2": "This is a simple string",
    "Obj3": "456.33"
}
'

示例

--Clean-up JSON String and add '|||' as a standard delimeter
Select @S = Replace(@S,MapFrm,MapTo)
 From (values ('"'     ,'')
             ,(char(13),'|||')
             ,(char(10),'|||')
             ,('}'     ,'|||')
             ,('{'     ,'|||')
             ,('['     ,'|||')
             ,(']'     ,'|||')
       ) b (MapFrm,MapTo)

带有解析/拆分 UDF 的选项

Select Item  = left(RetVal,charindex(':',RetVal+':')-1)
      ,Value = ltrim(right(RetVal,len(RetVal)-charindex(':',RetVal+':')))
 From  [dbo].[udf-Str-Parse](@S,'|||')
 Where Len(IsNull(RetVal,' '))>1
 Order By RetSeq

没有解析/拆分 UDF 的选项

Select Item  = left(RetVal,charindex(':',RetVal+':')-1)
      ,Value = ltrim(right(RetVal,len(RetVal)-charindex(':',RetVal+':')))
 From  (
        Select RetSeq = Row_Number() over (Order By (Select null))
              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
        From  (Select x = Cast('<x>' + replace((Select replace(@S,'|||','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
       ) A
 Where Len(IsNull(RetVal,' '))>1
 Order By RetSeq

都返回

Item            Value
Obj1    
Obj1_S_1    
Obj1_S_1_S_1    Blabla_1
Obj1_S_1_S_1    Blabla_2
Obj1_S_1_S_1    Blabla_3
Obj1_S_1_S_1    Blabla_4
Obj1_S_1_S_1    Blabla_5
Obj1_S_1_S_1    Blabla_6
Obj2            This is a simple string,
Obj3            456.33

UDF(如果需要)

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')