Optional parameters with n1ql and spel

Question

I have a query with multiples parameters that are optional.

@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter}" +
            "#{(#id != null) ? ' AND META().id = $id' : ''}" +
            "#{(#name != null) ? ' AND name like $name' : ''}" +
            "#{(#time != null) ? ' AND time = $time' : ''}" +
            "#{(#x == null) ? '' : ' AND x = $x' }" +
    )
    org.springframework.data.domain.ObjectDRP<OjectDRP> findAllAdvanced(
            @Param("id") String id,
            @Param("name") String name,
            @Param("time") Boolean time,
            @Param("x") String x

And I have this url: http://localhost:4200/api/objectDRP?page=0&size=10&x=test&sort=lastModifiedDate,desc

The server gives me:

{"msg":"Error evaluating filter. - cause: No value for named parameter $x.","code":5010}

But if I change the query to:

@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter}" +
            "AND $x is NOT NULL"
            "#{(#id != null) ? ' AND META().id = $id' : ''}" +
            "#{(#name != null) ? ' AND name like $name' : ''}" +
            "#{(#time != null) ? ' AND time = $time' : ''}" +
            "#{(#x == null) ? '' : ' AND x = $x' }" +
    )

It will work for every set of params: name, time, x; name, time; time...etc.
It's the first time for me with n1ql and spel so I don't understand very well what happens. Can anyone give me some help to solve this issue.

2.What to do in a n1ql query for a null required parameter?

E.q.:

"SELECT COUNT(*) AS count FROM test_pages WHERE _class = "com.test.testM.test.example.example"  AND test = null"

Answer 1

Once you used named/position parameters you must pass them during execution otherwise query returns error during execution if that part of predicate is executed.

NULL means undefined in SQL comparison with NULL always false (i.e x == NULL, undefined you can't compare with any value). If you query needs such comparison it must use

 x IS MISSING
 x IS NOT MISSING
 x IS NULL
 x IS NOT NULL
 x IS VALUED
 x IS NOT VALUED

You can also use something below

x = IFMISSINGORNULL($x,"")

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/comparisonops.html

The right query will be

SELECT COUNT(1) AS count 
FROM `testM`
WHERE _class = "com.test.testL.example.example"  
AND test IS NULL;