Elasticsearch Querying Double Nested Object, Match Multiple Rows in Query Within Parent

Question

My data model is related to patient records. At the highest level is the Patient, then their information such as Lab Panels and the individual rows of the results of the panel. So it looks like this: {Patient:{Labs:[{Results:[{}]}]}}

I am able to successfully create the two nested objects Labs nested in Patient and Results nested in Labs, populate it, and query it. What I am unable to successfully do is create a query that constrains the results to a single Lab, and then match by more than one row in the Results object. An example is attached, where I only want labs that are "Lipid Panel" and the results are HDL <= 46 and LDL >= 140.

Any suggestions?

Example Index

PUT localhost:9200/testpipeline

{
    "aliases": {},
    "mappings": {
        "dynamic": "false",
        "properties": {
            "ageAtFirstEncounter": {
                "type": "float"
            },
            "dateOfBirth": {
                "type": "date"
            },
            "gender": {
                "type": "keyword"
            },
            "id": {
                "type": "float"
            },
            "labs": {
                "type": "nested",
                "properties": {
                    "ageOnDateOfService": {
                        "type": "float"
                    },
                    "date": {
                        "type": "date"
                    },
                    "encounterId": {
                        "type": "keyword"
                    },
                    "id": {
                        "type": "keyword"
                    },
                    "isEdVisit": {
                        "type": "boolean"
                    },
                    "labPanelName": {
                        "type": "keyword"
                    },
                    "labPanelNameId": {
                        "type": "float"
                    },
                    "labPanelSourceName": {
                        "type": "text",
                        "store": true
                    },
                    "personId": {
                        "type": "keyword"
                    },
                    "processingLogId": {
                        "type": "float"
                    },
                    "results": {
                        "type": "nested",
                        "properties": {
                            "dataType": {
                                "type": "keyword"
                            },
                            "id": {
                                "type": "float"
                            },
                            "labTestName": {
                                "type": "keyword"
                            },
                            "labTestNameId": {
                                "type": "float"
                            },
                            "resultAsNumber": {
                                "type": "float"
                            },
                            "resultAsText": {
                                "type": "keyword"
                            },
                            "sourceName": {
                                "type": "text",
                                "store": true
                            },
                            "unit": {
                                "type": "keyword"
                            }
                        }
                    }
                }
            },
            "personId": {
                "type": "keyword"
            },
            "processingLogId": {
                "type": "float"
            },
            "race": {
                "type": "keyword"
            }
        }
    }
}

Example Document

PUT localhost:9200/testpipeline/_doc/274746

{
    "id": 274746,
    "personId": "10005786.000000",
    "processingLogId": 51,
    "gender": "Female",
    "dateOfBirth": "1945-01-01T00:00:00",
    "ageAtFirstEncounter": 76,
    "labs": [
        {
            "isEdVisit": false,
            "labPanelSourceName": "Lipid Panel",
            "dataType": "LAB",
            "ageOnDateOfService": 76.9041,
            "results": [
                {
                    "unit": "mg/dL",
                    "labTestNameId": 160,
                    "labTestName": "HDL",
                    "sourceName": "HDL",
                    "resultAsNumber": 46.0,
                    "resultAsText": "46",
                    "id": 2150284
                },
                {
                    "unit": "mg/dL",
                    "labTestNameId": 158,
                    "labTestName": "LDL",
                    "sourceName": "LDL",
                    "resultAsNumber": 144.0,
                    "resultAsText": "144.00",
                    "id": 2150286
                }
            ],
            "id": "9ab9ba84-580b-f2d2-4d32-25658ea5f1bf",
            "sourceId": 2150278,
            "personId": "10003783.000000",
            "encounterId": "39617217.000000",
            "processingLogId": 51,
            "date": "2021-11-08T00:00:00"
        }
    ],
    "lastModified": "2022-03-24T10:21:29.8682784-05:00"
}

Example Query

POST localhost:9200/testpipeline/_search

