CROSS APPLY OPENJSON returns 2 rows when only 1 row exists
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,
}
]
}