CODEWITHSUNDEEP
sqlsql-server
Сашко Мицевски
Сашко Мицевски

Reputation: 303

How to filter json data with openjson in SQL Server

I have this 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"
         }
      ]
   }
}

What I want to do is to filter only the loan history data that have role with value 'Kredimarrës', so as a result i will get 3 rows with data 1,2,3

So far, I have this script

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'

Upvotes: 0

Views: 762

Answers (1)

Charlieface
Charlieface

Reputation: 71578

You are overcomplicating this. You can pass a JSON path to OPENJSON to jump directly to $.loanHistory.loans

SELECT *
FROM OPENJSON(@json1, '$.loanHistory.loans')
  WITH (
    role nvarchar(100),
    type int
  ) h
WHERE h.role = N'Kredimarrës';

db<>fiddle

Upvotes: 1

Related Questions