{
    "fields": [
        "personId",
        "processingLogId",
        "id",
        "gender",
        "ageAtFirstDOS",
        "dateOfBirth"
    ],
    "from": 0,
    "query": {
        "bool": {
            "should": [
                {
                    "constant_score": {
                        "boost": 200,
                        "filter": {
                            "bool": {
                                "_name": "CriteriaFilterId:2068,CriteriaId:1,CriteriaClassId:1,Points:200,T5:False,SoftScore:200",
                                "should": [
                                    {
                                        "bool": {
                                            "must": [
                                                {
                                                    "nested": {
                                                        "path": "labs",
                                                        "inner_hits": {
                                                            "size": 3,
                                                            "name": "labs,CriteriaFilterId:2068,CriteriaId:1,CriteriaClassId:1,Points:200,T5:False,guid:8b41f346-2861-4099-b3c0-fcd6393c367b"
                                                        },
                                                        "query": {
                                                            "bool": {
                                                                "must": [
                                                                    {
                                                                        "bool": {
                                                                            "must": [
                                                                                {
                                                                                    "match_phrase": {
                                                                                        "labs.labPanelSourceName": {
                                                                                            "_name": "CriteriaFilterId:2068,Pipeline.Labs.LabPanelSourceName,es_match_phrase=>'Lipid Panel' found in text",
                                                                                            "query": "Lipid Panel",
                                                                                            "slop": 100
                                                                                        }
                                                                                    }
                                                                                },
                                                                                {
                                                                                    "nested": {
                                                                                        "path": "labs.results",
                                                                                        "inner_hits": {
                                                                                            "size": 3,
                                                                                            "name": "labs.results,CriteriaFilterId:2068,CriteriaId:1,CriteriaClassId:1,Points:200,T5:False,guid:3564e83f-958b-4fe8-848e-f9edb5d7f3b2"
                                                                                        },
                                                                                        "query": {
                                                                                            "bool": {
                                                                                                "must": [
                                                                                                    {
                                                                                                        "bool": {
                                                                                                            "should": [
                                                                                                                {
                                                                                                                    "bool": {
                                                                                                                        "must": [
                                                                                                                            {
                                                                                                                                "range": {
                                                                                                                                    "labs.results.resultAsNumber": {
                                                                                                                                        "lte": 46
                                                                                                                                    }
                                                                                                                                }
                                                                                                                            },
                                                                                                                            {
                                                                                                                                "term": {
                                                                                                                                    "labs.results.labTestNameId": {
                                                                                                                                        "value": 160
                                                                                                                                    }
                                                                                                                                }
                                                                                                                            }
                                                                                                                        ]
                                                                                                                    }
                                                                                                                },
                                                                                                                {
                                                                                                                    "bool": {
                                                                                                                        "must": [
                                                                                                                            {
                                                                                                                                "range": {
                                                                                                                                    "labs.results.resultAsNumber": {
                                                                                                                                        "gte": 140.0
                                                                                                                                    }
                                                                                                                                }
                                                                                                                            },
                                                                                                                            {
                                                                                                                                "term": {
                                                                                                                                    "labs.results.labTestNameId": {
                                                                                                                                        "value": 158
                                                                                                                                    }
                                                                                                                                }
                                                                                                                            }
                                                                                                                        ]
                                                                                                                    }
                                                                                                                }
                                                                                                            ],
                                                                                                            "minimum_should_match": 2
                                                                                                        }
                                                                                                    }
                                                                                                ]
                                                                                            }
                                                                                        }
                                                                                    }
                                                                                }
                                                                            ]
                                                                        }
                                                                    }
                                                                ]
                                                            }
                                                        }
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            ],
            "minimum_should_match": 1,
            "filter": [
         
            ]
        }
    },
    "size": 10,
    "sort": [
        {
            "_score": {
                "order": "desc"
            }
        },
        {
            "processingLogId": {
                "order": "asc"
            }
        },
        {
            "personId": {
                "order": "asc"
            }
        }
    ],
    "_source": false
}