CODEWITHSUNDEEP
sqlarraysjsonsql-servernested
Villapando Cedric
Villapando Cedric

Reputation: 309

Reading file containing multiple JSON objects using MS SQL Server

I need help reading JSON file in MSSQL

I have a json format like this

{ 
   "_id":{ 
      "$oid":"5c6ceb395916c77f71d9f531"
   },
   "uuid":"8337df01-7d98-4cdd-b5eb-7fafa88d3740",
   "firstName":"TESTFN",
   "lastName":"TESTLN",
   "middleName":"TESTMN",
   "gender":"MALE",
   "nationality":"",
   "mobileNumber":"",
   "card":"TACC-00001",
   "claims":{ 
      "$numberLong":"1"
   },
   "hisId":{ 
      "$numberLong":"0"
   },
   "hospitals":[ 
      { 
         "$ref":"hospital",
         "$id":{ 
            "$oid":"5bd80e4e36ec8c600f3e750a"
         }
      }
   ]
}

I'm trying to read json using below code. It is working fine. But when I use the exact source file which is repeated of above format and has a lot of fields, I'm only retrieving one row.

SELECT *
 FROM OPENROWSET (BULK 'C:\demo_json\claims.json', SINGLE_CLOB) as j
 CROSS APPLY OPENJSON(BulkColumn)

I've searched it and found out it is because of multi nested json. Used below code..

SELECT *
 FROM OPENROWSET (BULK 'C:\demo_json\claims.json', SINGLE_CLOB) as j
 CROSS APPLY OPENJSON('['+BulkColumn+']')

