By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * into tbl
from(
values
(1,
'{"result": true,
"data": {
"3271012": {
"taskId": 3271012,
"opl": 1245,
"owner": "name",
"description": "note",
"date": {
"date": "2021-06-25 00:00:00.000000",
"timezone_type": 3,
"timezone": ""
},
"responsible": "responsible name",
"subject": "note"
},
"3261201": {
"taskId": 3261201,
"opl": 1236,
"owner": "name",
"description": "note",
"startDate": {
"date": "2019-08-08 11:46:28.000000",
"timezone_type": 3,
"timezone": ""
},
"responsible": "responsible name",
"subject": "note"
}
}}')
) t(id, jsonstr)
;
1 rows affected
select t.id,
json_value(d.value, '$.taskId') taskid,
json_value(d.value, '$.opl') opl,
json_value(d.value, '$.owner') owner,
cast (json_value(d.value, '$.startDate.date') as datetime2) dt2
from tbl t
cross apply openjson (jsonstr, '$.data') d
| id | taskid | opl | owner | dt2 |
|---|---|---|---|---|
| 1 | 3271012 | 1245 | name | null |
| 1 | 3261201 | 1236 | name | 2019-08-08 11:46:28.0000000 |