Reading file containing multiple JSON objects using MS SQL Server

Question

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

Can you post an example for this "... source file which is repeated of above format ..."? Thanks. — Zhorov
– Zhorov, Commented Nov 11, 2019 at 13:06
The above format is wrapped with {} so is an object serialized to JSON. Why would you expect more than one row? — AlwaysLearning
– AlwaysLearning, Commented Nov 11, 2019 at 13:11
@AlwaysLearning I used above format as just a sample. I have edited the post to include the source file. — Villapando Cedric
– Villapando Cedric, Commented Nov 11, 2019 at 13:14
@VillapandoCedric You should consider this only as a test solution. Your lines are separeted by LF, so CROSS APPLY OPENJSON('[' + REPLACE(d.BulkColumn, '}' + CHAR(10) + '{', '},{') + ']') may help here. But the real question is why your file is constructed this way? — Zhorov
– Zhorov, Commented Nov 11, 2019 at 13:27

Zhorov · Accepted Answer · 2020-01-13 12:21:12Z

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

Collectives™ on Stack Overflow

Reading file containing multiple JSON objects using MS SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related