Parse json array in SQL to extract corresponding value of a specified name

It's not going to be pretty and you'd probably be better off parsing this json in some kind of backend code, e.g. C#, Python, JavaScript, whatever.

Now that you're warned, you're going to want to use a couple of JSON related functions in SQL:

• JSON_VALUE - extract a scalar value from the json object (e.g. the report identifier)
• OPENJSON - open the json object/array as if it was a table

We end up with:

SELECT JSON_VALUE(json, '$.RESPONSE."@ReportIdentifier"') AS ReportID
     , JSON_VALUE(Liability.value, '$."@FileID"') AS FileID
     , Summary.Value AS NumberOfBooks
 FROM so
CROSS
APPLY OPENJSON(json, '$.LIABILITY') AS Liability
CROSS
APPLY OPENJSON(json, '$.SUMMARY."_DATA_SET"') 
      WITH
      (
         Name NVARCHAR(25) '$."@_Name"'
       , Value NVARCHAR(25) '$."@_Value"'
      ) AS Summary
WHERE Summary.name = 'Number of books'

Here you see @ReportIdentifier can be extracted directly from your json object using a straightforward path. Since you want a row per each liability array element, we have to extract that using OPENJSON and then use JSON_VALUE to extract @FileID from each of the elements. Finally with the summary nested object we can't simply extract value because this is a dictionary and we just want the number of books. I don't know whether Number of Books dict entry is always first in your data set, so I opted for translating the whole array into a table and then filter only for the rows where the name is 'Number of Books'.

Here's a working demo on dbfiddle