CROSS APPLY OPENJSON returns 2 rows when only 1 row exists

Carl Tribble 31 Reputation points
2022-11-25T17:02:22.54+00:00

I am using SQL Serverless Pool in Synapse Analytics to query JSON documents that have array objects in them. The array is named costAllocation and can have 1 or more objects in it. To include values from those objects in my result, I am using CROSS APPLY OPENJSON like as show below. The query returns 4 rows, 2 for each document. This makes sense for the second document which has 2 costAllocation object. But why are 2 rows returned for the first document? It only has 1 costAllocation object? Below please find the SQL I am using followed by the first and second documents I am querying.

SELECT   
    d.id  
    , d.customerId  
    , i.costAllocationMethod  
    , i.points  
FROM OPENROWSET(​PROVIDER = 'CosmosDB',  
                CONNECTION = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX',  
                OBJECT = 'XXXXXXXXXXXXX',  
                SERVER_CREDENTIAL = 'XXXXXXXXXXXXXXXXXXXXXXXXXXX'  
)  
WITH(  
    customerId VARCHAR(50)  
    , id VARCHAR(50)  
    , points INT  
    , costAllocation VARCHAR(MAX)  
) AS d  
CROSS APPLY OPENJSON (costAllocation)  
WITH (  
    costAllocationMethod INT  
    , points INT  
) AS i  
WHERE   
    id IN ('f64a9ad2-1e2c-488e-bfdc-501e88c1f0e3','d163a81d-7000-4e30-950b-4835f44ee5c8')  

-- Document with only 1 costAllocation object
{
"id": "f64a9ad2-1e2c-488e-bfdc-501e88c1f0e3",
"pk": "4c3199c3-6005-4920-a358-dd37388cc6b9",
"customerId": "4c3199c3-6005-4920-a358-dd37388cc6b9",
"points": 500,
"costAllocation": [
{
"costAllocationMethod": 1319,
"points": 500,
}
]
}

-- Document with 2 costAllocation objects
{
"id": "d163a81d-7000-4e30-950b-4835f44ee5c8",
"pk": "4a9b88e5-e5eb-4ef7-ace1-af2688612938",
"customerId": "4a9b88e5-e5eb-4ef7-ace1-af2688612938",
"points": 500,
"costAllocation": [
{
"costAllocationMethod": 1906,
"points": 121,
},
{
"costAllocationMethod": 1906,
"points": 379,
}
]
}

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,250 questions
{count} votes