Flatten Object when Selecting Cosmos DB Document

Question

I have the following document in Cosmos DB

{
  "id": "c6c7a79a-3351-8be8-2700-b0c9414c1622",
  "_rid": "mdRtAJiE1cEBAAAAAAAAAA==",
  "_self": "dbs/mdRtAA==/colls/mdRtAJiE1cE=/docs/mdRtAJiE1cEBAAAAAAAAAA==/",
  "_etag": "\"00000000-0000-0000-35f2-3baea11001d5\"",
  "FedId": "1023157382",
  "UniqueIdentifier": "00003089421",
  "UniqueIdentifierNumeric": 3089421,
  "Item": {
    "LastUpdatedDate": "2019-07-08T02:36:20",
    "OrderNumber": "2013282",
    "QueueRank": 2
  }
}

And the following C# class

public class Item
{
    public string FedId { get; set; }
    public string UniqueIdentifier { get; set; }
    public DateTime LastUpdatedDate { get; set; }
    public string OrderNumber { get; set; }
    public int QueueRank { get; set; }
}

Using Cosmos SQL API, how do I select the document and map it to the Item class?

The following is what I have tried:

var result = _client.CreateDocumentQuery<Item>(collectionLink, new SqlQuerySpec
{
    QueryText = @"
        SELECT c.FedId, c.UniqueIdentifier, i.*
        FROM c JOIN i IN c.Item
        WHERE
            sie.FedId = '1023157382'
    "
});

Essentially, In addition to map document properties, I also need to flatten the Item property in the Cosmos document. The result that I'm expecting is:

Console.WriteLine(result.FedId); // return "1023157382"
Console.WriteLine(result.UniqueIdentifier); // return "00003089421"
Console.WriteLine(result.LastUpdatedDate); // return "2019-07-08T02:36:20"
Console.WriteLine(result.OrderNumber); // return "2013282"
Console.WriteLine(result.QueueRank); // return 2

I have also tried query with joins, but doesn't seem to work with non-array property. https://github.com/Azure/azure-cosmos-dotnet-v2/blob/d17c0ca5be739a359d105cf4112443f65ca2cb72/samples/code-samples/Queries/Program.cs#L421-L435

Alex AIT Alex AIT · Accepted Answer · 2019-07-22T18:32:30

DocumentDB find deep key:value

You don't need to do a join if you do not have an array property. You can just use c.Item.LastUpdatedDate.

Result should look something like this:

var result = _client.CreateDocumentQuery<Item>(collectionLink, new SqlQuerySpec
{
    QueryText = @"
        SELECT c.FedId, c.UniqueIdentifier, c.Item.LastUpdatedDate, c.Item.OrderNumber, c.Item.QueueRank
        WHERE
            c.FedId = '1023157382'
    "
});

On another note, you might also try to use the linq provider instead. Create a class for your top level document (e.g. MyDocument) and the item part of it, and use select to map the values.

Would work something like this:

var result = _client.CreateDocumentQuery<MyDocument>(collectionLink)
    .Select(document => new Item{
        FedId = document.FedId,
        UniqueIdentifier = document.FedId,
        LastUpdatedDate = document.Item.LastUpdatedDate,
        OrderNumber = document.Item.OrderNumber,
        QueueRank = document.Item.QueueRank
    });

PS: Use SelectMany once you have array properties instead of writing SQL joins.

Flatten Object when Selecting Cosmos DB Document

1 Answers