Unable to search with child object

Elastic Stack Elasticsearch
1

Hi,

In my scenario I am storing whole dynamoDB event into elastic search, now I want to get whole event by querying it.

Sharing below my index-document structure as well as my json query. What should be my json query to get matching hits on the basis of "CustomerId"

Kindly reply as soon as possible. Thanking you in anticipation. ...

{
  "took": 19,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 1111,
    "max_score": 1,
    "hits": [
      {
        "_index": "promotion_events",
        "_type": "event",
        "_id": "xxxxxxxxxxxx",
        "_score": 1,
        "_source": {
          "eventId": "xxxxxxxxxx",
          "record": {
            "eventSourceARN": "arn:aws:dynamodb:xxxxxxxxxxxxxxxxxxxxx",
            "eventID": "xxxxxxxxxxxxxxxx",
            "eventName": "INSERT",
            "eventVersion": "1.1",
            "eventSource": "aws:dynamodb",
            "awsRegion": "xxxxx",
            "dynamodb": {
              "keys": {
                "id": {  "s": "xxxxxxxxxxxxxxxxxxxxxx"  }  },
              "newImage": {
                "promotionType": { "s": "xxxxx"  },
                "enddate": { "s": "xxxxxx" },
                "productid": {   "s": "0"  },
                "campaignid": {     "s": "xxxxxx"  },
                "**customerid**": {   "s": "xxxxxxx"   },
                "id": { "s": "xxxxxxxxxxxxx"   },
                "promoid": {    "s": "xxxxxxxxxxxxxxx"   },
                "source": {  "s": "xxxxxxx"   },
                "startdate": {   "s": "xxxxxxxxx"  },
                "storeid": { "s": "0"   },
                "barcodeid": {  "s": "xxxxxxx"    },
                "timestamp": {   "s": "xxxxxxxxxxxxxxx"   }
              },
              "sequenceNumber": "xxxxxxxxxxxxxxxxx",
              "sizeBytes": 9031,
              "streamViewType": "NEW_AND_OLD_IMAGES"
            }
          }
        }
      }
]
}
}

My Json Query:

{
  "_source": {
    "include": ["record.*"]
  },
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "term": {
          "record.dynamodb.newImage.customerid": "xxxxxxx"
        }
      }
    }
  }
}

Output:
code : 200 , count :0
{"took":1,"timed_out":false,"_shards":{"total":5,"successful":5,"failed":0},"hits":{"total":0,"max_score":null,"hits":[]}}

2

