How to create N1QL query with array parameter

.NET SDK
, ,
1

I struggled with this initially & thought I should post this in case anyone else has this trouble.

I wanted to create a parameterized query in which one of the parameters is an array/list. For example:

var query = @"
SELECT
doctype1.user_id
FROM bucket doctype1
JOIN bucket doctype2
ON doctype2.user_id = doctype1.user_id
AND doctype2.type = ‘doctype2’
AND doctype2.value.Value IN [$myset]
WHERE doctype1.type = ‘doctype1’
AND doctype1.value.Type = $type
ORDER BY doctype1.user_id
LIMIT 2
OFFSET 0";

I tried a couple of other things like doing $myset = “‘val1’, ‘val2’”, $myset = “[‘val1’, ‘val2’]”, $myset = new List() {“val1”, “val2”}, $myset = new[ ] { “val1”, “val2”}

The solution ended up being to remove the brackets around my parameter and pass either an array or a list of strings:

AND doctype2 . value . Value IN $myset

and use either $myset = new List() {“val1”, “val2”} OR $myset = new[ ] { “val1”, “val2”}

No where in other posts I found or in the docs suggested how to do a parameter as an array :slight_smile:

2 Likes
2

Hi @coreyponsv,

Thank you for sharing this excellent tip :slight_smile:

I would like to know the places where you tried looking were unable to find this solution (so that we can try to improve documentation).

Also, I don’t know if you’re into blogging or dev.to or anything like that, but if you wrote up a short post, I would be happy to review/promote/share it with the community (along with any other tips you might learn and share along the way). We’re working on a platform now to encourage (and reward) those like yourself who are willing to teach and share their expertise!

1 Like
3

FYI: Right side of IN clause requires ARRAY (i.e constant Array or expression that evaluates to array that includes named or query parameters.

       field1  IN ["a","b","c"]    -- constant ARRAY
       field1 IN  [ "a"||"b", "c"]  --- ARRAY constructed with constant expression
       filed1  IN ["a', filed3]        --- Array contains dynamic expressions
       filed1 IN  $f1    -- Named parameters and it must be ARRAY
        filed1 IN [$f2,$f3]  -- Array constructed dynamically with named parameters
       filed1 IN filed2   --- filed2 must be ARRAY

named parameters must be valid JOSN datatype.
$myset = “‘val1’, ‘val2’”, $myset = “[‘val1’, ‘val2’]” these two are JSON strings not an ARRAY

1 Like
4

Using Named parameters (the syntax is Java):

  var query =
    "SELECT h.name, h.city, h.state " +
    "FROM `travel-sample` h " +
    "WHERE h.type = $type " +
      "AND h.city in $city "+
      "LIMIT 10;";

  QueryResult result = cluster.query(query,
    queryOptions().parameters(
      JsonObject.create()
        .put("type", "hotel")
        .put("city",  List.of("Malibu","Medway"))
    ));