How to project nested documents along side properties in the root document?

Question

I am attempting to return a projection of a nested document and some properties from the root document also. I would like to do this within the query if at all possible.

For example, I have an index cities where and the initial dataset:

[
    {
        _id: "1",
        city: "New York"
        state:"NY"
        users: [ 
         {
            firstName: "John",
            lastname: "Peters",
            birthYear: 1985
         }, {
            firstName: "Raul",
            lastname: "Other",
            birthYear: 1986
         }, {
            firstName: "Paul",
            lastname: "Ray",
            birthYear: 1997
         }
        ]       
    },
    {
        _id: "2",
        city: "Hackensack",
        state: "NJ"
        users: [ 
         {
            firstName: "Joe",
            lastname: "Anders",
            birthYear: 1988
         }
        ]
    }, 
    {
        _id: "3",
        city: "Albany"
        state:"NY"
        users: [ 
         {
            firstName: "Zoy",
            lastname: "Bat",
            birthYear: 1984
         }, {
            firstName: "Ana",
            lastname: "Lily",
            birthYear: 1999
         }
        ]        
    }
]

users is of type nested. (S/n: I think this is for the best currently because I would like to possibly filter on one of the properties, but I am willing to change this if need be.)

I would like to do a query for users in the state of NY ordered by birthYear, and what I would like Elasticsearch to return is something like:

[
  {
    city: "Albany"
    firstName: "Zoy",
    lastname: "Bat",
    birthYear: 1984
  }, {
    city: "New York"
    firstName: "John",
    lastname: "Peters",
    birthYear: 1985
  }, { 
    city: "New York"
    firstName: "Raul",
    lastname: "Other",
    birthYear: 1986
  }, {
    city: "New York"
    firstName: "Paul",
    lastname: "Ray",
    birthYear: 1997
  }, {
    city: "Albany"
    firstName: "Ana",
    lastname: "Lily",
    birthYear: 1999
  }        
]

This feels like something that should be possible during query time but I have not been able to find the appropriate functionality.

Answer 1

It is not possible to return what you expect using nested documents, as you cannot mix and match documents nested into different top-level documents and return them in the order you mention.

You should actually denormalize your data and promote users to primary entities. First, that would easily solve the query need you expressed and second I'm pretty confident this would also support your other needs.

So your documents should look like this instead:

 {
    id": 1,
    firstName: "John",
    lastname: "Peters",
    birthYear: 1985,
    city: "New York",
    state:"NY"
 }
 {
    id": 2,
    firstName: "Raul",
    lastname: "Other",
    birthYear: 1986,
    city: "New York",
    state:"NY"
 }
 etc...