Import only arrays that match conditions in Elastic Search

Question

I'm dealing with nested nested data in ElasticSearch.

I want it to work like a SELECT * from where in an RDBMS.

If you have the following data

POST test-stack/test/1234_5678
{
  "Id" : 1234,
  "availables":
  [
    {
      "Id" : 4444,
      "date" : "2019-09-10",
      "time" : [
        {
          "dateTime" : "2019-09-10T09:30:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-10T10:00:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-10T10:30:00+09:00",
          "Count" : 50
        }
      ]
    },
    {
      "Id" : 5555,
      "date" : "2019-09-11",
      "time" : [
        {
          "dateTime" : "2019-09-11T09:30:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-11T10:00:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-11T10:30:00+09:00",
          "Count" : 50
        }
      ]
    },
    {
      "Id" : 6666,
      "date" : "2019-09-12",
      "time" : [
        {
          "dateTime" : "2019-09-12T09:30:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-12T10:00:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-12T10:30:00+09:00",
          "Count" : 50
        }
      ]
    }
  ]
}

If I do this,

Select * from test t where t.availables.date == '2019-09-10';

So, I want to get this answer,

      "Id" : 4444,
      "date" : "2019-09-10",
      "time" : [
        {
          "dateTime" : "2019-09-10T09:30:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-10T10:00:00+09:00",
          "Count" : 50
        },
        {
          "dateTime" : "2019-09-10T10:30:00+09:00",
          "Count" : 50
        }
      ]
    }

I'm a beginner in Elastic Search and I wonder if this is possible in Elastic Search.

I've studied painless scripts but still don't know.

Answer 1

You need to use nested query and inner hits.

Nested query will help you to filter on nested field and inner hits will return matching nested document

Mapping:

PUT testindex11/_mapping
{
  "properties": {
    "Id": {
      "type": "text"
    },
    "availables": {
      "type": "nested",
      "properties": {
        "Id": {
          "type": "text"
        },
        "date": {
          "type": "date",
          "format": "yyyy-MM-dd"
        },
        "time":{
          "type": "nested",
           "properties": {
             "dateTime" :{
               "type":"date",
               "format":"yyyy-MM-dd'T'HH:mm:ss"
             },
             "count":{
               "type":"integer"
             }
           }
        }
      }
    }
  }
}

Query:

GET testindex11/_search
{
  "query": {
    "nested": {
      "path": "availables",
      "query": {
        "term": {
          "availables.date": {
            "value": "2019-09-10"
          }
        }
      },
       "inner_hits": {} 
    }
  }
}

Result:

 [
      {
        "_index" : "testindex11",
        "_type" : "_doc",
        "_id" : "PXuHQm0B4boMRQnoJOpR",
        "_score" : 1.0,
        "_source" : {
          "Id" : 1234,
          "availables" : [
            {
              "Id" : 4444,
              "date" : "2019-09-10",
              "time" : [
                {
                  "dateTime" : "2019-09-10T09:30:00",
                  "Count" : 50
                },
                {
                  "dateTime" : "2019-09-10T10:00:00",
                  "Count" : 50
                },
                {
                  "dateTime" : "2019-09-10T10:30:00",
                  "Count" : 50
                }
              ]
            },
            {
              "Id" : 5555,
              "date" : "2019-09-11",
              "time" : [
                {
                  "dateTime" : "2019-09-11T09:30:00",
                  "Count" : 50
                },
                {
                  "dateTime" : "2019-09-11T10:00:00",
                  "Count" : 50
                },
                {
                  "dateTime" : "2019-09-11T10:30:00",
                  "Count" : 50
                }
              ]
            },
            {
              "Id" : 6666,
              "date" : "2019-09-12",
              "time" : [
                {
                  "dateTime" : "2019-09-12T09:30:00",
                  "Count" : 50
                },
                {
                  "dateTime" : "2019-09-12T10:00:00",
                  "Count" : 50
                },
                {
                  "dateTime" : "2019-09-12T10:30:00",
                  "Count" : 50
                }
              ]
            }
          ]
        },
        "inner_hits" : {
          "availables" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "testindex11",
                  "_type" : "_doc",
                  "_id" : "PXuHQm0B4boMRQnoJOpR",
                  "_nested" : {
                    "field" : "availables",
                    "offset" : 0
                  },
                  "_score" : 1.0,
                  "_source" : {
                    "Id" : 4444,
                    "date" : "2019-09-10",
                    "time" : [
                      {
                        "dateTime" : "2019-09-10T09:30:00",
                        "Count" : 50
                      },
                      {
                        "dateTime" : "2019-09-10T10:00:00",
                        "Count" : 50
                      },
                      {
                        "dateTime" : "2019-09-10T10:30:00",
                        "Count" : 50
                      }
                    ]
                  }
                }
              ]
            }
          }
        }
      }
    ]