prepared-statementcouchbasesql++

What is the correct syntax for Prepared Statement at Couchbase?

I'm trying to create Prepared Statement with named parameter at Couchbase 7.1 as follows:

PREPARE index_1 AS SELECT DISTINCT $column FROM bucket_name;
EXECUTE index_1 USING {'column': 'Name'};

I would like to receive the JSON array with distinct values for a given column name which is achievable using SQL syntax e.g.: SELECT DISTINCT Name FROM bucket_name;

Instead of this, I receive only given column name.

How to do it correctly?

Solution

  • Named parameters or positional parameters can be used for values only. Not for field names or identifiers.

    Above statements and results are correct.

    If you must use the following if you want distinct Name field values.

    PREPARE index_1 AS SELECT DISTINCT Name FROM bucket_name;
EXECUTE index_1;

    OR

    PREPARE p1 AS SELECT DISTINCT RAW t.[$column] FROM bucket_name AS t;
EXECUTE p1 USING {'column': 'Name'};

    see N1QL query to select a dynamic key in Couchbae explanation of dynamic fields