Elasticsearch multilevel nested object filter and update

Question

Elasticsearch : 6.3
I'm trying to update the nested field by filtering the particular Book. Below is the sample record.

{  
   "StoreName":"test",
   "StoreID":"12435",
   "BookInfo":[  
      {  
         "Name":"Book1",
         "Comments":[  
            {  
               "date":"2019-01-07",
               "Comment":"this is a nice book"
            },
            {  
               "date":"2019-01-06",
               "Comment":"this is a nice book"
            },
            {  
               "date":"2019-01-04",
               "Comment":"this is a nice book"
            },
            {  
               "date":"2019-01-03",
               "Comment":"this is a nice book"
            }
         ],
         "UPC":2435466,

      },
      {  
         "Name":"Book2",
         "Comments":[  
            {  
               "date":"2019-01-07",
               "Comment":"this is a nice book"
            },
            {  
               "date":"2019-01-06",
               "Comment":"this is a nice book"
            },
            {  
               "date":"2019-01-04",
               "Comment":"this is a nice book"
            },
            {  
               "date":"2019-01-03",
               "Comment":"this is a nice book"
            }
         ],
         "UPC":576868345657,

      },

   }
]
} 

Here, we have multiple stores and each store has multiple books. For each book, we store the user comments on daily basis. I want to add the date and comment under [BookInfo.Comments] by checking whether the comment on that particular date is already there or not. If it's already there, then I'll need to skip it if not then append the new comment. I tried doing it by update API but I couldn't filter the particular book and update the comments for it from the list of available books. Can someone help me with this? Thanks in advance.

Answer 1

Please see below as how, looking at your document in question, I've created the mapping, the update by query script and the response as how it appears.

Mapping:

PUT mybook
{
  "mappings": {
    "mydocs":{
      "properties":{
        "StoreName": {
          "type": "keyword"
        },
        "StoreID":{
          "type": "keyword"
        },
        "BookInfo":{
          "type": "nested",
          "properties": {
            "Comments" :{
              "type": "nested",
              "properties":{
                "date":{
                  "type": "date"
                },
                "Comment": {
                  "type": "text"
                }
              }
            },
            "Name": {
              "type": "keyword"
            }
          }
        }
      }
    }
  }
}

Query:

Below is the Update By Query that you would need:

POST mybook/_update_by_query
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "StoreName": "test"
          }
        },
        {
          "term": {
            "StoreID": "12435"
          }
        },
        {
          "nested": {
            "path": "BookInfo",
            "query": {
              "term": {
                "BookInfo.Name": "Book1"
              }
            }
          }
        }
      ]
    }
  },
  "script": {
    "lang": "painless",
    "inline": """
      ArrayList list = ctx._source.BookInfo;
      for(int i=0; i<list.size();i++){
        HashMap myHashMap = list.get(i);
        boolean isPresent = false;
        for(int j=0; j<myHashMap.size();j++){
          String mybook = myHashMap.get(params.book_key);
          if(mybook==params.book_value)
          {
            ArrayList mylist = myHashMap.get("Comments");
            for(int k=0; k<mylist.size(); k++){
              HashMap existingMap = mylist.get(k);
              Iterator entries = existingMap.entrySet().iterator();
              while(entries.hasNext()){
                Map.Entry entry = (Map.Entry) entries.next();
                if(entry.getKey()==params.date_key && entry.getValue()==params.date_value){
                  isPresent = true
                }
              }  
            }

            if(!isPresent){
              HashMap myMap = new HashMap();
              myMap.put(params.date_key, params.date_value);
              myMap.put(params.comment_key, params.comment_value);
              mylist.add(myMap);
              isPresent = false;
            }
          }
        }
      }
    """,
    "params": {
      "book_key":   "Name",
      "book_value": "Book1",
      "date_key":   "date",
      "date_value": "2019-01-08",
      "comment_key": "Comment",
      "comment_value": "This is a new comment here"

  }}
}

In summary, I'm trying to add the date and comment for the book having "Name:Book1", "StoreId": "1234" and "StoreName": "test"

Notice the below section in the query:

"params": {
      "book_key":   "Name",
      "book_value": "Book1",
      "date_key":   "date",
      "date_value": "2019-01-08",
      "comment_key": "Comment",
      "comment_value": "This is a new comment here"

}

These are the parameters, which you need to modify(as per your requirement) for the above query to run.

Rest of the painless, script is something I'd suggest you to go through as its just like plain java code and I'm simply traversing it from BookInfo to get to the required document/fields in question and add the date/comment.

Of course, please forgive me for using so many iterations and loops. I'd suggest better way is to construct the document in your application layer completely and re-ingest it.

Response:

{
  "_index": "mybook",
  "_type": "mydocs",
  "_id": "1",
  "_version": 44,
  "found": true,
  "_source": {
    "StoreName": "test",
    "StoreID": "12435",
    "BookInfo": [
      {
        "Comments": [
          {
            "date": "2019-01-07",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-06",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-04",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-03",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-08",
            "Comment": "This is a new comment here"
          }
        ],
        "UPC": 2435466,
        "Name": "Book1"
      },
      {
        "Comments": [
          {
            "date": "2019-01-07",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-06",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-04",
            "Comment": "this is a nice book"
          },
          {
            "date": "2019-01-03",
            "Comment": "this is a nice book"
          }
        ],
        "UPC": 576868345657,
        "Name": "Book2"
      }
    ]
  }
}

Hope this helps!