Cosmos DB NoSQL - Make a SQL SELECT Query that nests documents, where documents are flat in the database

Question

I'm looking for a SELECT query that, given a familyID, uses a Subquery (https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/subquery) that can get me an array result of Families with nested "canine" and "feline" properties.

This is a desired result:

[
  {
    "id": "100",
    "type": "family",
    "familyId": "100",
    "canines": [
      {
        "id": "2",
        "type": "dog",
        "familyId": "100"
      }
    ],
    "felines": [
      {
        "id": "3",
        "type": "cat",
        "familyId": "100"
      },
      {
        "id": "4",
        "type": "cat",
        "familyId": "100"
      },
    ]
  }
]

I'm changing the names of the database schema to simplify things.

We have three types of Models in the database, they are all stored in the same document store, let's just call it Families.

Let's say that type is an Enum of dog | cat | family

Family model:

id: uuid string
familyId: uuid string (same as id, for all 'family' type documents)
type: "family"

Dog model:

the familyId matches to a type "family" document id elsewhere in the database

id: uuid string
familyId: uuid string
type: "dog"

Cat model:

id: uuid string
familyId: uuid string
type: "cat"

What I've tried, given an id that belongs to a family type, I can simply do:

SELECT * FROM c WHERE c.familyId='100'

... the only problem is this returns a flat list of Family, Dogs and Cats elements in the array.

This might be helpful: a subquery result that gets me all Dogs looks like this:

SELECT c.id, c.type, dogsFamilyIds
FROM Families c
JOIN (SELECT VALUE c.familyId FROM c WHERE c.Type = 'dog') dogsFamilyIds
WHERE (dogsFamilyIds = '100')

My only problem now, is how do I nest dogs and cats within a family?

Answer 1

Use subqueries for both felines and canines and then combine them, something like this;

SELECT f.id, f.type, f.familyId, 
    (SELECT VALUE d FROM Families d WHERE d.type = 'dog' AND d.familyId = f.familyId) AS canines, 
    (SELECT VALUE c FROM Families c WHERE c.type = 'cat' AND c.familyId = f.familyId) AS felines
FROM Families f
WHERE f.type = 'family' AND f.familyId = '100'