April 17, 2023 at 9:06 pm
I have a list of data as follows. I have several rows but I just extracted two rows for the help. Having trouble to pull the data correctly so that i can load into SQL table. Any help on this will be greatly appreciated.
DECLARE @Jsondata AS NVARCHAR(MAX)
SET @Jsondata =
'{
"Transaction": [
{
"Id": 43358,
"DueDate": "2023-04-15T00:00:00"
},
{
"Id": 43357,
"DueDate": "2023-04-15T00:00:00",
}
],
"Response": {
"Success": true,
"Error": null
}
}'
SELECT id , DueDate
FROM OPENJSON(@Jsondata)
WITH
(
Id NVARCHAR(500) '$.Id',
DueDate NVARCHAR(100) '$.DueDate'
)
April 18, 2023 at 2:24 am
Okay cleaning that up a bit I have the following:
DECLARE @Jsondata AS NVARCHAR(MAX)
SET @Jsondata = '{ "Transaction": [ { "Id": 43358
,"DueDate": "2023-04-15T00:00:00"
}
,{ "Id": 43357
,"DueDate": "2023-04-15T00:00:00"
,
}
]
,"Response": { "Success": true
,"Error": null
}
}'
SELECT id
,DueDate
FROM OPENJSON(@Jsondata)
WITH ( Id NVARCHAR(500) '$.Id'
,DueDate NVARCHAR(100) '$.DueDate'
)
First off I would point out that your second Id has an extra comma in the JSON data and that is going to kill things right there. Next what is it that you are attempting to do and/or having a problem doing? As you do not explain that at all. Lastly if it is just that extra comma that could have easily been found by properly structuring your code which does not happen often enough and subsequently causes issues.
Lastly using VARCHAR(MAX) when you absolutely do not need to is going to cause a significant slow down in the execution of any code associated with your use of VARCHAR(MAX) or MAX within any VAR type variable for that matter.
April 18, 2023 at 6:08 pm
I used Dennis Jensen's code (as I could copy-paste to SSMS) then tweaked it to work in SSMS and give some results with ID and DueDate:
USE master
DECLARE @Jsondata AS NVARCHAR(MAX)
SET @Jsondata = '{ "Transaction": [ { "Id": 43358
,"DueDate": "2023-04-15T00:00:00"
}
,{ "Id": 43357
,"DueDate": "2023-04-15T00:00:00"
}
]
,"Response": { "Success": true
,"Error": null
}
}'
SELECT *
FROM OPENJSON(@Jsondata, '$.Transaction')
WITH (Id INT, DueDate DATETIME)
NOTE I removed that extra comma so the code would run AND the results are JUST showing the Transaction related data, not the Response. If you need both, it'll be a bit more parsing, but not impossible to do.
If you are going to be parsing JSON data a lot, I recommend reading up on it - https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16
Not trying to be snarky or anything, just trying to give some advice :).
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.
April 20, 2023 at 2:01 am
Thank you. That works great. Now, I have another Challange. I’m calling API to get the json file (string) and loading json string into SQL table in a column nvarchar(max). The issue I’m having is when the dataset is large, it doesn’t fit in nvarchar(max) and chops off the remaining data, thus forming bad json.
Any workaround on this? What dataype can hold the large json string?
Thanks in advance.
April 20, 2023 at 4:21 am
Varchar(max) can hold more, but if you really need to store a huge amount of json data, it may make more sense to store it on disk and parse it outside of SQL...
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.
April 20, 2023 at 2:27 pm
Yeah and it should be noted to -- absolutely only use VARCHAR(MAX) when VARCHAR(8000) is to small -- otherwise you take a nasty performance hit. Further I concur with Mr. Brain Gale on their assessment of handling a really large JSON file. Sometimes overthinking a solution leads to far more complexity than is necessary and over complexity often leads to unnecessary issues later on. So always K.I.S.S. it when you can. Oh and for those that know the negative version for that here is the positive one and the one I use as to me it makes a lot more sense Keep It Simple and Smart
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply