Aggregation and query bool must multiple on array fields "and"

Elastic Stack Elasticsearch
1

Hi all,

I am looking for many hours the solution of a request aggregates.

I have this data : http://pastebin.com/ZUWSDkEX

I want to find the number of person have the consent id 374 and 375 active (status = 1) but dont have the validate id 800 active (status = 1).

I have this query :

But she select and exclude all consent with filter on status.

How i can count only the person have a list of consent id active, and not the other list (active) ?

Thank for you help.

2

The easiest way would be to have different arrays for the active and inactive consents so that your current query would work.

Otherwise you will need to use nested objects and queries, which will work but will also make things slower and more complicated.

3

Hi Adrien,

Thank for your reply, but the mapping is "array" for the consents field, and i cant convert.

Another solution ?

4

There is no way to solve the problem without reindexing since Elasticsearch indexes data in a flat way by default: it has no way to figure which consent the 0s and the 1s map to.

Nested documents would allow you to keep the structure but you would still need to reindex in order to take into account different mappings. That said, I was advise against it since nested documents will make things complicated and slow.

5

Hi,

I have change my mapping, and i have now "nested" object for consents.

So, now, how i can select person have consent id 374 and 375 with status = 1 but dont have the consent id 800 with status = 1 ?

Thank you.