azureazure-cosmosdbazure-synapseazure-cosmosdb-sqlapi

Azure Cosmos Synapse Link - null fields within object default to 0 in SQL

I have a Cosmos database in Well-Defined schema mode. I have a Synapse Serverless SQL instance. I have documents in Cosmos with objects that contain fields with null values. When I create a View for the Container in SQL the null object values are appearing as 0, why would it do this?

Example:

Here's a doc in Cosmos (object highlighted in yellow with null fields):

Cosmos

Synapse View

Synapse Query

Solution

  • When I read the document from cosmos db in serverless SQL pool I faced the same issue as shown below:

    enter image description here

    The null values in AddressDetails are being replaced with 0. This suggests that the SQL Server engine (or the underlying JSON parser used by OPENROWSET) is interpreting line1, line2, and postcode as numeric fields (probably integers), as shown below:

    enter image description here

    And 0 is their default value. That may be the reason to get AddressDetails in above format. You can use below query to transform the AddressDetails field as required:

    SELECT 
    JSON_QUERY(
        CONCAT(
            '{"line1":', 
            CASE WHEN JSON_VALUE(AddressDetails, '$.line1') <> '0' THEN JSON_VALUE(AddressDetails, '$.line1') ELSE 'null' END, 
            ',"line2":', 
            CASE WHEN JSON_VALUE(AddressDetails, '$.line2') <> '0' THEN JSON_VALUE(AddressDetails, '$.line2') ELSE 'null' END, 
            ',"postcode":', 
            CASE WHEN JSON_VALUE(AddressDetails, '$.postcode') <> '0' THEN JSON_VALUE(AddressDetails, '$.postcode') ELSE 'null' END, 
           
            '}'
        )
    ) AS AddressDetails_Transformed
FROM OPENROWSET(
    PROVIDER = 'CosmosDB',
    CONNECTION = 'Account=csmsdbs;Database=db',
    OBJECT = 'c1',
    SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
) AS [c1];

    You can get the output as shown below:

    enter image description here