I have a table calculations with some columns, one of them contains, I assume, a JSON object.

Simplified it looks like this:

I have a table calculations with some columns, one of them contains, I assume, a JSON object.

Simplified it looks like this:

{"ContractNumber":10,
"Comment":"test",
"position":[
    {"Value:":100.0,
        "PosNr":1,
        "PosType":1,
        "Counter":
            {"1":
                {"Name":"",
                "Free":800},
            "2":
                {"Name":"",
                "Free":700}
            }
    },
    {"Value:":30.0,
    "PosNr":2,
    "PosType":2,
    "Counter":
        {"1":
            {"Name":"",
            "Free":10},
        "2":
            {"Name":"",
            "Free":50}
        }
    },
    {"Value:":50.0,
    "PosNr":3,
    "PosType":1,
    "Counter":
        {"1":
            {"Name":"",
            "Free":200},
        "2":
            {"Name":"",
            "Free":100}
        }
    },
    {"Value:":10.0,
    "PosNr":4,
    "PosType":2,
    "Counter":
        {"1":
            {"Name":"",
            "Free":15},
        "2":
            {"Name":"",
            "Free":38}
        }
    }
]}

I should now export everything to an Excel which should contain the columns from the table but also totals from the values in the JSON object.

Simplified:

Since I have never worked with JSON before, I thought to write the data from the JSON objects into temp. tables first and then query from there via SQL, but maybe this is the wrong approach.

How can I split the JSON object into 3 tables with references?

• one with the general values of the calculation
• one with the positions of the calculation
• and one with the counters for the positions

• json
• sql-server
• t-sql

1 Answer 1

You need to parse the JSON content with the appropriate OPENJSON() calls, which depend on the actual structure of the returned tables. The following example statements return part of the expected tables:

Statement for general values:

SELECT c.calculationNo, c.date, j.*
FROM #calculations c
OUTER APPLY (
    SELECT 
        PosType, 
        SUM(Value) AS SumOfValue, 
        SUM(Counter1) AS SumOfCounter1, 
        SUM(Counter2) AS SumOfCounter2
    FROM OPENJSON(c.calculation, '$.position') WITH (
        Value numeric(10, 2) '$."Value:"',
        PosNr int '$.PosNr',
        PosType int '$.PosType',
        Counter1 int '$.Counter."1".Free',
        Counter2 int '$.Counter."2".Free'
    )
    GROUP BY PosType
) j

Statement for the positions of the calculation:

SELECT c.calculationNo, c.date, j.*
FROM #calculations c
OUTER APPLY OPENJSON(c.calculation, '$.position') WITH (
    Value numeric(10, 2) '$."Value:"',
    PosNr int '$.PosNr',
    PosType int '$.PosType'
) j