Multiple Json Objects Imported into SQL Server Table

» json » Multiple Json Objects Imported into SQL Server Table

By : sckint

Date : November 26 2020, 06:23 AM

like below fixes the issue You need an additional OPENJSON call with explicit schema to get data from nested JSON objects.
JSON:

code :

DECLARE @json nvarchar(max) = N'{
   "currency": {
    "0": {
      "ISOCode": "USD",
      "name": "US Dollar",
      "symbol": "$"
    },
    "1": {
      "ISOCode": "IEP",
      "name": "Irish Pound or Punt",
      "symbol": "£"
    },
    "2": {
      "ISOCode": "BEF",
      "name": "Belgian Franc",
      "symbol": "₣"
    },
    "3": {
      "ISOCode": "ZAR",
      "name": "South African Rand",
      "symbol": "R"
    },
    "4": {
      "ISOCode": "JPY",
      "name": "Japanese Yen",
      "symbol": "¥"
    }
   }
}'

SELECT j2.*
FROM OPENJSON(@json, '$.currency') j1
CROSS APPLY OPENJSON(j1.[value]) WITH (
    ISOCode NVARCHAR(10),
    name NVARCHAR(50),
    symbol NVARCHAR(50),
    DefaultCurrencyCode INT
) j2

-------------------------------------------------------
ISOCode name                symbol  DefaultCurrencyCode
-------------------------------------------------------
USD     US Dollar           $   
IEP     Irish Pound or Punt £   
BEF     Belgian Franc       ₣   
ZAR     South African Rand  R   
JPY     Japanese Yen        ¥

Share :

send multiple json objects to http server

By : user3913994

Date : March 29 2020, 07:55 AM

help you fix your problem You should send json objects one after other. When you receive the response from server for one json object,then send the next json object. Sending all the data in one go is not a good solution.

Drools - Decision table with multiple imported objects of same type

By : Darian Yaw

Date : March 29 2020, 07:55 AM

I hope this helps . Using the very same class for two rather different entities isn't good design. The good and the bad do have several properties in common: name, age and so on. But the flag alreadyConvicted should be reserved for offenders only.
For best results, consider subclassing Person:

code :

class Offender extends Person {
    private boolean alreadyConvicted;
    //...
}
class Guardian extends Person {
}

SQL Server 2016 select where in json array of objects with normal table

By : user3025340

Date : March 29 2020, 07:55 AM

this will help You need to use OPENJSON() with explicit schema definition and appropriate joins:
Tables:

code :

CREATE TABLE Table1 (
   product_category nvarchar(50),
   product_name nvarchar(50),    
   product_id int,  
   product_cost int
)
INSERT INTO Table1
   (product_category, product_name, product_id, product_cost)
VALUES   
   (N'Gift'        , N'Glass', 157, 85),
   (N'Electronics' , N'Bulb' , 833, 218),
   (N'Kitchen'     , N'Glass', 157, 75)
CREATE TABLE Table2 (
   Order_ID nvarchar(100),
   Pincode int,
   Order_details nvarchar(max),
   Email nvarchar(100)
)
INSERT INTO Table2
   (Order_ID, Pincode, Order_details, Email)
VALUES
   (
   N'HASDUI2N342',
   766815,
   N'{
  "order_date": "26-07-2019",
  "Items": [
    {
      "product_category": "Gift",
      "product_id": "157"
    },
    {
      "product_category": "Electronics",
      "product_id": "833"
    }
  ],
  "amount_paid": 333,
  "shipping" :  30
   }',
   N'user1@domain.com'
   )

SELECT 
   t2.Order_ID, t2.Pincode, t2.Email,
   t1.product_name, t1.product_cost
FROM Table2 t2
CROSS APPLY OPENJSON(t2.Order_details, '$.Items') WITH (
   product_id nvarchar(100) '$.product_id',
   product_category nvarchar(100) '$.product_category'
) j
LEFT JOIN Table1 t1 ON (j.product_id = t1.product_id) AND (j.product_category = t1.product_category)

Order_ID    Pincode Email               product_name    product_cost
HASDUI2N342 766815  user1@domain.com    Glass           85
HASDUI2N342 766815  user1@domain.com    Bulb            218

Reading file containing multiple JSON objects using MS SQL Server

By : taiw

Date : March 29 2020, 07:55 AM

To fix this issue 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):

code :

{"_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

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

How do i create a table from a json file with multiple objects?

By : Xabe

Date : March 29 2020, 07:55 AM

I hope this helps you . If you have nested objects like the one in the pastebin, you can use the inbuilt object methods like Object.keys(), Object.values() and Object.entries() to loop through the response object you are handling.