Parsing JSON with T-SQL

Question

I'm looking to only select each value name and value in the JSON string and pivot into separate columns in SQL so that I can easily pass these into a powershell string to send to an external API

DECLARE @json NVARCHAR(MAX)

SET @json='{  "InsertRecordData": {  "data": {  "AdditionalData": {  "DataObjects": {  "ObjData": {  "Name": "coll_exclude",  "Fields": {  "FieldData1": {  "Name": "agreement",  "Value": "1234"  },  "FieldData2": {  "Name": "system",  "Value": "live"  },  "FieldData3": {  "Name": "date_added",  "Value": "2019-08-01"  },  "FieldData4": {  "Name": "time_added",  "Value": "11:20"  }  }  }  }  }  }  }  }';

SELECT *
FROM OPENJSON(@json,'$.InsertRecordData.data.AdditionalData.DataObjects.ObjData.Fields')

So id want to see

Agreement   System    Date_added   time_added

1234           live      2019-08-01   11:20

score 1 · Accepted Answer · answered Aug 08 '19 at 08:24

use mssql pivot()

DECLARE @json NVARCHAR(MAX)

SET @json='{  "InsertRecordData": {  "data": {  "AdditionalData": {  "DataObjects": {  "ObjData": {  "Name": "coll_exclude",  "Fields": {  "FieldData1": {  "Name": "agreement",  "Value": "1234"  },  "FieldData2": {  "Name": "system",  "Value": "live"  },  "FieldData3": {  "Name": "date_added",  "Value": "2019-08-01"  },  "FieldData4": {  "Name": "time_added",  "Value": "11:20"  }  }  }  }  }  }  }  }';


select [agreement], [system], [date_added], [time_added] from
(
    SELECT json_value(js.value, '$.Name') as Titles, json_value(js.value, '$.Value') as val
    FROM OPENJSON(@json,'$.InsertRecordData.data.AdditionalData.DataObjects.ObjData.Fields') as js) as SourceTb
PIVOT
(
    max(val)
    FOR Titles in ([agreement], [system], [date_added], [time_added])
) as PivotTable

Parsing JSON with T-SQL

1 Answers1