October 5, 2021 at 7:32 pm
I have one query at the top that returns data (see text at bottom stored in a .json file actually) but the second one returns only the headers:
-- this one works to bring in data unformatted on just one cell:
SELECT *
FROM OPENROWSET (BULK 'C:\Users\mwendel\Desktop\Colors.JSON', SINGLE_CLOB) as j
---------------------------------------------------------------------------------
-- The one below does not work, only headers are returned:
Declare @JSON varchar(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\mwendel\Desktop\Colors.JSON', SINGLE_CLOB) as j
SELECT * FROM OPENJSON (@JSON,'$.data')
With (_id int,
product_name varchar(4000) '$.links.product_name',
supplier varchar(4000) '$.attributes.supplier',
quantity int '$.attributes.quantity',
unit_cost varchar(50) '$.attributes.unit_cost'
) as Dataset
Go
[{
"_id": {
"$oid": "5968dd23fc13ae04d9000001"
},
"product_name": "sildenafil citrate",
"supplier": "Wisozk Inc",
"quantity": 261,
"unit_cost": "$10.47"
}, {
"_id": {
"$oid": "5968dd23fc13ae04d9000002"
},
"product_name": "Mountain Juniperus ashei",
"supplier": "Keebler-Hilpert",
"quantity": 292,
"unit_cost": "$8.74"
}, {
"_id": {
"$oid": "5968dd23fc13ae04d9000003"
},
"product_name": "Dextromathorphan HBr",
"supplier": "Schmitt-Weissnat",
"quantity": 211,
"unit_cost": "$20.53"
}]
October 5, 2021 at 10:17 pm
I've not worked with JSON in SQL before, but my first debugging step would be to do a SELECT @json to make sure that you got the full data from the file. This is just a debugging step.
Once you have confirmed that you have the full data from the file in the variable, the next step is to ensure you are using SQL Server's JSON syntax correctly. My testing indicates there are a few issues, but easy enough to correct.
Try this out instead:
DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = '[{
"_id": {
"$oid": "5968dd23fc13ae04d9000001"
},
"product_name": "sildenafil citrate",
"supplier": "Wisozk Inc",
"quantity": 261,
"unit_cost": "$10.47"
}, {
"_id": {
"$oid": "5968dd23fc13ae04d9000002"
},
"product_name": "Mountain Juniperus ashei",
"supplier": "Keebler-Hilpert",
"quantity": 292,
"unit_cost": "$8.74"
}, {
"_id": {
"$oid": "5968dd23fc13ae04d9000003"
},
"product_name": "Dextromathorphan HBr",
"supplier": "Schmitt-Weissnat",
"quantity": 211,
"unit_cost": "$20.53"
}]'
SELECT * FROM OPENJSON (@JSON)
With (_id VARCHAR(25) '$._id."$oid"',
product_name varchar(4000) '$.product_name',
supplier varchar(4000) '$.supplier',
quantity int '$.quantity',
unit_cost varchar(50) '$.unit_cost'
) as Dataset
I took out things that don't exist like "links.product_name" as that isn't valid. "links" doesn't exist anywhere in your JSON. I also fixed the ID as you hadn't indicated what that ID was, and your OPENJSON had a bad path as you have no "data" path in your JSON, so I just stripped out the path completely as it isn't needed for your example.
Lastly, I changed the _id value from an INT and returning NULL (as it was not defined) to return a VARCHAR(25) of the $oid value. Not sure if that is the intended result or not, but I see no "int" value to use in your JSON to populate the _id.
Does the above help?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 6, 2021 at 12:51 pm
This works perfectly! Thanks! I was able to read from the actual file by just using this and was able to add an insert into to get it into a SQL table::
Declare @JSON varchar(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\mwendel\Desktop\Colors.JSON', SINGLE_CLOB) as j
SELECT * FROM OPENJSON (@JSON)
With (_id VARCHAR(25) '$._id."$oid"',
product_name varchar(4000) '$.product_name',
supplier varchar(4000) '$.supplier',
quantity int '$.quantity',
unit_cost varchar(50) '$.unit_cost'
) as Dataset
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply