Azure Synapse Analytics: Unexpected null Value when Querying Array from CosmosDB

Question

Azure Synapse Analytics: Unexpected null Value when Querying Array from CosmosDB

Finn Schmidt 86

Hello,
I am working with the analytical store for CosmosDb, via Synapse Link. In CosmosDB i have json documents with (roughly) the following structure

{  
    "ID": 123,  
    "...": "...",  
    "items": [  
         {  
             "itemId": 1,  
             "...": "..."  
         }  
     ]  
}

When i try to query this dataset in the analytical store in synapse, via

Select ID, items  
FROM OPENROWSET( PROVIDER = 'CosmosDB', CONNECTION = "...", ...)

The items column returns arrays with a prepended null value, such as:

[  
    null,  
    {  
        "itemId": 1,  
        "...": "..."  
    }  
]

which, when using CROSS APPLY OPENJSON(items) to parse the items array into a tabular structure creates duplicated rows.
Of course I can simply remove those duplicated rows, but i don't understand where this null value is coming from in the first place.

MartinJaffer-MSFT 26,236 Reputation points

2022-12-08T00:20:51.16+00:00
Hello and welcome to Microsoft Q&A @Finn Schmidt

So I tried to reproduce your issue. I uploaded to an empty collection a document based upon yours:

{ "_id" : ObjectId("6391262f1b568e0784ed3bae"), "ID" : 123, "foo" : "bar", "items" : [ { "itemId" : 1, "data" : "a" }, { "itemId" : 2, "data" : "b" } ] }

Then after doing similar query using serverless SQL script

Select ID, items FROM OPENROWSET( ...) as [f]

The results were

ID : NjM5MTI2MmYxYjU2OGUwNzg0ZWQzYmFl
items: {"array":[{"object":{"itemId":{"int32":1},"data":{"string":"a"}}},{"object":{"itemId":{"int32":2},"data":{"string":"b"}}}]}

Am I doing something differently?
Finn Schmidt 86 Reputation points

2022-12-08T09:25:30.21+00:00
Hi MartinJaffer-MSFT,

thank you for taking a look at my question.

I believe this is not a generic issue, as it only happens on some of the documents, while others (with a similar json structure in CosmosDB) work as expected. So there likely is a different reason for why this is occuring when querying those documents, but I am having a hard time figuring out what that reason may be.

One additional question:
When i query the items array, my results look like this:

[null,{"key":"value"},null,{"key":"value"},null]

whereas yours seems to provide additional information (it injects the "array" and "object" keys, and shows the data types for each element).
Did you specify that somewhere else in your query?
MartinJaffer-MSFT 26,236 Reputation points

2022-12-08T18:25:13.237+00:00

No I didn't. Maybe it is the Cosmos type? Mine was working on the Mongo type of Cosmos. I couldn't find the analytical store in the other Cosmos types. The Link needs that, right? Which Cosmos type are you using?

I did have an idea. The objects are anonymous

items: [ {"data":1} , {"data":2"} ]

vs

items: [ "item1"{"data":1}, "item2":{"data":2} ]

maybe the nulls are the lack of a key?

Or maybe it is expecting more elements and not finding them.

Anyway, can I assume it is always the same documents being a problem? Can you share examples of the good ones and bad ones? Don't forget to redact any sensitive data. Maybe I can find something different about the documents.

Finn Schmidt 86

I am using the Azure Cosmos DB for NoSQL (and actually i thought this was the only one that supported azure synapse link? - maybe this has changed)

The Problematic Document looks like this:

    {  
        "items": [  
            {  
                "title": "abc",  
                "publishingDate": "2022-03-02T00:00:00Z",  
                "deliveryDate": null,  
                "redeliveryDate": null,  
                "deliveryListCreatedAt": "2022-03-02T00:00:00.351502Z",  
                "redeliveryListCreatedAt": null,  
                "additionalData": null,  
                "deliveryListDateId": 100046  
            },  
            {  
                "title": "def",  
                "publishingDate": "2022-03-09T00:00:00Z",  
                "deliveryDate": null,  
                "redeliveryDate": null,  
                "deliveryListCreatedAt": "2022-03-09T00:00:00.252762Z",  
                "redeliveryListCreatedAt": null,  
                "additionalData": null,  
                "deliveryListDateId": 100047  
            },  
         ],  
        "id": "123",  
        "deliveryListId":123,  
        "customerPartitionId": "abcd123"  
    }

with potentially more entries in the items array.

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-12-08T00:20:51.16+00:00

Hello and welcome to Microsoft Q&A @Finn Schmidt

So I tried to reproduce your issue. I uploaded to an empty collection a document based upon yours:

{ "_id" : ObjectId("6391262f1b568e0784ed3bae"), "ID" : 123, "foo" : "bar", "items" : [ { "itemId" : 1, "data" : "a" }, { "itemId" : 2, "data" : "b" } ] }

Then after doing similar query using serverless SQL script

Select ID, items FROM OPENROWSET( ...) as [f]

The results were

ID : NjM5MTI2MmYxYjU2OGUwNzg0ZWQzYmFl
items: {"array":[{"object":{"itemId":{"int32":1},"data":{"string":"a"}}},{"object":{"itemId":{"int32":2},"data":{"string":"b"}}}]}

Am I doing something differently?
Finn Schmidt 86 Reputation points

2022-12-08T09:25:30.21+00:00

Hi MartinJaffer-MSFT,

thank you for taking a look at my question.

I believe this is not a generic issue, as it only happens on some of the documents, while others (with a similar json structure in CosmosDB) work as expected. So there likely is a different reason for why this is occuring when querying those documents, but I am having a hard time figuring out what that reason may be.

One additional question:
When i query the items array, my results look like this:

[null,{"key":"value"},null,{"key":"value"},null]

whereas yours seems to provide additional information (it injects the "array" and "object" keys, and shows the data types for each element).
Did you specify that somewhere else in your query?
MartinJaffer-MSFT 26,236 Reputation points

2022-12-08T18:25:13.237+00:00

No I didn't. Maybe it is the Cosmos type? Mine was working on the Mongo type of Cosmos. I couldn't find the analytical store in the other Cosmos types. The Link needs that, right? Which Cosmos type are you using?

I did have an idea. The objects are anonymous

items: [ {"data":1} , {"data":2"} ]

vs

items: [ "item1"{"data":1}, "item2":{"data":2} ]

maybe the nulls are the lack of a key?

Or maybe it is expecting more elements and not finding them.

Anyway, can I assume it is always the same documents being a problem? Can you share examples of the good ones and bad ones? Don't forget to redact any sensitive data. Maybe I can find something different about the documents.
Finn Schmidt 86 Reputation points

2022-12-09T12:13:32.4+00:00

I am using the Azure Cosmos DB for NoSQL (and actually i thought this was the only one that supported azure synapse link? - maybe this has changed)

The Problematic Document looks like this:

{ "items": [ { "title": "abc", "publishingDate": "2022-03-02T00:00:00Z", "deliveryDate": null, "redeliveryDate": null, "deliveryListCreatedAt": "2022-03-02T00:00:00.351502Z", "redeliveryListCreatedAt": null, "additionalData": null, "deliveryListDateId": 100046 }, { "title": "def", "publishingDate": "2022-03-09T00:00:00Z", "deliveryDate": null, "redeliveryDate": null, "deliveryListCreatedAt": "2022-03-09T00:00:00.252762Z", "redeliveryListCreatedAt": null, "additionalData": null, "deliveryListDateId": 100047 }, ], "id": "123", "deliveryListId":123, "customerPartitionId": "abcd123" }

with potentially more entries in the items array.

Share via

Azure Synapse Analytics: Unexpected null Value when Querying Array from CosmosDB

Your answer