JSON String invalid

Question

JSON String invalid

Dave

Old Hand

Points: 350
More actions
May 6, 2022 at 3:48 pm

#4028111

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

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

Dave Old Hand Points: 350 More actions · Answer 2

Dave

Old Hand

Points: 350

May 9, 2022 at 8:56 am

#4029360

thank Eddie

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

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)

Dave Old Hand Points: 350 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

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)

Phil Parkin SSC Guru Points: 246987 More actions · Answer 6

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.

SomeJSON

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dave Old Hand Points: 350 More actions · Answer 7

Dave

Old Hand

Points: 350

May 11, 2022 at 5:17 pm

#4031259

ha ha attempts have been made Jeff

Dave Old Hand Points: 350 More actions · Answer 8

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

Steve Collins SSCrazy Points: 2174 More actions · Answer 9

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