How to query embedded data documentDB to retrieve parent document

Question

In DocumentDb, is it possible to search for child documents to get the parent documents?

public class Customer
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "name")]
    public string Name { get; set; } 

    [JsonProperty(PropertyName = "locations")]
    public List<Location> Locations { get; set; }

    public Customer()
    {
        Locations = new List<Location>();
    }
}

public class Location
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "city")]
    public string City{ get; set; }

    [JsonProperty(PropertyName = "state")]
    public string State{ get; set; } 

}

In the Document Explorer, I can see that I have one instance of this class structure like so:

{
  "id": "7",
  "name": "ACME Corp", 
  "location": [
    {
      "id": "c4202793-da55-4324-88c9-b9c9fe8f4b6c",
      "city": "newcity",
      "state": "ca" 
    }
  ]
},
{
  "id": "35",
  "name": "Another Corp", 
  "location": [
    {
      "id": "d33e793-da55-4324-88c9-b9c9fe8f4baa",
      "city": "newcity",
      "state": "ca" 
    }
  ]
}

Is there a way to query the embedded data like where city = 'newcity' and state = 'ca' but retrieve parent data? if I use SelectMany(x => x.Locations) to query for child then it will get Location data not the root(Customer) document.

thanks

Tom Sun - MSFT Tom Sun - MSFT · Accepted Answer · 2017-07-27T06:46:38

Is there a way to query the embedded data like where city = 'newcity' and state = 'ca' but retrieve parent data?

Yes, we may use Join to do that. More details please refer to Advanced database concepts and SQL queries.

SELECT c.id as id ,c.name as name,l as location from customer c Join l in 
c.location where l.city = 'newcity' and l.state = 'ca'

Which returns

C# code demo:

FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };
var customerQuery = client.CreateDocumentQuery<dynamic>(
UriFactory.CreateDocumentCollectionUri(databaseName, collectionName),
                "SELECT c.id as id, c.name as name, l as location from customer c Join l in c.location where l.city = 'newcity' and l.state = 'ca'",
                queryOptions).AsDocumentQuery();

var customerList = new List<dynamic>();
while (customerQuery.HasMoreResults)
{

   customerList.AddRange(customerQuery.ExecuteNextAsync<dynamic>().Result);

}

How to query embedded data documentDB to retrieve parent document

1 Answers