07-30-2023, 02:22 PM
I've got the following simple mapping:
"element": {
"dynamic": "false",
"properties": {
"id": { "type": "string", "index": "not_analyzed" },
"group": { "type": "string", "index": "not_analyzed" },
"type": { "type": "string", "index": "not_analyzed" }
}
}
Which basically is a way to store `Group` object:
{
id : "...",
elements : [
{id: "...", type: "..."},
...
{id: "...", type: "..."}
]
}
I want to find how many different groups exist sharing the same set of element types (ordered, including repetitions).
An obvious solution would be to change the schema to:
"element": {
"dynamic": "false",
"properties": {
"group": { "type": "string", "index": "not_analyzed" },
"concatenated_list_of_types": { "type": "string", "index": "not_analyzed" }
}
}
But, due to the requirements, we need to be able to exclude some types from group by (aggregation) :(
All fields of the document are mongo ids, so in SQL I would do something like this:
SELECT COUNT(id), concat_value FROM (
SELECT GROUP_CONCAT(type_id), group_id
FROM table
WHERE type_id != 'some_filtered_out_type_id'
GROUP BY group_id
) T GROUP BY concat_value
In Elastic with given mapping it's really easy to filter out, its also not a problem to count assuming we have a concated value. Needless to say, sum aggregation does not work for strings.
How can I get this working? :)
Thanks!
"element": {
"dynamic": "false",
"properties": {
"id": { "type": "string", "index": "not_analyzed" },
"group": { "type": "string", "index": "not_analyzed" },
"type": { "type": "string", "index": "not_analyzed" }
}
}
Which basically is a way to store `Group` object:
{
id : "...",
elements : [
{id: "...", type: "..."},
...
{id: "...", type: "..."}
]
}
I want to find how many different groups exist sharing the same set of element types (ordered, including repetitions).
An obvious solution would be to change the schema to:
"element": {
"dynamic": "false",
"properties": {
"group": { "type": "string", "index": "not_analyzed" },
"concatenated_list_of_types": { "type": "string", "index": "not_analyzed" }
}
}
But, due to the requirements, we need to be able to exclude some types from group by (aggregation) :(
All fields of the document are mongo ids, so in SQL I would do something like this:
SELECT COUNT(id), concat_value FROM (
SELECT GROUP_CONCAT(type_id), group_id
FROM table
WHERE type_id != 'some_filtered_out_type_id'
GROUP BY group_id
) T GROUP BY concat_value
In Elastic with given mapping it's really easy to filter out, its also not a problem to count assuming we have a concated value. Needless to say, sum aggregation does not work for strings.
How can I get this working? :)
Thanks!