OPENJSON - unnamed nested array

Question

OPENJSON - unnamed nested array

Dave

Old Hand

Points: 350
More actions
March 28, 2022 at 2:59 pm

#4007126

Currently working on sql script to extract from JSON file(s) but i've come across an unnamed array within a named array
Wondering if anybody had any idea how to extract this? rest of my script is working fine using CROSS APPLY OPENJSON to extract what i require and where the arrays are named i'm having no problems but this has got me completely stumped.
Hoping somebody out there can do an old man a favour!!!!
"DataRepeating": [
[
{
"DataItem": {
"parentId": 1
}
}
]
],

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Is it possible that you can post an example that contains some actual data, along with your desired results based on that example? It might also be helpful to see your existing query.

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 2

Sure, its the final cross apply which is the issue. Have also attached section of the file which hopefully highlights what my issue is

Thanks for looking into it

SELECT

parentDataId

,dataId

,isRated

,id

,label

,value

,previousDataId

FROM OPENJSON(@JSON)

WITH

(

policyStatus NVARCHAR(25) '$.policy.status',

riskData NVARCHAR(MAX) '$.riskData' AS JSON

) AS jrd

CROSS APPLY OPENJSON(jrd.riskData)

WITH

(

riskSections NVARCHAR(MAX) AS JSON

) p

CROSS APPLY OPENJSON (p.riskSections)

WITH

(

sectionName NVARCHAR(255),

riskDataRepeating NVARCHAR(MAX) AS JSON

) rdr

CROSS APPLY OPENJSON (rdr.riskDataRepeating)

WITH

(

parentDataId INT '$.riskDataItem.parentDataId',

dataId INT,

isRated BIT,

id NVARCHAR(255),

label NVARCHAR(255),

value NVARCHAR(255),

previousDataId INT

) rdi

WHERE policyStatus IN ('adc','ads','alp','aoc','aqt','arj','can','ndc','nds','nia','niq','nlp','noc','nqt','nrj','qtd','rdc','rlp','roc','rqt','rrj')

Attachments:

You must be logged in to view attached files.

Steve Collins SSCrazy Points: 2160 More actions · Answer 3

When there is no name provided for an array you could use OPENJSON without specifying a schema and then refer to the array as '[value]' (with appropriate table/tvf alias'ing) in subsequent paths. In the code both 'jdr' and 'jval' alias schema-less OPENJSON functions. Maybe something like this

declare @json       nvarchar(max)=N'
{
    "DataRepeating": [
        [
            {
                "DataItem": {
                    "parentId": 1
                }
            }
        ]
    ]
}';

select json_value(jval.[value], N'$.DataItem.parentId') parent_id
from openjson(@json) 
        with (DataRepeating     nvarchar(max) as json) asj
     cross apply openjson(asj.DataRepeating) jdr
     cross apply openjson(jdr.[value]) jval;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Dave Old Hand Points: 350 More actions · Answer 4

Steve that works perfectly, you've saved me from losing even more hair!

Thanks for your help on this