json converted into SQl Server Table

Question

Hi,
I have json below
{"id":"70212255+2020-12-15+M+1608033319","CustomerId":"70212255","TypeId":"CallFinish","ActionTakenAt":"2020-12-15T18:55:19Z","RouteId":"VN170001","UserId":"6dd1c809-590d-4118-aa7f-331d53b18502","Location":{"Latitude":10.8575102,"Longitude":106.7853094,"Accuracy":15.2,"TimeCaptured":"2020-12-15T11:52:01Z"},"Details":{"IsPlannedCall":false,"CallScheduleId":"70212255+2020-12-15+M","CallType":"M","CallScheduleDate":"2020-12-15"},"PhoneCallReasonId":null}

I need to extract this json in SQL Server Table.

Please help

Answer 1

Please try the following. You may need to adjust data types.

SQL

DECLARE @JSON NVARCHAR(max) =
'{
 "id": "70212255+2020-12-15+M+1608033319",
 "CustomerId": "70212255",
 "TypeId": "CallFinish",
 "ActionTakenAt": "2020-12-15T18:55:19Z",
 "RouteId": "VN170001",
 "UserId": "6dd1c809-590d-4118-aa7f-331d53b18502",
 "Location": {
 "Latitude": 10.8575102,
 "Longitude": 106.7853094,
 "Accuracy": 15.2,
 "TimeCaptured": "2020-12-15T11:52:01Z"
 },
 "Details": {
 "IsPlannedCall": false,
 "CallScheduleId": "70212255+2020-12-15+M",
 "CallType": "M",
 "CallScheduleDate": "2020-12-15"
 },
 "PhoneCallReasonId": null
}';

-- just to see
SELECT *
FROM OPENJSON (@JSON);

-- real deal
SELECT report.*
FROM OPENJSON(@JSON)
WITH 
(
    [id] varchar(100) '$.id'
    , [CustomerId] varchar(20) '$.CustomerId'
    , [TypeId] varchar(100)  '$.TypeId' 
    , [ActionTakenAt] DATETIMEOFFSET(3)   '$.ActionTakenAt'
    , [RouteId] varchar(20) '$.RouteId'
    , [UserId] nvarchar(100) '$.UserId'
    , [Latitude] nvarchar(100)    '$.Location.Latitude'
    , [Longitude] nvarchar(100)    '$.Location.Longitude'
    , Accuracy nvarchar(100) '$.Location.Accuracy'
    , TimeCaptured DATETIMEOFFSET(3) '$.Location.TimeCaptured'
    , IsPlannedCall BIT '$.Details.IsPlannedCall'
    , CallScheduleId nvarchar(100)      '$.Details.CallScheduleId'
    , CallType char(1)  '$.Details.CallType'
    , CallScheduleDate DATE '$.Details.CallScheduleDate'
    , PhoneCallReasonId nvarchar(100)     '$.Details.PhoneCallReasonId'
) AS report;