By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS #TEMP2;
CREATE TABLE #TEMP2 (
ID INT,
JSON_COLUMN VARCHAR(MAX)
);
INSERT INTO #TEMP2 VALUES (
1, N'[{"loanBalance": 100000.0, "balancePeriod": "2022-02-28T00:00:00+02:00", "guaranteeBalance": 75000.0}, {"chargeId": "21671", "loanBalance": 100000.0, "balancePeriod": "2022-03-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-05-09T00:00:00+03:00"}, {"chargeId": "23678", "loanBalance": 100000.0, "balancePeriod": "2022-04-30T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2022-06-03T00:00:00+03:00"}, {"chargeId": "26077", "loanBalance": 100000.0, "balancePeriod": "2022-05-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-08-06T00:00:00+03:00"}, {"chargeId": "26956", "loanBalance": 100000.0, "balancePeriod": "2022-06-30T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2022-08-12T00:00:00+03:00"}, {"chargeId": "32760", "loanBalance": 100000.0, "balancePeriod": "2022-07-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-11-20T00:00:00+02:00"}, {"chargeId": "33605", "loanBalance": 100000.0, "balancePeriod": "2022-08-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-12-01T00:00:00+02:00"}, {"chargeId": "36010", "loanBalance": 100000.0, "balancePeriod": "2022-09-30T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2023-01-15T00:00:00+02:00"}, {"chargeId": "37025", "loanBalance": 100000.0, "balancePeriod": "2022-10-31T00:00:00+02:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2023-02-10T00:00:00+02:00"}, {"chargeId": "37032", "loanBalance": 100000.0, "balancePeriod": "2022-11-30T00:00:00+02:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2023-02-10T00:00:00+02:00"}, {"chargeId": "37037", "loanBalance": 100000.0, "balancePeriod": "2022-12-31T00:00:00+02:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2023-02-10T00:00:00+02:00"}]'
)
;
INSERT INTO #TEMP2 VALUES (
2, N'[{"loanBalance": 14442.72, "balancePeriod": "2022-02-28T00:00:00+02:00", "guaranteeBalance": 11554.18}, {"chargeId": "21330", "loanBalance": 13359.71, "balancePeriod": "2022-03-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 22.41, "guaranteeFeeDueDate": "2022-04-24T00:00:00+03:00"}, {"chargeId": "23466", "loanBalance": 13223.71, "balancePeriod": "2022-04-30T00:00:00+03:00", "guaranteeBalance": 10578.97, "guaranteeFeeAmount": 20.74, "guaranteeFeeDueDate": "2022-06-02T00:00:00+03:00"}, {"chargeId": "26511", "loanBalance": 13359.71, "balancePeriod": "2022-05-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 21.43, "guaranteeFeeDueDate": "2022-08-11T00:00:00+03:00"}, {"chargeId": "31054", "loanBalance": 13359.71, "balancePeriod": "2022-06-30T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 20.84, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "31068", "loanBalance": 13359.71, "balancePeriod": "2022-07-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 21.54, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "31073", "loanBalance": 13359.71, "balancePeriod": "2022-08-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 21.54, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "31075", "loanBalance": 13359.71, "balancePeriod": "2022-09-30T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 20.84, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "37752", "loanBalance": "11903.33", "balancePeriod": "2022-10-31T00:00:00+02:00", "guaranteeBalance": "9522.66400000", "guaranteeFeeAmount": "23.8426", "guaranteeFeeDueDate": "2023-02-13T00:00:00+02:00"}]'
)
;
SELECT ELEMENTS.loanBalance
, ELEMENTS.balancePeriod
, ELEMENTS.guaranteeBalance
, ELEMENTS.chargeId
, ELEMENTS.guaranteeFeeAmount
, ELEMENTS.guaranteeFeeDueDate
FROM
#TEMP2 T
CROSS APPLY
OPENJSON(T.JSON_COLUMN) WITH (
loanBalance NVARCHAR(255),
balancePeriod NVARCHAR(255),
guaranteeBalance NVARCHAR(255),
chargeId nvarchar(100),
| loanBalance | balancePeriod | guaranteeBalance | chargeId | guaranteeFeeAmount | guaranteeFeeDueDate |
|---|---|---|---|---|---|
| 100000.0 | 2022-02-28T00:00:00+02:00 | 75000.0 | null | null | null |
| 100000.0 | 2022-03-31T00:00:00+03:00 | 75000.0 | 21671 | 142.08 | 2022-05-09T00:00:00+03:00 |
| 100000.0 | 2022-04-30T00:00:00+03:00 | 75000.0 | 23678 | 137.5 | 2022-06-03T00:00:00+03:00 |
| 100000.0 | 2022-05-31T00:00:00+03:00 | 75000.0 | 26077 | 142.08 | 2022-08-06T00:00:00+03:00 |
| 100000.0 | 2022-06-30T00:00:00+03:00 | 75000.0 | 26956 | 137.5 | 2022-08-12T00:00:00+03:00 |
| 100000.0 | 2022-07-31T00:00:00+03:00 | 75000.0 | 32760 | 142.08 | 2022-11-20T00:00:00+02:00 |
| 100000.0 | 2022-08-31T00:00:00+03:00 | 75000.0 | 33605 | 142.08 | 2022-12-01T00:00:00+02:00 |
| 100000.0 | 2022-09-30T00:00:00+03:00 | 75000.0 | 36010 | 137.5 | 2023-01-15T00:00:00+02:00 |
| 100000.0 | 2022-10-31T00:00:00+02:00 | 75000.0 | 37025 | 142.08 | 2023-02-10T00:00:00+02:00 |
| 100000.0 | 2022-11-30T00:00:00+02:00 | 75000.0 | 37032 | 137.5 | 2023-02-10T00:00:00+02:00 |
| 100000.0 | 2022-12-31T00:00:00+02:00 | 75000.0 | 37037 | 142.08 | 2023-02-10T00:00:00+02:00 |
| 14442.72 | 2022-02-28T00:00:00+02:00 | 11554.18 | null | null | null |
| 13359.71 | 2022-03-31T00:00:00+03:00 | 10687.77 | 21330 | 22.41 | 2022-04-24T00:00:00+03:00 |
| 13223.71 | 2022-04-30T00:00:00+03:00 | 10578.97 | 23466 | 20.74 | 2022-06-02T00:00:00+03:00 |
| 13359.71 | 2022-05-31T00:00:00+03:00 | 10687.77 | 26511 | 21.43 | 2022-08-11T00:00:00+03:00 |
| 13359.71 | 2022-06-30T00:00:00+03:00 | 10687.77 | 31054 | 20.84 | 2022-10-17T00:00:00+03:00 |
| 13359.71 | 2022-07-31T00:00:00+03:00 | 10687.77 | 31068 | 21.54 | 2022-10-17T00:00:00+03:00 |
| 13359.71 | 2022-08-31T00:00:00+03:00 | 10687.77 | 31073 | 21.54 | 2022-10-17T00:00:00+03:00 |
| 13359.71 | 2022-09-30T00:00:00+03:00 | 10687.77 | 31075 | 20.84 | 2022-10-17T00:00:00+03:00 |
| 11903.33 | 2022-10-31T00:00:00+02:00 | 9522.66400000 | 37752 | 23.8426 | 2023-02-13T00:00:00+02:00 |