Fetch list of items using one query from AZURE Cosmos Db

Question

Want to to fetch list of items depending on roomConfirmationNo or showConfirmationNo or diningConfirmationNo using one query from AZURE Cosmos Db.

Have mentioned below the item structure :

{
  "reservations": {
    "diningReservations": [
      {
        "confirmationNumber": "ABCDE"
      }
    ],
    "roomReservations": [
      {
        "confirmationNumber": "A433vg66"
      }
    ],
    "showReservations": [
      {
        "confirmationNumber": "12345"
      }
    ]
  },
  "id": "*****",
  "_rid": "*****",
  "_self": "*****",
  "_etag": "*****",
  "_attachments": "*****",
  "_ts": "*****"
}

Have tried using join and array contains queries but these queries are not working :

SELECT c FROM c
JOIN rr IN c.reservations.roomReservations
JOIN sr IN c.reservations.showReservations
JOIN dr IN c.reservations.diningReservations
 WHERE rr.confirmationNumber IN ("A433vg66", "A56553vg66", "A433fdffvg66")
 OR sr.confirmationNumber IN ("12345", "78665")
 OR dr.confirmationNumber IN ("ABCDE")
 
 SELECT c FROM c
JOIN rr IN c.reservations.roomReservations
JOIN sr IN c.reservations.showReservations
JOIN dr IN c.reservations.diningReservations
 WHERE rr.confirmationNumber IN ("A433vg66", "A56553vg66", "A433fdffvg66")
 AND sr.confirmationNumber IN ("12345", "78665")
 AND dr.confirmationNumber IN ("ABCDE")


SELECT c FROM c 
JOIN (SELECT VALUE rr FROM rr IN c.reservations.roomReservations WHERE rr.confirmationNumber IN ("A433vg66", "A56553vg66", "A433fdffvg66"))
JOIN (SELECT VALUE sr FROM sr IN c.reservations.showReservations WHERE sr.confirmationNumber IN ("12345", "78665"))
JOIN (SELECT VALUE dr FROM dr IN c.reservations.diningReservations where dr.confirmationNumber IN ("ABCDE"))

SELECT c
FROM c
WHERE ARRAY_CONTAINS(c.reservations.roomReservations, {confirmationNumber: 'A433vg66'})
OR ARRAY_CONTAINS(c.reservations.showReservations, {confirmationNumber: '12345'})
OR ARRAY_CONTAINS(c.reservations.diningReservations, {confirmationNumber: 'ABCDE'})

SELECT c
FROM c
WHERE ARRAY_CONTAINS(c.reservations.roomReservations, {confirmationNumber: 'A433vg66'})
AND ARRAY_CONTAINS(c.reservations.showReservations, {confirmationNumber: '12345'})
AND ARRAY_CONTAINS(c.reservations.diningReservations, {confirmationNumber: 'ABCDE'})

Answer 1

等待大神答复