Synapse Serverless nested JSON in SQL view

Question

Synapse Serverless nested JSON in SQL view

Ásgeir Gunnarsson 61

Hi

I´m using Synapse Pipelines to read data from the groups endpoint in Microsoft Graph. The output is something like this (of course with many more groups):

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#groups",
    "@odata.nextLink": "https://graph.microsoft.com/v1.0/groups?$skiptoken=RFNwdAIAAQAAACpHcm91cF8wNWU0MGRhZC03YWFjLTQyNGEtYWM3Zi0yMjE1YWRhZTI2NjAqR3JvdXBfMDVlNDBkYWQtN2FhYy00MjRhLWFjN2YtMjIxNWFkYWUyNjYwAAAAAAAAAAAAAAA",
    "value": [
        {
            "id": "12345678-1234-1234-1234-012345678912",
            "deletedDateTime": null,
            "classification": null,
            "createdDateTime": "2021-02-02T08:50:21Z",
            "creationOptions": [
                "Team",
                "ExchangeProvisioningFlags:3552"
            ],
            "description": "Group",
            "displayName": "GroupName",
            "groupTypes": [
                "Unified"
            ],
            "preferredLanguage": null,
            "resourceBehaviorOptions": [
                "HideGroupInOutlook",
                "SubscribeMembersToCalendarEventsDisabled",
                "WelcomeEmailDisabled"
            ],
            "resourceProvisioningOptions": [
                "Team"
            ]
        }
}

I´m trying to create a query in Serverless that can list some of the attributes as I want it exposed as a view. But I´m failing to get anything.

Since the fields are inside the value tag I tried this to get the id column only (as a test):

select JSON_VALUE (doc, '$.value.id') AS GroupId

from openrowset(
         BULK 'Link to file in data lake',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows

But that gives me just nulls.

Does anyone have an idea how to get the nested values (I would love to get the next level down as well to filter only on unified groups).

Thanks in advance,
Ásgeir Gunnarsson

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

SQLArcher 81

Hi @Ásgeir Gunnarsson

You need to identify the depth you want to navigate to. See below for an example, and notice the value [0]:

SELECT TOP 100  
      
     JSON_VALUE(r.jsonContent, '$.value[0].id') as id,  
     JSON_VALUE(r.jsonContent, '$.value[0].deletedDateTime') as deletedDateTime,  
     JSON_VALUE(r.jsonContent, '$.value[0].classification') as classification,  
     JSON_VALUE(r.jsonContent, '$.value[0].createdDateTime') as createdDateTime  
  
FROM  
    OPENROWSET(  
        BULK '<link>',  
        FORMAT = 'CSV',  
        FIELDQUOTE = '0x0b',  
        FIELDTERMINATOR ='0x0b',  
        ROWTERMINATOR = '0x0b'  
    )  
    WITH (  
        jsonContent varchar(MAX)  
    ) AS [r]

More info here: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server?view=sql-server-ver15#PATH

If this answers you question, then please remember to mark this as Answered.

Ásgeir Gunnarsson 61 Reputation points

2021-12-14T08:39:54.887+00:00

Thank you @SQLArcher it worked
Ásgeir Gunnarsson 61 Reputation points

2021-12-14T08:43:49.727+00:00

Hmmm this will only give me the first id. Do you know of a way to get all the ids? There is thousands of them. The example I posted above only had one but the actual results have varied size.

SQLArcher 81

@Ásgeir Gunnarsson

You would need to be more specific around thousands of them - i.e. values, or files, etc.

It would be better to write to individual files if you are unable to guarantee the schema, or use Spark which provides more flexibility around what can be done vs T-SQL in Serverless. As an example, I have modified your source data by adding a second value (assuming that each new record is an additional array within "value":

    {  
    			"id": "12345678-1234-1234-1234-012345678913",  
    			"deletedDateTime": null,  
    			"classification": "fake",  
    			"createdDateTime": "2021-02-02T08:50:21Z",  
<...>  
    }

This changes the query to:

SELECT  
     *  
FROM  
    OPENROWSET(  
        <...>  
    )  
    WITH (  
        jsonContent varchar(MAX)  
    ) AS [r]  
    CROSS APPLY OPENJSON(jsonContent, '$.value')  
    WITH (  
    id NVARCHAR(100) '$.id',  
    deletedDateTime NVARCHAR(100) '$.deletedDateTime',  
    classification NVARCHAR(100) '$.classification',  
    createdDateTime NVARCHAR(100) '$.createdDateTime',  
    creationOptions NVARCHAR(100)  '$.creationOptions'  
    )

Hopefully the above steers you in the right direction.

Ásgeir Gunnarsson 61 Reputation points

2021-12-14T11:53:15.097+00:00

Thanks again.

I was hoping to use only Serverless but I guess it´s not going to happen. It´s thousands of records in one file by the way not thousands of files. I could perhaps change that and have only one record in each file and use your original method. I will see. But I´m much better off now than before thanks to your answers.

Share via

Synapse Serverless nested JSON in SQL view

0 additional answers

Your answer