我需要帮助来读取MSSQL中的JSON文件
我有这样的json格式
{ "_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" } } ] }
我正在尝试使用以下代码读取json。一切正常。但是，当我使用上述格式重复的确切源文件并且具有很多字段时，我只检索一行。
SELECT * FROM OPENROWSET (BULK 'C:\demo_json\claims.json', SINGLE_CLOB) as j CROSS APPLY OPENJSON(BulkColumn)
我搜索了它，发现它是由于多重嵌套的json所致。在下面的代码中使用。
SELECT * FROM OPENROWSET (BULK 'C:\demo_json\claims.json', SINGLE_CLOB) as j CROSS APPLY OPENJSON('['+BulkColumn+']')
但是我遇到了错误： JSON文本格式不正确。 在位置2760处发现意外字符’{‘。
注意：我无法控制或无法编辑源文件JSON我该怎么做才能确保JSON文件的格式正确或使我可以正确读取它。
源文件：声明
看来，您的输入文件包含行，它们是有效的JSON对象。在这种情况下，一种可能的方法是使用BULK INSERT并适当地导入此文件ROWTERMINATOR：
JSON
BULK INSERT
ROWTERMINATOR
文本文件（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"}}}
陈述：
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
笔记：
您可以尝试使用基本的字符串转换来构建有效的JSON数组，但是最好逐行读取文件：
SELECT * FROM OPENROWSET (BULK 'C:\demo_json\claims.json', SINGLE_CLOB) d CROSS APPLY OPENJSON('[' + REPLACE(d.BulkColumn, '}' + CHAR(10) + '{', '},{') + ']') j
