Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I use OPENJSON on the result of a sub OPENJSON

I have a JSON structure like this:

Declare @layout NVARCHAR(MAX) = N'
    {
        "Sections": [
            {
                "SectionName":"Section1",
                "SectionOrder":1,
                "Fields":[
                    {
                        "FieldName":"Field1",
                        "FieldData":"Data1"
                    },
                    {
                        "FieldName":"Field2",
                        "FieldData":"Data2"
                    }
                ]
            },
            {
                "SectionName":"Section2",
                "SectionOrder":2,
                "Fields":[
                    {
                        "FieldName":"Field3",
                        "FieldData":"Data3"
                    },
                    {
                        "FieldName":"Field4",
                        "FieldData":"Data4"
                    }
                ]
            }
        ]
    }
'

How do I query to the equivalent of Sections.Fields.FieldName = 'Field3'?

select *
from OPENJSON(@layout,'$.Sections') 
WITH (
    SectionName nvarchar(MAX) '$.SectionName',  
    SectionOrder nvarchar(MAX) '$.SectionOrder', 
    Fields nvarchar(MAX) '$.Fields' as JSON
)

This is as far as I got, I can't go down any further or it will not return any results.

like image 527
Bill Software Engineer Avatar asked Oct 30 '25 02:10

Bill Software Engineer


1 Answers

Figured it out after reading documentation, fully works!

SELECT SectionName, FieldName, FieldData FROM (
    select *
    from OPENJSON(@layout,'$.Sections') 
    WITH (
        SectionName nvarchar(MAX) '$.SectionName',  
        SectionOrder nvarchar(MAX) '$.SectionOrder', 
        Fields nvarchar(MAX) '$.Fields' as JSON
    )
) as Sections
CROSS APPLY OPENJSON(Fields,'$')
WITH (
    FieldName nvarchar(MAX) '$.FieldName',  
    FieldData nvarchar(MAX) '$.FieldData'
)
like image 81
Bill Software Engineer Avatar answered Nov 01 '25 17:11

Bill Software Engineer



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!