How to parse the nested Json into a MS SQL Table? – Asp.net

It is SQL server, right? Although JSON capabilities of SQL server is not much (compared to say postgreSQL), still you can use OpenJson(), Json_value(). ie:

SELECT pd.pn,
       pd.phid,
       pd.gid,
       pd.pnum,
       pd.punit,
       pd.pyear,
       pd.psuffix,
       pd.incedption,
       pd.expires,
       pd.plantype,
       PayrollId,
       PayrollStatus,
       StartDate,
       EndDate,
       DueDate
FROM
    OPENJSON(@json)
    WITH
    (
        PolicyPayrolls NVARCHAR(MAX) AS JSON
    ) t
    CROSS APPLY
    OPENJSON(t.PolicyPayrolls)
    WITH
    (
        PolicyDetails NVARCHAR(MAX) AS JSON,
        PayrollDetails NVARCHAR(MAX) AS JSON
    ) pp
    CROSS APPLY
(
    SELECT JSON_VALUE(pp.PolicyDetails, '$.PolicyName'),
           JSON_VALUE(pp.PolicyDetails, '$.PolicyHeaderId'),
           JSON_VALUE(pp.PolicyDetails, '$.GroupIdn'),
           JSON_VALUE(pp.PolicyDetails, '$.PolicyNumber'),
           JSON_VALUE(pp.PolicyDetails, '$.PolicyUnitNumber'),
           JSON_VALUE(pp.PolicyDetails, '$.PolicyYearDate'),
           JSON_VALUE(pp.PolicyDetails, '$.PolicySuffixCode'),
           JSON_VALUE(pp.PolicyDetails, '$.InceptionDate'),
           JSON_VALUE(pp.PolicyDetails, '$.ExpirationnDate'),
           JSON_VALUE(pp.PolicyDetails, '$.BillPlanType')
) pd(pn, phid, gid, pnum, punit, pyear, psuffix, incedption, expires, plantype)
    CROSS APPLY
    OPENJSON(pp.PayrollDetails)
    WITH
    (
        PayrollId INT,
        PayrollStatus VARCHAR(20),
        StartDate DATE,
        EndDate DATE,
        DueDate DATE
    );

DBFiddle demo is here

EDIT: I didn’t notice C# there before. If you would do this in C# then it would be much easier really. You could create your matching classes and deserialize with it or use anonymous deseriliaztion with newtonsoft. ie:

var policies = JsonConvert.DeserializeAnonymousType(json, new
{
    PolicyPayrolls = new[] {
    new {
            PolicyDetails = new {
            PolicyName="",
            PolicyHeaderId=0,
            GroupIdn="",
            PolicyNumber=0,
            PolicyUnitNumber=0,
            PolicyYearDate=0,
            PolicySuffixCode="",
            InceptionDate=DateTime.Now,
            ExpirationnDate=DateTime.Now,
            BillPlanType=""
        },
    PayrollDetails = new[] {
            new {PayrollId=0,
           PayrollStatus="",
           StartDate=DateTime.Now,
           EndDate=DateTime.Now,
           DueDate=DateTime.Now}
        }
    }
    }
});

Result looks like:

EDIT2: I think it is like this:

You have a json file (or REST API result) that looks like the one in question but with many more items (2000+) ?
And also a table matching the create table definition on the question
You want the content of json be inserted into that table?
And you want to insert from within C#.

Then:

Assuming databasename is SampleDb on server localhost, tablename is
tmpPOLICY_OUTSTANDING and json comes from a file on
"d:tempinsurance.json"

void Main()
{
    var sql = @"INSERT INTO dbo.tmpPOLICY_OUTSTANDING(PolicyYear, PolicySuffix, PolicyUnitNumber, PolicyGroupId, 
    InceptionDate, ExpirationDate, PolicyNumber)
SELECT pd.PolicyYearDate, pd.PolicySuffixCode, pd.PolicyUnitNumber, pd.GroupIdn,
       pd.InceptionDate, pd.ExpirationnDate,pd.PolicyNumber
FROM
    OPENJSON(@json) 
        WITH(PolicyPayrolls NVARCHAR(MAX) AS JSON) t
    CROSS APPLY OPENJSON(t.PolicyPayrolls)
        WITH(PolicyDetails NVARCHAR(MAX) AS JSON, PayrollDetails NVARCHAR(MAX) AS JSON) pp
    CROSS APPLY OPENJSON(pp.PolicyDetails)
        WITH(PolicyName VARCHAR(50), PolicyHeaderId VARCHAR(50), GroupIdn VARCHAR(50), 
        PolicyUnitNumber VARCHAR(50), PolicyNumber INT, PolicyYearDate VARCHAR(10), 
        PolicySuffixCode VARCHAR(50), InceptionDate DATE, ExpirationnDate DATE, BillPlanType VARCHAR(10)) pd
    CROSS APPLY OPENJSON(pp.PayrollDetails)
    WITH(PayrollId INT, PayrollStatus VARCHAR(20), StartDate DATE, EndDate DATE, DueDate DATE);
";
    using (SqlConnection cn = new SqlConnection("server=.;Database=SampleDb;Trusted_Connection=yes"))
    using (SqlCommand cmd = new SqlCommand(sql,cn))
    {
        cmd.Parameters.Add("@json", SqlDbType.VarChar).Value = File.ReadAllText(@"d:tempinsurance.json");
        
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
    Console.WriteLine("Done");
}