I've a JSON file content and I want read the content using Open JSON and load it into the SQL. need help in loading the content to SQl.
{
"Header": {
"Time": "2023-11-22T01:39:22-08:00",
"ReportName": "AgedReceivableDetail",
"EndPeriod": "2023-09-30",
"Currency": "USD",
"Option": [
{
"Name": "report_date",
"Value": "2023-09-30"
},
{
"Name": "NoReportData",
"Value": "false"
}
]
},
"Columns": {
"Column": [
{
"ColTitle": "Date",
"ColType": "tx_date"
},
{
"ColTitle": "Transaction Type",
"ColType": "txn_type"
},
{
"ColTitle": "Num",
"ColType": "doc_num"
},
{
"ColTitle": "Customer",
"ColType": "cust_name"
},
{
"ColTitle": "Due Date",
"ColType": "due_date"
},
{
"ColTitle": "Amount",
"ColType": "subt_amount"
},
{
"ColTitle": "Open Balance",
"ColType": "subt_open_bal"
}
]
},
"Rows": {
"Row": [
{
"Header": {
"ColData": [
{
"value": "Current"
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
}
]
},
"Rows": {
"Row": [
{
"ColData": [
{
"value": "2023-08-31"
},
{
"value": "Invoice",
"id": "60"
},
{
"value": "1016"
},
{
"value": "Kookies lts",
"id": "16"
},
{
"value": "2023-09-30"
},
{
"value": "75.00"
},
{
"value": "75.00"
}
],
"type": "Data"
},
{
"ColData": [
{
"value": "2023-09-01"
},
{
"value": "Invoice",
"id": "92"
},
{
"value": "1028"
},
{
"value": "Freeman Twin Lane",
"id": "9"
},
{
"value": "2023-10-01"
},
{
"value": "81.00"
},
{
"value": "81.00"
}
],
"type": "Data"
},
{
"ColData": [
{
"value": "2023-09-01"
},
{
"value": "Invoice",
"id": "75"
},
{
"value": "1027"
},
{
"value": "Bills Pvt",
"id": "2"
},
{
"value": "2023-10-01"
},
{
"value": "85.00"
},
{
"value": "85.00"
}
],
"type": "Data"
},
{
"ColData": [
{
"value": "2023-09-27"
},
{
"value": "Invoice",
"id": "67"
},
{
"value": "1021"
},
{
"value": "Animal Inc Sanctuary",
"id": "1"
},
{
"value": "2023-10-27"
},
{
"value": "459.00"
},
{
"value": "459.00"
}
],
"type": "Data"
}
]
},
"Summary": {
"ColData": [
{
"value": "Total for Current"
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": "1736.00"
},
{
"value": "1631.00"
}
]
},
"type": "Section"
},
{
"Summary": {
"ColData": [
{
"value": "TOTAL"
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": "2279.00"
},
{
"value": "2174.00"
}
]
},
"type": "Section"
}
]
}
}
used the below code to parse and but no luck. please try to help me in processing this JSON file.
DROP TABLE IF EXISTS #TempTable
-- Create a temporary table to store the JSON data
CREATE TABLE #TempTable
(
[Key] NVARCHAR(255),
[Value] NVARCHAR(MAX)
)
-- Insert the JSON data into the temporary table
INSERT INTO #TempTable
VALUES ('$', N'{
"Header": {
"Time": "2023-11-22T01:39:22-08:00",
"ReportName": "ARDetail",
"EndPeriod": "2023-09-30",
"Currency": "USD",
"Option": [
{
"Name": "report_date",
"Value": "2023-09-30"
},
{
"Name": "NoReportData",
"Value": "false"
}
]
},
"Columns": {
"Column": [
{
"ColTitle": "Date",
"ColType": "tx_date"
},
{
"ColTitle": "Transaction Type",
"ColType": "txn_type"
},
{
"ColTitle": "Num",
"ColType": "doc_num"
},
{
"ColTitle": "Customer",
"ColType": "cust_name"
},
{
"ColTitle": "Due Date",
"ColType": "due_date"
},
{
"ColTitle": "Amount",
"ColType": "subt_amount"
},
{
"ColTitle": "Open Balance",
"ColType": "subt_open_bal"
}
]
},
"Rows": {
"Row": [
{
"Header": {
"ColData": [
{
"value": "Current"
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
}
]
},
"Rows": {
"Row": [
{
"ColData": [
{
"value": "2023-08-31"
},
{
"value": "Invoice",
"id": "60"
},
{
"value": "1016"
},
{
"value": "Kookies lts",
"id": "16"
},
{
"value": "2023-09-30"
},
{
"value": "75.00"
},
{
"value": "75.00"
}
],
"type": "Data"
},
{
"ColData": [
{
"value": "2023-09-01"
},
{
"value": "Invoice",
"id": "92"
},
{
"value": "1028"
},
{
"value": "Freeman Twin Lane",
"id": "9"
},
{
"value": "2023-10-01"
},
{
"value": "81.00"
},
{
"value": "81.00"
}
],
"type": "Data"
},
{
"ColData": [
{
"value": "2023-09-01"
},
{
"value": "Invoice",
"id": "75"
},
{
"value": "1027"
},
{
"value": "Bills Pvt",
"id": "2"
},
{
"value": "2023-10-01"
},
{
"value": "85.00"
},
{
"value": "85.00"
}
],
"type": "Data"
},
{
"ColData": [
{
"value": "2023-09-27"
},
{
"value": "Invoice",
"id": "67"
},
{
"value": "1021"
},
{
"value": "Animal Inc Sanctuary",
"id": "1"
},
{
"value": "2023-10-27"
},
{
"value": "459.00"
},
{
"value": "459.00"
}
],
"type": "Data"
}
]
},
"Summary": {
"ColData": [
{
"value": "Total for Current"
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": "1736.00"
},
{
"value": "1631.00"
}
]
},
"type": "Section"
},
{
"Summary": {
"ColData": [
{
"value": "TOTAL"
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": ""
},
{
"value": "2279.00"
},
{
"value": "2174.00"
}
]
},
"type": "Section"
}
]
}
}')
SELECT
[Time]
,ReportName
,EndPeriod
,Currency
,[Option].Value AS ReportDate
,[Option].Value AS NoReportData
,ColumnData.ColTitle
,ColumnData.ColType
,RowData.type AS RowType
FROM
#TempTable
CROSS APPLY
OPENJSON(#TempTable.[Value], '$.Header') WITH
(
Time NVARCHAR(25) '$.Time',
ReportName NVARCHAR(255) '$.ReportName',
EndPeriod NVARCHAR(25) '$.EndPeriod',
Currency NVARCHAR(3) '$.Currency',
[Option] NVARCHAR(MAX) '$.Option' AS JSON
) AS Header
CROSS APPLY
OPENJSON(Header.[Option]) WITH
(
Name NVARCHAR(255) '$.Name',
Value NVARCHAR(MAX) '$.Value'
) AS [Option]
CROSS APPLY
OPENJSON(#TempTable.[Value], '$.Columns') WITH
(
ColumnData NVARCHAR(MAX) '$.Columns.Column' AS JSON
) AS Columns
CROSS APPLY
OPENJSON(ColumnData) WITH
(
ColTitle NVARCHAR(255) '$.ColTitle',
ColType NVARCHAR(50) '$.ColType'
) AS ColumnData
CROSS APPLY
OPENJSON(#TempTable.[Value], '$.Rows') WITH
(
RowData NVARCHAR(MAX) '$.Rows.Row' AS JSON
) AS Rows
CROSS APPLY
OPENJSON(RowData) WITH
(
type NVARCHAR(50) '$.type',
Header NVARCHAR(MAX) '$.Header' AS JSON,
Rows NVARCHAR(MAX) '$.Rows.Row' AS JSON,
Summary NVARCHAR(MAX) '$.Summary' AS JSON
) AS RowData
-- Drop the temporary table
DROP TABLE #TempTable
