JSON String invalid
-
Hi All,
Have query regarding a nested array in JSON file, previously raised a ticket on how to retrieve the data but due to the number of JSON files involved using JSON_VALUE is leading to poor performance. Decided to try to name the array myself in the JSON string but coming across problems, below is an idea of what I'm trying to do in SQL at the moment.
@J1 works fine but as you can see "riskDataRepeating" array is followed by an unnamed array so its "[[". Need to name the array so its "["TEST": [" instead as per @J2 example below. At least I think that's what I need, @J2 errors though
Hopefully somebody out there can point me in the right direction on this
DECLARE @J1 NVARCHAR(MAX)
SET @J1 = '{
"riskDataRepeating": [
[
{
"riskDataItem": {
"dataId": 123,
"label": "TEST",
"value": "TEST"
}
}
]
]
}'
SELECT @J1, ISJSON(@J1)
SELECT * FROM OPENJSON(@J1)
--*************************************************************************
DECLARE @J2 NVARCHAR(MAX)
SET @J2 = '{
"riskDataRepeating": [
"TEST": [
{
"riskDataItem": {
"dataId": 123,
"label": "TEST",
"value": "TEST"
}
}
]
]
}'
SELECT @J2, ISJSON(@J2)
SELECT * FROM OPENJSON(@J2)
Msg 13609, Level 16, State 4, Line 39
JSON text is not properly formatted. Unexpected character ':' is found at position 36.
Thanks
-
Wrap the value of "riskDataRepeating" with braces (" { } ") instead of brackets (" [ ] "):
DECLARE @J2 NVARCHAR(MAX)
SET @J2 = '{
"riskDataRepeating": {
"TEST": [
{
"riskDataItem": {
"dataId": 123,
"label": "TEST",
"value": "TEST"
}
}
]
}
}'
SELECT @J2, ISJSON(@J2)
SELECT * FROM OPENJSON(@J2)Eddie Wuerch
MCM: SQL -
thank Eddie
-
IMHO, it seems to me that you're attacking the wrong issue. Whatever is creating the bad JSON is the real issue that needs to be fixed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
JSON is being passed to us and the team that produce it have refused to fix as they claim its valid JSON so that's a no no
-
I guess they don't understand the concept of "DevOps". 😀
Can't you snuggle up to them and tell them, "Yes, you're correct but I really need your help with a tweak because SQL Server is having one heck of a problem with it"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I often use this site when looking at JSON strings. I pasted your J1 version into it and, as you will see from the image below, it is perfectly valid.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
ha ha attempts have been made Jeff
-
Hi Phil
It is valid JSON but there's valid JSON and then there's JSON that makes sense, not sure why you would have unnamed arrays for something like this but i might be missing something. Agree with you thats its correct in terms of passing a valid JSON test though
-
Maybe try this. If ISJSON(@j1) equals 1 then it's readable
select oj5.*
from openjson(@j1) oj1
cross apply openjson(oj1.value) oj2
cross apply openjson(oj2.value) oj3
cross apply openjson(oj3.value) oj4
cross apply openjson(oj4.value) oj5;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply