logo
down
shadow

Multiple Json Objects Imported into SQL Server Table


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 : facebook icon twitter icon
send multiple json objects to http server

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

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

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

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?

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.
Related Posts Related Posts :
  • how to write r.headers from different urls into one json?
  • Parsing JSON concurrently - panic of runtime error (decoding related)
  • How to fix "Subscript out of range" error in XML HTTP Request
  • Splitting nested arrays as separate entities
  • Trying to parse the JSON and create an extracted JSON
  • NiFi JoltTransformJson outputs only first record
  • Dynamic (But known as another key value) JSON Decoding with Swift Decodable
  • How to use map on json response returned by a REST API with ReactJs
  • How to read a file line by line and then output all lines compounded with sth in jq?
  • java.lang.RuntimeException: Failed : HTTP error code : 400 : Bad Request
  • How to check jq result is null or not?
  • CloudFormation - How to use Sub intrinsic function?
  • VBA: Nested JSON Object Structure for UK Bank Holidays - Run Time Errors 13, 438 and 449
  • JasperReports. Using value from parent json in child table
  • Output key value pairs as JSON from T-SQL query
  • Getting key value pairs with linux server
  • Modifying settings.json in vscode to add shell escape flag to pdflatex in latex workshop
  • Unable to render data into grid column using JSON results
  • JSON decode failed
  • How to remove brakets on Json respone
  • How to parse JSON-input
  • JSON syntax for getting personal query ID
  • detecting 400 response without JSON response
  • Newtonsoft deserializing Json incorrectly
  • Getting _jsonparsefailure with valid json in logstash
  • How to send Map as String in POST request in flutter?
  • Difficulty fetching json data from API using Angular
  • Reading JSON data for my select query in SQL Server
  • AWS CloudFormation Template (JSON) to Create EC2 - Unexpected Error
  • How to concat multiple fields to same line with jq
  • Issue sending POST request from Swift
  • Storing JSON in BigQuery
  • How to get the value of a key in a JSON?
  • How to validate JSON input in Go
  • Extracting specific and latest data from http webserver using golang
  • Incorrect type. Expected "object"
  • json_to_record with embedded encoded json in PostgreSQL >= 10
  • Nested json extract from powershell
  • Parsing Json using Golang
  • Type 'number' is not assignable to type 'string'. How to cast number to string
  • kubectl - format the resource quota values in json format
  • Initializing and inserting nested JSON data in Golang?
  • Open JSON files in PhpStorm built-in web server
  • Extract Badge ID from JSON in .gitlab-ci.yml
  • Sharing Json Schema files among projects with versioning
  • Export Json data to an excel file using Angular 4
  • Return an empty array instead of null with golang for json return with gin
  • can Kafka connect value conveter (JSONConverter) can be used to convert GPB?
  • read json and access multiple keys
  • Spark How to get number of Keys changed in two JSONS in Scala?
  • Break JSON in pager "less"
  • How to filter Map in Flutter?
  • Convert hashmap to simple object in Groovy
  • Not extracting json properly using json4s
  • How do I display JSON data in my tableView when objects have the same value?
  • Where I can parse IoT data in Azure so I can afterwards save it to SQL DB
  • How to unmarshal this json string
  • What's wrong with my JSON?
  • JSON parsing problem in BlackBerry
  • GWT: How can I use JsonpRequestBuilder to handle a Json response of a list
  • shadow
    Privacy Policy - Terms - Contact Us © festivalmusicasacra.org