Couchbase n1ql match any of a sub document properties value

Question

Another question my document structure is :

{
  "desktop": {
    "default": {
      "s": {
        "camp": {
          "100112": 0,
          "100114": 0,
          "100122": 0
        },
        "score": 1
      }
    }
  }
} 

I want to find document where at least one of the camp properties is greater than 0. I starrt something like :

select * from my_bucket t where ANY camp_id IN desktop.default.s.camp SATISFIES camp_id.value > 0 END

But of course it doesn't work :)

Any clues ?

Thanks in advance !

Answer 1

You're on the right track with the use of the ANY ... IN ... SATISFIES clause.

The major problem with your query is that there isn't a value property in your temporary camp_id object. Instead, you can make use of the OBJECT_VALUES function (https://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/objectfun.html) to pull the values from the camp poperties and compare them directly.

Your query will look similar to the following:

SELECT 
  * 
FROM my_bucket 
WHERE 
  ANY camp_val 
  IN OBJECT_VALUES(default.desktop.default.s.camp) 
  SATISFIES camp_val > 0 
END