Query "at least one not in given array"

[ltr31 (Lionel)]

Hello

I have documents with let say positive terms and negative terms like

{
   ...
   "mood" : [
        "happy",
        "excited",
        "furious"
   ],
   ...
}

Positive terms can be an infinite list of values, while negative terms are among 3 only :

• furious
• livid
• scared

Now, I want to query documents that have positive terms even if they contain a negative term. What I don't want is a document which contains only negative moods. Documents all contain at least one mood.

Documents OK :

{ ... "mood" : [ "happy", "honest", "joyful" ] ... }
{ ... "mood" : [ "fabulous", "livid", "scared" ] ... }

Documents not OK :

{ ... "mood" : [ "livid" ] ... }
{ ... "mood" : [ "scared", "furious" ] ... }

Remember that I cannot give an exhaustive list of positive terms expected, the list is infinite. Which is finite is the negative mood list.

I have tried many writings of queries but none gave me satisfaction.

Do you have any ideas how I can achieve this?

Thanks in advance!

[Christian_Dahlqvist]

It sounds to me like this is something that is known at the time the document is indexed or updated, so the most efficient way would probably be to add a positive_mood_flag field to the document and set this to true or false.

You can probably also determine this flag through a runtime field, but indexing it would scale and perform better.