Query nested objects along with the query Elasticsearch

Question

I am having some trouble with querying data along with the nested objects in ES. More like left join in SQL,

SELECT
    select_list
FROM
    T1
LEFT JOIN T2 ON
    join_predicate;

which will return the all the data according to given term and matching nested objects.

Please see the below example.

1. Here is my mapping...

{
    mappings: {
        _doc: {
            properties: {
                accountId: { type: 'keyword' },
                history: {
                    type: 'nested',
                    properties: {
                        status: { type: 'keyword' },
                        createdAt: { type: 'date' }
                    }
                }               
            }
        }
    }
}

2. Data inside ES

[
    {
        accountId: 10001,
        history: {
            status: "NEW",
            createdAt: "2010-01-01"
        }
    },
    {
        accountId: 10002,
        history: {
            status: "NEW",
            createdAt: "2010-01-02"
        }
    },
    {
        accountId: 10001,
        history: {
            status: "FAIL",
            createdAt: "2010-01-03"
        }
    },
    {
        accountId: 10004,
        history: {
            status: "FAIL",
            createdAt: "2010-01-04"
        }
    },  
    {
        accountId: 10001,
        history: {}
    },  
    {
        accountId: 10001
    }
]

3. I need to get all the data (including nested objects) where accountId is 10001.

So basically it should return below data.

[
    {
        accountId: 10001,
        history: {
            status: "NEW",
            createdAt: "2010-01-01"
        }
    },
    {
        accountId: 10001,
        history: {
            status: "FAIL",
            createdAt: "2010-01-03"
        }
    },
    {
        accountId: 10001,
        history: {}
    },
    {
        accountId: 10001
    }
]

Can you help me?

Answer 1

Seeing that you only require documents having "accountId":"10001", you just need to use a simple Term Query for what you are looking for:

POST <your_index_name>/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "accountId": {
              "value": "10001"
            }
          }
        }
      ]
    }
  }
}

Your response would return the source document as it when you ingested it.

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : 0.44183275,
    "hits" : [
      {
        "_index" : "somenested",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.44183275,
        "_source" : {
          "accountId" : "10001",
          "history" : {
            "status" : "NEW",                <--- Doc 1
            "createdAt" : "2010-01-01"
          }
        }
      },
      {
        "_index" : "somenested",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 0.44183275,
        "_source" : {
          "accountId" : "10001",             <--- Doc 2
          "history" : {
            "status" : "FAIL",
            "createdAt" : "2010-01-03"
          }
        }
      },
      {
        "_index" : "somenested",
        "_type" : "_doc",
        "_id" : "5",
        "_score" : 0.44183275,
        "_source" : {
          "accountId" : "10001",             <--- Doc 3
          "history" : { }
        }
      },
      {
        "_index" : "somenested",
        "_type" : "_doc",
        "_id" : "6",
        "_score" : 0.44183275,
        "_source" : {
          "accountId" : "10001"              <--- Doc 4
        }
      }
    ]
  }
}

Hope this helps!