Query "at least one not in given array"

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!

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.

1 Like

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