Named Parameters in the Web Console

[vsr1]

curl should work. post exact command used

[manideepl]

curl http://[node_ip]:8093/query/service -u Administrator:[password] -H "Content-Type: application/json" -d @students.sql > students_zero_points.txt

Where students.sql is

{
"statement":"SELECT t.studentId, t.houseId, ARRAY_LENGTH(t.points) AS points FROM hogwarts t WHERE t.type=\"record-txn\" and t.houseId = \"gryffindor\" and t.studentId IN $students and ARRAY_LENGTH(t.points) = 0;",
"$students": ["b393c367-ed05-445e-9795-fde7099d07e4"]
}

[vsr1]

This query u have ARRAY_LENGTH(t.points) = 0 , use the following index.

CREATE INDEX ix2 ON hogwarts`(`houseId`, array_length(`points`) ,`studentId) 
WHERE (`type` = 'record-txn')

[manideepl]

Still no luck

[manideepl]

It’s working, sorry was using some other IP than that of a Couchbase node.

Can’t thank you enough for this help!! I’ll learn more about Couchbase/N1QL and start contributing back to these forums myself!

[eric_balawejder]

Using the UI like this on version 7.1.5, I am only able to add values that are numbers, “strings”, {braces} and [brackets]. Is there a way to represent NULL values using the work bench?

For example, to be used in a query like, ...AND $propCode IS NULL OR document.propCode = $propCode

[vsr1]

Best optimal way, if possible use 2 statements (one null , non-null) because when named/positional parameters planner has to generate plan generically assume all possible cases.
i.e. $propCode is NULL it needs all rows from indexer with out field value
$propCode is not null only subset even one push to indexer due to OR other one produces all rows from indexer.

[mreiche]

in json, the unquoted string null is null.

Positional Parameters
$1 =
null

select $1 where $1 is null
[
{
“$1”: null
}
]

[eric_balawejder]

Thank you. Total face palm moment. I was using capital letters like the query reserved word and the UI wouldn’t accept it.

[vsr1]

might be oversight. MB-60464

[eric_balawejder]

All of the reserved words are case insensitive so I didn’t think to try.

[eben]

Indeed the values for parameters need to be JSON, not N1QL/SQL++. And JSON doesn’t like NULL.