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

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
}

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.