import json file
-
Any idea on how to import a json file into sql server? I've never done one before. I saw some example like below but the file I have had columns report for different sections.
SELECT import_data.*
FROM OPENROWSET (BULK 'C:\Files\import_data\small_json.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH( name varchar (255), object_id varchar (255) , schema_id varchar (255) ,
parent_object_id varchar (255) , type varchar (255) , type_desc varchar (255) ,
create_date varchar (255) , modify_date varchar (255), is_ms_shipped varchar (255),
is_published varchar (255) , is_schema_published varchar (255)
) AS import_data
-
There seem to be four separate entities in that file (Company, Demographics, Claims, Procedures).
Is your aim to import the file into four separate tables? If not, what is your aim?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I think a table for each
-
Here is a partial solution which shows you how to separate out the data for each of the entities.
DECLARE @x NVARCHAR(MAX)
= N'{
"Company": {
"CompanyKey": "TrainMH",
"CompanyName": "County MH TEST SITE",
"EIN": "999999999",
"MaNumber": "999999999",
"ServiceLocation": "0033"
},
"Demographics": [
{
"CompanyKey": "TrainMH",
"ClientKey": 1058,
"BSU": "4100000024",
"BirthDate": "1928-01-26T00:00:00",
"Gender": "F",
"Race": "4",
"Ethinicity": "1",
"Language": "English",
"County": "38",
"RelationshipStatus": null,
"VetStatus": null,
"AdmitDate": "2019-05-01T00:00:00",
"CloseDate": null,
"OpenPriorityGroup": null,
"ClosePriorityGroup": null,
"OpenLivingSituation": null,
"CloseLivingSituation": null,
"OpenVocEd": null,
"CloseVocEd": null,
"ReferralSource": null,
"Diag1": "F200",
"Diag2": null,
"Diag3": null,
"EmpCode": null,
"EduCode": null,
"SchoolDistrict": null,
"Pregnant": false,
"SAP": false,
"WomanWithChildren": false,
"EpisodePK": 1052
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"BSU": null,
"BirthDate": "1950-10-02T00:00:00",
"Gender": "F",
"Race": "5",
"Ethinicity": "1",
"Language": null,
"County": "41",
"RelationshipStatus": null,
"VetStatus": null,
"AdmitDate": "2022-08-31T00:00:00",
"CloseDate": null,
"OpenPriorityGroup": null,
"ClosePriorityGroup": null,
"OpenLivingSituation": null,
"CloseLivingSituation": null,
"OpenVocEd": null,
"CloseVocEd": null,
"ReferralSource": null,
"Diag1": "F339",
"Diag2": null,
"Diag3": null,
"EmpCode": null,
"EduCode": null,
"SchoolDistrict": null,
"Pregnant": false,
"SAP": false,
"WomanWithChildren": false,
"EpisodePK": 1093
}
],
"Claims": [
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"ClaimKey": 1180,
"BatchKey": 2060,
"MPI": "100003112",
"ServiceLocation": "0003",
"AddDate": "2023-03-10T09:45:45",
"Diagnosis": "F339",
"ClientCoPay": 0.00,
"NonResidentLiability": 0.00,
"ResidentLiability": 0.00,
"MaPayment": 0.00,
"InsurancePayment": 0.00,
"OtherPayment": 0.00,
"RoomAndBoardPayment": 0.00,
"Payer": "COUNTYMH",
"TotalDue": 0.00
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"ClaimKey": 1175,
"BatchKey": 2058,
"MPI": "100744641",
"ServiceLocation": "0063",
"AddDate": "2023-01-13T10:47:39.347",
"Diagnosis": "F339",
"ClientCoPay": 0.00,
"NonResidentLiability": 0.00,
"ResidentLiability": 0.00,
"MaPayment": 0.00,
"InsurancePayment": 0.00,
"OtherPayment": 0.00,
"RoomAndBoardPayment": 0.00,
"Payer": "COUNTYMH",
"TotalDue": 0.00
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1058,
"ClaimKey": 1096,
"BatchKey": 2032,
"MPI": "100000291",
"ServiceLocation": "0071",
"AddDate": "2021-09-14T10:46:53",
"Diagnosis": "F919",
"ClientCoPay": 0.00,
"NonResidentLiability": 0.00,
"ResidentLiability": 0.00,
"MaPayment": 0.00,
"InsurancePayment": 0.00,
"OtherPayment": 0.00,
"RoomAndBoardPayment": 0.00,
"Payer": "COUNTYMH",
"TotalDue": -125.25
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"ClaimKey": 3200,
"BatchKey": 4068,
"MPI": "100000291",
"ServiceLocation": "0071",
"AddDate": "2023-07-06T11:02:09",
"Diagnosis": "F339",
"ClientCoPay": 0.00,
"NonResidentLiability": 0.00,
"ResidentLiability": 0.00,
"MaPayment": 0.00,
"InsurancePayment": 0.00,
"OtherPayment": 0.00,
"RoomAndBoardPayment": 0.00,
"Payer": "COUNTYMH",
"TotalDue": 133.00
}
],
"Procedures": [
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"ClaimKey": 1175,
"ProcedureCode": "H0043; ; ; ; ; ",
"PlaceOfService": "99",
"FundingType": "PF",
"IsResidential": false,
"CostCenter": "3.22",
"DenialCode": null,
"UnitOfMeasure": "Day",
"PaidUnits": 30,
"Rate": 0.00
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"ClaimKey": 1180,
"ProcedureCode": "H2023; ; HX; ; ; ",
"PlaceOfService": "99",
"FundingType": "PF",
"IsResidential": false,
"CostCenter": "3.12",
"DenialCode": null,
"UnitOfMeasure": "15 Minute",
"PaidUnits": 12,
"Rate": 0.00
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1094,
"ClaimKey": 3200,
"ProcedureCode": "90853; UB; ; ; ; ",
"PlaceOfService": "11",
"FundingType": "FFS",
"IsResidential": false,
"CostCenter": "3.6",
"DenialCode": null,
"UnitOfMeasure": "15 Minute",
"PaidUnits": 7,
"Rate": 19.00
},
{
"CompanyKey": "TrainMH",
"ClientKey": 1058,
"ClaimKey": 1096,
"ProcedureCode": "H0034; ; ; ; ; ",
"PlaceOfService": "49",
"FundingType": "FFS",
"IsResidential": false,
"CostCenter": "3.6",
"DenialCode": null,
"UnitOfMeasure": "15 Minute",
"PaidUnits": 1,
"Rate": 125.25
}
]
}';
SELECT *
FROM
OPENJSON (@x, '$.Company')
WITH
(
CompanyKey VARCHAR (50)
,CompanyName VARCHAR (100)
) Companies;
SELECT *
FROM
OPENJSON (@x, '$.Demographics')
WITH
(
CompanyKey VARCHAR (50)
,ClientKey VARCHAR (100)
) Demographics;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Heh... and people wonder why data storage is exploding. The only good thing about it is that it has only half the tag bloat of XML but, lordy, what a waste. Even an old fashioned set of TSV files with a manifest file would be a lot more efficient here.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply