CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
Niroj
Niroj

Reputation: 68

Azure CosmosDB sql join not returning results when the child contains empty array

we have the below json structure. Having nested array of objects. Some arrays may be empty.

[
{
    "adjustments": [
        {
            "id": "1_0000001",
            "clientID": 1,
            "adjustmentID": "0000001",
            "chargeID": "0000001",
            "dateOfEntry": "2019-01-29T00:00:00",
            "adjustmentAmount": 200
        }
    ],
    "payments": [
        {
            "id": "1_0000001",
            "clientID": 1,
            "paymentID": "0000001",
            "chargeID": "0000001",
            "dateOfDeposit": "2019-01-28T00:00:00",
            "dateOfEntry": "2019-01-29T00:00:00",
            "paymentAmount": 250,
        },
        {
            "id": "1_0000002",
            "clientID": 1,
            "paymentID": "0000002",
            "chargeID": "0000001",
            "dateOfDeposit": "2019-01-28T00:00:00",
            "dateOfEntry": "2019-01-29T00:00:00",
            "paymentAmount": 50,
        }
    ],
    "id": "1_0000001",
    "clientID": 1,
    "chargeID": "0000001",
    "encounterID": "0000001",
    "patientID": "1234567"
    "dateOfServiceBegin": "2019-01-20T00:00:00",
    "dateOfServiceEnd": "2019-01-20T00:00:00",
    "dateOfEntry": "2019-01-21T00:00:00",
    "location": "Main Campus",
    "chargeTotal": 500

},
{
    "adjustments": [],
    "payments": [],
    "id": "1_0000001",
    "clientID": 1,
    "chargeID": "0000001",
    "encounterID": "0000001",
    "patientID": "1234567"
    "dateOfServiceBegin": "2019-02-20T00:00:00",
    "dateOfServiceEnd": "2019-02-20T00:00:00",
    "dateOfEntry": "2019-02-21T00:00:00",
    "location": "Main Campus",
    "chargeTotal": 500
}
]

i am trying to execute the below query

SELECT udf.getMonthAndYearPart(c.dateOfEntry) as date, sum(p.paymentAmount) as paymentAmount , sum(c.chargeTotal) as chargeAmount , sum(a.adjustmentAmount) as adjustmentAmount FROM c
JOIN p IN c.payments 
JOIN a IN c.adjustments 
where c.dateOfEntry >= '2019-01-11T18:30:00.000Z' and c.dateOfEntry <= '2020-12-30T18:30:00.000Z' 
GROUP BY udf.getMonthAndYearPart(c.dateOfEntry)

I am expecting the below result

[
    {
        "date": "January-2019",
        "paymentAmount": 300,
        "chargeAmount": 1000,
        "adjustmentAmount": 400
    },
    {
        "date": "February-2019",
        "chargeAmount": 500,
    }
]

But I got only first object

[
    {
        "date": "January-2019",
        "paymentAmount": 300,
        "chargeAmount": 1000,
        "adjustmentAmount": 400
    }
]

Is there anything i can do without join? I want to calculate the sum of child objects amounts with group by month. Please help.

Upvotes: 0

Views: 904

Answers (2)

Niroj
Niroj

Reputation: 68

Found a solution by myself. using sub queries and group by. below one is the query in case anyone need this.

Select sum(k.totalPaymentAmount) as totalPaymentAmount,sum(k.totalAdjustmentAmount) as totalAdjustmentAmount,sum(k.totalCharge) as totalCharge,k.date as date From (SELECT 
(SELECT value sum(c.paymentAmount) FROM c IN RevenueAnalytics.payments) as totalPaymentAmount,
(SELECT value sum(c.adjustmentAmount) FROM c IN RevenueAnalytics.adjustments) as totalAdjustmentAmount,
RevenueAnalytics.chargeTotal as totalCharge,
udf.getMonthAndYearPart(RevenueAnalytics.dateOfServiceBegin) as date
FROM RevenueAnalytics) k
Group BY k.date

Upvotes: 1

Hugo Barona
Hugo Barona

Reputation: 1398

In your case you would need to do a LEFT JOIN in your query to include the cases of documents with empty adjustments or payments. LEFT JOIN at the moment is not supported, you can vote this thread to include this feature. In the meanwhile you can create a procedure and do two separate queries, one as you are doing using joins, and the other not using joins and filtering (where clause) for entries with array_length 0 for adjustments and payments, and then aggregate all results and result.

Upvotes: 0

Related Questions