3

Is there a CloudSearch structured query to return results that do not have a value within a field? For example, I have a field called target_date that does not always have a value and I want to return all results with no target_date. This field is not zero'd out or set to a default; it doesn't exist at all for items without the date.

There is another case too. I need to return all results after a target_date AND include any results without an existing date. The structured query I am using is target_date:['2000-03-03T00:00:00Z',}. The query to find non-existing dates should work with an and operator, like: (and target_date:['2000-03-03T00:00:00Z',} [QUERY_GOES_HERE])

Siphon
  • 1,041
  • 8
  • 17

2 Answers2

5

I believe you're asking the same thing as Amazon Cloudsearch : Filter if exists

To summarize the options from there:

  1. Add a new boolean field called 'has_target_date'
  2. Set a default target_date (eg 1/1/1970) to mean that it doesn't exist
  3. The hack: (range field=target_date [0,})

Any of those options should work with the QUERY_GOES_HERE portion of your question.

Community
  • 1
  • 1
alexroussos
  • 2,671
  • 1
  • 25
  • 38
  • The query `(range field=target_date [0,})` doesn't work on a date field and `(range field=target_date ['0000-00-00T00:00:00Z',})` gave me results with existing dates. If there's nothing but a 'hack' that can do this, then I'm thinking a default value would be a better solution. Do you have any other suggestions for this? – Siphon Mar 05 '15 at 14:50
  • 2
    If `(range field=target_date ['0000-00-00T00:00:00Z',})` gave you results _with_ dates, you could try negating it with ` (NOT (range field=target_date ['0000-00-00T00:00:00Z',}))` (see the NOT operator : http://docs.aws.amazon.com/cloudsearch/latest/developerguide/searching-compound-queries.html) – alexroussos Mar 05 '15 at 16:06
  • That works out perfectly! Sometimes the simple details are the ones we miss. – Siphon Mar 05 '15 at 17:02
  • Woohoo! Glad it worked. Hopefully someday they'll add a proper way to search for null values. – alexroussos Mar 05 '15 at 18:15
2

You can also construct a lonnnng query to exclude any items that have a string in that field. Essentially you say "Give me everything that doesnt start with A-Z or 0-9"

In preliminary tests, my results were all items that were missing the field completely. Example code is below for a compound (structured) query on a "title" field to find all items that had no title at all

(and (not (prefix field='title' 'a')) (not (prefix field='title' 'b')) (not (prefix field='title' 'c')) (not (prefix field='title' 'd')) (not (prefix field='title' 'e')) (not (prefix field='title' 'f')) (not (prefix field='title' 'g')) (not (prefix field='title' 'h')) (not (prefix field='title' 'i')) (not (prefix field='title' 'j')) (not (prefix field='title' 'k')) (not (prefix field='title' 'l')) (not (prefix field='title' 'm')) (not (prefix field='title' 'n')) (not (prefix field='title' 'o')) (not (prefix field='title' 'p')) (not (prefix field='title' 'q')) (not (prefix field='title' 'r')) (not (prefix field='title' 's')) (not (prefix field='title' 't')) (not (prefix field='title' 'u')) (not (prefix field='title' 'v')) (not (prefix field='title' 'w')) (not (prefix field='title' 'x')) (not (prefix field='title' 'y')) (not (prefix field='title' 'z')) (not (prefix field='title' '1')) (not (prefix field='title' '2')) (not (prefix field='title' '3')) (not (prefix field='title' '4')) (not (prefix field='title' '5')) (not (prefix field='title' '6')) (not (prefix field='title' '7')) (not (prefix field='title' '8')) (not (prefix field='title' '9')) (not (prefix field='title' '0')))
Eric Solan
  • 41
  • 3