But I'm getting error: JSON text is not properly formatted. Unexpected character '{' is found at position 2760.

Note: I have no control or cant edit the source file JSON What can I do to make sure JSON file is in correct format or make it correct for me to read it.

Source file : claims

Upvotes: 0

Views: 870

Answers (1)

Zhorov
Zhorov

Reputation: 29963

It seems, that your input file has lines, which are valid JSON objects. One possible approach in this situation is to import this file using BULK INSERT and appropriate ROWTERMINATOR:

Text file (claims.json):

{"_id":{"$oid":"5c6ceb395916c77f71d9f536"},"_class":"stash.ph.data.model.mongo.claims.Claims","uuid":"02735305-220e-4399-9217-0b99939797d2","submittedAt":{"$date":{"$numberLong":"1550641993123"}},"submittedByUuid":"c60d326e-0893-4718-a0e0-f64ac697dd2e","status":"SUBMITTED","billingDesignation":"Hospital","createdAt":{"$date":{"$numberLong":"1550641977093"}},"updatedAt":{"$date":{"$numberLong":"1550641977093"}},"particulars":[{"_id":{"$oid":"5c6ceb395916c77f71d9f530"},"uuid":"b2b3cc59-b9a8-49f1-8f92-30021d2c7abb","particular":"ER_CONSULTATION","procedure":[],"doctors":[{"_id":{"$oid":"5c6ceb395916c77f71d9f52f"},"uuid":"494e1bf9-a3f9-4c3a-bdc0-3ef15b4760a2","type":"PRIMARY","professionalFee":{"$numberDouble":"500.0"},"doctor":{"$ref":"doctor","$id":{"$oid":"5bd8156736ec8c600f3e7604"}}}]}],"patient":{"_id":{"$oid":"5c6ceb395916c77f71d9f531"},"uuid":"8337df01-7d98-4cdd-b5eb-7fafa88d3740","firstName":"TESTFN","email":"","lastName":"TESTLN","middleName":"TESTMN","suffix":"","prefix":"","birthday":"","age":"","gender":"MALE","nationality":"","mobileNumber":"","card":"TACC-00001","cardExpiration":"","company":"","claims":{"$numberLong":"1"},"phicNo":"","hisId":{"$numberLong":"0"},"hospitals":[{"$ref":"hospital","$id":{"$oid":"5bd80e4e36ec8c600f3e750a"}}],"hmos":[]},"files":[{"_id":{"$oid":"5c6ceb395916c77f71d9f533"},"uuid":"8731cb71-e424-4147-8158-38558df56ad5","filename":"8731cb71-e424-4147-8158-38558df56ad5.pdf","description":"files desc"}],"medicalData":{"_id":{"$oid":"5c6ceb395916c77f71d9f534"},"uuid":"617e193b-2280-491b-b34f-adf6eaeb2244","complain":[""],"diagnosis":["A90 | Dengue fever [classical dengue]"],"remarks":"","dateExamined":{"$date":{"$numberLong":"1546272000000"}}},"loa":{"_id":null,"uuid":"b4bc96d1-554b-4cc7-9511-c56d325cf5de","referenceId":""},"payment":{"_id":{"$oid":"5c6ceb395916c77f71d9f535"},"uuid":"9714fdf1-311f-4898-8070-f3fd1ddd0658","phicBill":{"$numberDouble":"0.0"},"hospitalBill":{"$numberDouble":"0.0"},"total":{"$numberDouble":"500.0"},"balance":{"$numberDouble":"500.0"},"createdAt":{"$date":{"$numberLong":"1550641977101"}},"updatedAt":{"$date":{"$numberLong":"1550641977101"}}},"hmoRepresentative":"","remarks":"","registryNumber":{"$numberLong":"0"},"soaNumber":"MBC-MCD-OUT-190000001","soaUuid":"2366abe1-672b-456b-bfd9-5584bf9e7f5a","batchName":"BATCH-190000001","claimLogs":[{"_id":{"$oid":"5c6ceb395916c77f71d9f532"},"uuid":"e2079956-0fbd-4565-9a93-0791a898fbcd","status":"PENDING","date":{"$date":{"$numberLong":"1550641977093"}},"userUuid":"c60d326e-0893-4718-a0e0-f64ac697dd2e","firstName":"Medicard","middleName":"","lastName":"Hospital"}],"messages":[],"hospital":{"$ref":"hospital","$id":{"$oid":"5bd80e4e36ec8c600f3e750a"}},"hmo":{"$ref":"hmo","$id":{"$oid":"598615f970d8a672a291132e"}}}
{"_id":{"$oid":"5c6ceda95916c77f71d9f574"},"_class":"stash.ph.data.model.mongo.claims.Claims","uuid":"73e6627e-187a-4f9f-a141-e4e01666c7c4","submittedAt":{"$date":{"$numberLong":"1550642786142"}},"submittedByUuid":"c60d326e-0893-4718-a0e0-f64ac697dd2e","status":"RETURN","billingDesignation":"Hospital","createdAt":{"$date":{"$numberLong":"1550642601507"}},"updatedAt":{"$date":{"$numberLong":"1550642601507"}},"particulars":[{"_id":{"$oid":"5c6ceda95916c77f71d9f56e"},"uuid":"8c774847-e0ca-4732-9669-6bf6a4579cc6","particular":"ER_CONSULTATION","procedure":[],"doctors":[{"_id":{"$oid":"5c6ceda95916c77f71d9f56d"},"uuid":"0520ffdf-ceb9-4413-8cec-c5eb728b8972","type":"PRIMARY","professionalFee":{"$numberDouble":"500.0"},"doctor":{"$ref":"doctor","$id":{"$oid":"5bd8156736ec8c600f3e7604"}}}]}],"patient":{"_id":{"$oid":"5c6ceda95916c77f71d9f56f"},"uuid":"e71116f6-710e-4e13-b806-c88cf172f5ca","firstName":"FGSDF","email":"","lastName":"FDGDS","middleName":"FGHDSF","suffix":"","prefix":"","birthday":"","age":"","gender":"MALE","nationality":"","mobileNumber":"","card":"TACC-00002","cardExpiration":"","company":"","claims":{"$numberLong":"1"},"phicNo":"","hisId":{"$numberLong":"0"},"hospitals":[{"$ref":"hospital","$id":{"$oid":"5bd80e4e36ec8c600f3e750a"}}],"hmos":[]},"files":[{"_id":{"$oid":"5c6ceda95916c77f71d9f571"},"uuid":"cc53c964-9440-413e-87b6-24fa86ccaa39","filename":"cc53c964-9440-413e-87b6-24fa86ccaa39.pdf","description":"files desc"}],"medicalData":{"_id":{"$oid":"5c6ceda95916c77f71d9f572"},"uuid":"d683a97b-f440-45a3-adef-b3df81be2fdc","complain":[""],"diagnosis":["A90 | Dengue fever [classical dengue]"],"remarks":"","dateExamined":{"$date":{"$numberLong":"1546358400000"}}},"loa":{"_id":null,"uuid":"3e929137-44c3-45f0-8cb5-fef1d83ff866","referenceId":""},"payment":{"_id":{"$oid":"5c6ceda95916c77f71d9f573"},"uuid":"6b0ed0d3-2993-4605-b4cd-421d84656921","phicBill":{"$numberDouble":"0.0"},"hospitalBill":{"$numberDouble":"0.0"},"total":{"$numberDouble":"500.0"},"balance":{"$numberDouble":"500.0"},"createdAt":{"$date":{"$numberLong":"1550642601512"}},"updatedAt":{"$date":{"$numberLong":"1550642601512"}}},"hmoRepresentative":"","remarks":"lack of documents","registryNumber":{"$numberLong":"0"},"approvedByUuid":"6fe1a2df-2a87-4b1e-a9b7-85a8b46eec19","soaNumber":"MBC-ILE-OUT-190000001","soaUuid":"6198678b-4254-4c89-a3d1-54e0366e751c","batchName":"BATCH-190000002","claimLogs":[{"_id":{"$oid":"5c6ceda95916c77f71d9f570"},"uuid":"96077dba-166b-47bd-a8d9-b06bee38e763","status":"PENDING","date":{"$date":{"$numberLong":"1550642601507"}},"userUuid":"c60d326e-0893-4718-a0e0-f64ac697dd2e","firstName":"Medicard","middleName":"","lastName":"Hospital"}],"messages":[],"hospital":{"$ref":"hospital","$id":{"$oid":"5bd80e4e36ec8c600f3e750a"}},"hmo":{"$ref":"hmo","$id":{"$oid":"5985c32870d8a672a291129d"}}}

Statement:

CREATE TABLE #Data (
    BulkColumn nvarchar(max)
)
BULK INSERT #Data 
FROM 'C:\demo_json\claims.json'
WITH (ROWTERMINATOR = '0x0A')

SELECT *
FROM #Data d
CROSS APPLY OPENJSON(d.BulkColumn) j

Notes:

You may try to use basic string transformations to build a valid JSON array, but reading the file line by line should be your first option:

SELECT *
FROM OPENROWSET (BULK 'C:\demo_json\claims.json', SINGLE_CLOB) d
CROSS APPLY OPENJSON('[' + REPLACE(d.BulkColumn, '}' + CHAR(10) + '{', '},{') + ']') j

Upvotes: 1

Related Questions