I took the liberty to edit your post to have some structure for the json part. You can do that by inserting 3 backticks (`) before and after your code.

3

How does the mapping look like?

4

Tanks for your reply
{"promotion_events":{"mappings":{"event":{"properties":{"eventId":{"type":"string"},"record":{"properties":{"awsRegion":{"type":"string"},"dynamodb":{"properties":{"keys":{"properties":{"id":{"properties":{"s":{"type":"string"}}}}},"newImage":{"properties":{"barcodeid":{"properties":{"s":{"type":"string"}}},"campaignid":{"properties":{"s":{"type":"string"}}},"customerid":{"properties":{"s":{"type":"string"}}},"enddate":{"properties":{"s":{"type":"string"}}},"id":{"properties":{"s":{"type":"string"}}},"marketingType":{"properties":{"m":{"properties":{"id":{"properties":{"s":{"type":"string"}}},"metadata":{"properties":{"m":{"properties":{"marketingAttributes":{"properties":{"m":{"properties":{"description":{"properties":{"s":{"type":"string"}}},"id":{"properties":{"s":{"type":"string"}}},"image":{"type":"object"},"name":{"properties":{"s":{"type":"string"}}},"offerValue":{"properties":{"s":{"type":"string"}}},"previousPrice":{"properties":{"s":{"type":"string"}}},"price":{"properties":{"s":{"type":"string"}}},"sellingUOM":{"properties":{"s":{"type":"string"}}}}}}},"source":{"properties":{"m":{"properties":{"description":{"properties":{"s":{"type":"string"}}},"name":{"properties":{"s":{"type":"string"}}},"sourceId":{"properties":{"s":{"type":"string"}}}}}}}}}}},"promotionActive":{"properties":{"m":{"properties":{"endDate":{"properties":{"s":{"type":"string"}}},"startDate":{"properties":{"s":{"type":"string"}}}}}}},"promotionType":{"properties":{"s":{"type":"string"}}},"reward":{"properties":{"l":{"properties":{"m":{"properties":{"id":{"properties":{"n":{"type":"string"}}}}}}}}}}}}},"productid":{"properties":{"s":{"type":"string"}}},"promoid":{"properties":{"s":{"type":"string"}}},"promotionType":{"properties":{"s":{"type":"string"}}},"source":{"properties":{"s":{"type":"string"}}},"startdate":{"properties":{"s":{"type":"string"}}},"storeid":{"properties":{"s":{"type":"string"}}},"timestamp":{"properties":{"s":{"type":"date","format":"strict_date_optional_time||epoch_millis"}}}}},"oldImage":{"properties":{"barcodeid":{"properties":{"s":{"type":"string"}}},"campaignid":{"properties":{"s":{"type":"string"}}},"customerid":{"properties":{"s":{"type":"string"}}},"enddate":{"properties":{"s":{"type":"string"}}},"id":{"properties":{"s":{"type":"string"}}},"marketingType":{"properties":{"m":{"properties":{"id":{"properties":{"s":{"type":"string"}}},"metadata":{"properties":{"m":{"properties":{"marketingAttributes":{"properties":{"m":{"properties":{"description":{"properties":{"s":{"type":"string"}}},"id":{"properties":{"s":{"type":"string"}}},"image":{"type":"object"},"name":{"properties":{"s":{"type":"string"}}},"offerValue":{"properties":{"s":{"type":"string"}}},"previousPrice":{"properties":{"s":{"type":"string"}}},"price":{"properties":{"s":{"type":"string"}}},"sellingUOM":{"properties":{"s":{"type":"string"}}}}}}},"source":{"properties":{"m":{"properties":{"description":{"properties":{"s":{"type":"string"}}},"name":{"properties":{"s":{"type":"string"}}},"sourceId":{"properties":{"s":{"type":"string"}}}}}}}}}}},"promotionActive":{"properties":{"m":{"properties":{"endDate":{"properties":{"s":{"type":"string"}}},"startDate":{"properties":{"s":{"type":"string"}}}}}}},"promotionType":{"properties":{"s":{"type":"string"}}},"reward":{"properties":{"l":{"properties":{"m":{"properties":{"id":{"properties":{"n":{"type":"string"}}}}}}}}}}}}},"productid":{"properties":{"s":{"type":"string"}}},"promoid":{"properties":{"s":{"type":"string"}}},"promotionType":{"properties":{"s":{"type":"string"}}},"source":{"properties":{"s":{"type":"string"}}},"startdate":{"properties":{"s":{"type":"string"}}},"storeid":{"properties":{"s":{"type":"string"}}},"timestamp":{"properties":{"s":{"type":"date","format":"strict_date_optional_time||epoch_millis"}}}}},"sequenceNumber":{"type":"string"},"sizeBytes":{"type":"long"},"streamViewType":{"type":"string"}}},"eventID":{"type":"string"},"eventName":{"type":"string"},"eventSource":{"type":"string"},"eventSourceARN":{"type":"string"},"eventVersion":{"type":"string"}}}}}}}}

5

Hi,

I'm afraid this is impossible to read for me. Can you please use a tool like http://jsonlint.com or something similar in the future to format you output? And use the 3 backticks (`) before and after your code as I mentioned above?

In any case, I spotted that your customerid mapping looks like this:

"customerid": {
    "properties": {
        "s": {
            "type": "string"
        }
    }
}

So there seems to be this little s field inside each mapping that you should probably consider when writing your queries. Having said that, the mapping looks somewhat auto-generated from some database schema and looks quiet deep to me. I'm not sure if I'd expect it to work terribly well with this level of nesting.

6

:slight_smile:
I can understand, but this textbox has only 5000 character capacity, so no option left with me. I would like to suggest you to copy this json text into your formating tool it will give you formatted json.
And you are right its generated mapping from dynamoDb Streams.

7

If the formatted version is too big, post it as a gist on GitHub (or use some other service) and link to it instead.

8

what is 's'?
please use this instead!

"customerid": {
       "type": "string"
}

{
  "_source": {
    "include": ["record.*"]
  },
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "term": {
          "record.dynamodb.newImage.customerid": "xxxxxxx"
        }
      }
    }
  }
}