There is no way to do joins between two separate indexes in Elasticsearch. One type per index and then doing joins between those indexes is not possible and I don't see it happening in the foreseeable future. That's just not how Elasticsearch works.
The only way that your use case is going to work is by normalizing to a single document type using the join field as I have described in my earlier post. To answer your first question: yes, unfortunately it's not easy to check individual child mappings like this. That's up to your application now.
Answering question 2: I'm surprised that you find the query results to be significantly different. In fact, I think these are exactly the same, except for the ordering of the fields, which is never guaranteed in JSON.
I'm pasting the results below. Please let me know which differences you see, and I'll try to help you resolve those.
Query 1 response:
{
"took": 7,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 2,
"hits": [
{
"_index": "cic4essupport_new",
"_type": "doc",
"_id": "csar_1234567890210",
"_score": 2,
"_source": {
"gender": "MALE",
"race": "BLACK",
"address_type": "RESIDENCE",
"last_name": "LNAME1",
"weight": 165,
"hair_color": "BLACK",
"fcn": "1234567890210",
"eye_color": "BROWN",
"@timestamp": "2017-11-29T00:51:28.536Z",
"@version": "1",
"geo_code": "38.38755,-122.212282",
"join": {
"name": "csar"
},
"first_name": "FNAME1",
"height": 509
},
"inner_hits": {
"es_070_ori": {
"hits": {
"total": 1,
"max_score": 0.13353139,
"hits": [
{
"_type": "doc",
"_id": "es_070_ori_1234567890210",
"_score": 0.13353139,
"_routing": "csar_1234567890210",
"_source": {
"fcn": "1234567890210",
"@timestamp": "2017-11-29T00:52:18.850Z",
"ori": [
{
"agency_name": "ED SO",
"ori_owner": "CA0040001"
},
{
"agency_name": "YY SO",
"ori_owner": "CA0550020"
},
{
"agency_name": "SCC PD",
"ori_owner": "CA0445300"
}
],
"@version": "1",
"join": {
"parent": "csar_1234567890210",
"name": "es_070_ori"
}
}
}
]
}
},
"es_000_reg": {
"hits": {
"total": 1,
"max_score": 0.13353139,
"hits": [
{
"_type": "doc",
"_id": "es_000_reg_1234567890210",
"_score": 0.13353139,
"_routing": "csar_1234567890210",
"_source": {
"fcn": "1234567890210",
"@timestamp": "2017-11-29T01:09:36.244Z",
"@version": "1",
"registrant": [
{
"reg_status": "Z_OTHERS",
"flag1": "N",
"reg_type": "TERM"
}
],
"join": {
"parent": "csar_1234567890210",
"name": "es_000_reg"
}
}
}
]
}
}
}
}
]
}
}
Query 2 response:
{
"took": 3,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 6,
"max_score": 0,
"hits": []
},
"aggregations": {
"ori_child": {
"doc_count": 2,
"child_nested": {
"doc_count": 5,
"ori_ori_owner.keyword": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "CA0040001 - ED SO",
"doc_count": 1
},
{
"key": "CA0152300 - PPD",
"doc_count": 1
},
{
"key": "CA0330R01 - PCC SO",
"doc_count": 1
},
{
"key": "CA0445300 - SCC PD",
"doc_count": 1
},
{
"key": "CA0550020 - YY SO",
"doc_count": 1
}
]
}
}
},
"registrant_child": {
"doc_count": 2,
"child_nested": {
"doc_count": 2,
"registrant_flag1.keyword": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "N",
"doc_count": 1
},
{
"key": "Y",
"doc_count": 1
}
]
},
"registrant_reg_type.keyword": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "SSS",
"doc_count": 1
},
{
"key": "TERM",
"doc_count": 1
}
]
},
"registrant_reg_status.keyword": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "OOS",
"doc_count": 1
},
{
"key": "Z_OTHERS",
"doc_count": 1
}
]
}
}
}
}
}