Elasticsearch Query for Retrieving Dependencies of multiple documents in one index

Elastic Search
1

Description:
I'm facing a challenge with formulating an Elasticsearch query to retrieve dependencies related to a given parent_id. Here's a brief description of the problem:

I have a set of independent documents with fields like identifier, parent_id, ticket_title, and ticket_type. The goal is to query all dependencies for documents with the ticket_type "Task," even when parent_id has its own set of dependencies. These dependencies can form a chain, and the nesting level is not consistent across documents.

Typically, Elasticsearch uses nested or parent-child relationships for hierarchical data, but in this case, the documents are independent. I'm seeking assistance in formulating a query that can effectively navigate through the dependencies, regardless of their nested or independent nature, and provide a comprehensive view of all related dependencies for a given parent_id. I'm also interested if I can query to which project this task is contributing.

Example:

PUT ticket_parent/_doc/1
{
"identifier" : 1,
"parent_id" : 2,
"ticket_title": "Im a task",
"ticket_type":"Task"
}

PUT ticket_parent/_doc/2
{
"identifier" : 2,
"ticket_title": "Im a story",
"parent_id" : 3,
"ticket_type":"Story",
"child_id" : 1
}

PUT ticket_parent/_doc/3
{
"identifier" : 3,
"ticket_title": "Im a feature",
"ticket_type":"Feature",
"child_id" : 2,
"project" : "foobar"
}

I've tried using the "terms" aggregation for parent_id, but the results aren't capturing all dependencies. Any suggestions or guidance on constructing a suitable Elasticsearch query for this scenario would be greatly appreciated.

Thank you!

2

Hey @Super8film87 thanks for the question!

You're running into this issue because Elasticsearch doesn't support recursive lookups or joins like something like a SQL database would.

There are a few workarounds that might work for your use case though.

  1. I know that you discounted the nested field type but it could make sense in this case, especially for the parent feature.
  2. You could try flattening your data. In this case for example assuming that the hierarchy is set to three levels you could add additional fields, e.g. a ticket type of task could have a story_parent field, and a ticket type of story could have a feature_parent field etc. You could then use boolean queries to search your data.
  3. You could try sending in multiple requests, to get various hierarchies of issues.
  4. You may be interested in a new tech preview feature ES|QL
3

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