JSON column parse in SQL

one of the columns in my database contains an imported JSON file.

Format of JSON:

"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",

There are hundreds of objects with taskId (3271012,3261201,…) how can i parse this tasks from one column to multiple rows?

expected output:

Answer

First you can get all the “data” items as key-value pairs with OPENJSON and then extract the values of interest

DECLARE @s varchar(max) = 
'{"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"
     }
 }}';

select 
     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 openjson (@s, '$.data') d; 

The same for the table tbl(id,jsonstr)

 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;

db<>fiddle