Alphabetical sorting on children

Elastic Stack Elasticsearch
1

Hi,

I currently need to run some queries on my company dataset stored in elastic. A simplified version of the mapping in place is the following:

Mapping for Child
{
	"_parent": {
		"type": "parent"
	},
	"properties": {
		"childId": {
			"type": "long"
		},
		"term": {
			"type": "text",
			"fields": {
				"raw": { 
					"type":  "keyword"
				}
			}
		},
		"integerValue": {
			"type": "integer"
		},
		"doubleValue": {
			"type": "double"
		},
		"boolValue": {
			"type": "boolean"
		},
		"dateTimeValue": {
			"type": "date"
		}
	}
}

Mapping for Parent
{
	"properties": {
		"parentId": {
			"type": "long"
		},
		"title": {
			"type": "text",
			"fields": {
				"raw": { 
					"type":  "keyword"
				}
			}
		},
		"createdDate": {
			"type": "date"
		},
		"createdBy": {
			"type": "integer"
		},
		"geometry": {
			"type": "geo_shape"
		}
	}
}

I am providing the company with a custom built query mechanism to query this dataset by using an abstract company syntax that reflects the structure we have in place. The problem I am facing at the moment is related to sorting and originated two questions.

  1. How do I sort alphabetically on a child attribute in an "has_child" query? In this case how would I sort on the term.raw field of a child type document in an "has_child" query executed on the parent?

  2. Assuming I am now able to sort alphabetically on the child attribute in a "has_child" query, how do I sort in a single query, on multiple fields, BOTH from child and parent while also applying some conditions?

For example, let's say I wanted to retrieve all parents (we always want to retrieve the parents, never the children, children are only used for querying) with createdBy = 123 that also have children with doubleValue = 45.4. All fine up to here, however I'd also like, whichever the results are, for them to be sorted alphabetically on the term.raw field from a child and on the createdDate field. Priority to the term.raw field.

Is it even possible to achieve this? I looked into function_score and it looked powerful but I don't see how to use it with text/keyword fields and how to use it on parent and children at the same time. Am I wandering into a dangerous place no innocent soul should go to?

Thanks in advance for the help!!!

2

Is there some more information I should put in? Is the question badly worded? Also an answer to point 1 alone would be greatly helpful thankssssssssssss :smiley:

3

Did you check docs https://www.elastic.co/guide/en/elasticsearch/reference/5.6/query-dsl-has-child-query.html#_sorting ? Basically, you only have _score, which means that unless you can somehow convert the children's text field to a number, there is not much you can do there.

4

Thank you very much for the answer!!!!!! I guess we are either pushing the system to a limit here or using it incorrectly altogether :sweat_smile:

5

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