Why distinct is not taking WHERE condition in Cosmos?

Why distinct is not taking considering WHERE clause? I am getting all records and I want only those which satisfies WHERE conditon.

Query:

SELECT distinct c.id, c.stockNumber, c.createdDateTime, c.lineItems 
FROM c
JOIN t IN c.lineItems
WHERE c.stockNumber in ('123')
and t.operationId = 1400742

Expected Result:

{
  "id": "b3d7768e-7a22-4109-9511-cdd17585267b",
  "createdDateTime": "2023-06-01T21:02:34.4800722+00:00",
  "earliestPaymentDate": "2023-05-31T07:00:00+00:00",
  "stockNumber": "123",
  "lineItems": [
    {
      "itemUpdateDateTime": "2023-06-01T21:07:26.2846163+00:00",
      "location": "SomeLocation",
      "operationId": 1400742
    }
  ]
}

Actual Result:

[
   {
  "id": "b3d7768e-7a22-4109-9511-cdd17585267b",
  "createdDateTime": "2023-06-01T21:02:34.4800722+00:00",
  "earliestPaymentDate": "2023-05-31T07:00:00+00:00",
  "stockNumber": "123",
  "lineItems": [
    {
      "itemUpdateDateTime": "2023-06-01T21:07:26.2846163+00:00",
      "location": "SomeLocation",
      "operationId": 1400742
    },
    {
      "itemUpdateDateTime": "2023-06-01T21:07:26.28473+00:00",
      "location": "SomeLocation",
      "operationId": 1400741
    },
    {
      "itemUpdateDateTime": "2023-06-01T21:07:26.284864+00:00",
      "location": "SomeLocation",
      "operationId": 1400741
    },
    {
      "itemUpdateDateTime": "2023-06-01T21:07:26.284906+00:00",
      "location": null,
      "operationId": null
    },
    {
      "itemUpdateDateTime": "2023-06-01T21:07:26.2849904+00:00",
      "location": null,
      "operationId": null
    }
  ]
}]

The error is not because of the distinct keyword. The code you provided selects the field c.lineitem and filter is on t.operationId. That is the reason you are getting all the data from lineitems array. Below is the corrected code.

Code:

SELECT  distinct c.id, c.stockNumber, c.createdDateTime,
{"operationId":t.operationId,"location":t.location,"itemUpdateDateTime": t.itemUpdateDateTime} as lineItems
FROM c
JOIN t IN c.lineItems
WHERE c.stockNumber in ('123')
and t.operationId =  1400742

The above code selects the id, stockNumber, and createdDateTime fields from the root document c, and creates a new lineItems object that includes only the operationId, location, and itemUpdateDateTime fields from the lineItems array using the JOIN operator.

Updated code:

SELECT DISTINCT c.id, c.stockNumber, c.createdDateTime, 
t as lineItems FROM c JOIN t IN c.lineItems
WHERE c.stockNumber in ('123') and t.operationId = 1400742