如何在SQL Server中使用OPENJSON过滤JSON数据

原始JSON数据 #

{
   "personalLoans":{
      "loans":[]
   },
   "loanHistory":{
      "loans":[
         {
            "role":"Kredimarrës",
            "type":"1"
         },
         {
            "role":"Kredimarrës",
            "type":"2"
         },
         {
            "role":"Kredimarrës",
            "type":"3"
         },
         {
            "role":"Tjetër",
            "type":"4"
         }
      ]
   }
}

需求说明 #

需要过滤出loanHistory下loans数组中role值为Kredimarrës的记录，最终得到包含type为1、2、3的3行数据。

现有SQL脚本 #

SELECT *
FROM OPENJSON(@json1)
     WITH (personalLoans NVARCHAR(MAX) '$.personalLoans' AS JSON,
           loanHistory NVARCHAR(MAX) '$.loanHistory' AS JSON)
OUTER APPLY OPENJSON(loanHistory)
      WITH (hloans NVARCHAR(max) '$.loans' AS JSON)
OUTER APPLY OPENJSON(hloans)
      WITH (hrole NVARCHAR(max) '$.role')
WHERE hrole  = 'Kredimarrës'

优化后的SQL脚本 #

现有脚本未获取目标字段type，且嵌套调用可简化。直接定位到$.loanHistory.loans数组，同时提取role和type字段后过滤即可：

SELECT type
FROM OPENJSON(@json1, '$.loanHistory.loans')
WITH (
    role NVARCHAR(MAX) '$.role',
    type NVARCHAR(MAX) '$.type'
)
WHERE role = 'Kredimarrës'

执行后会返回符合条件的3条记录，对应type值为1、2、3。

内容的提问来源于stack exchange，提问作者Сашко Мицевски