How to use a variable in a n1ql query?

Hello everyone,

I need a little help on a query that seems basic to me. But I’m stuck.

Thanks to you !

I would like to make a simple n1ql query that uses a variable. The idea will be to be able to put my variable in a query chain in order to quickly check the quality of my data.

Query :

declare $a=“actionType”;
select * from cemo where table = $a ;

This returns an error to me. I don’t know how to declare or set my variable called a.

Thank you !

What client are you using? What error are you getting ?

If you are using the cbq shell, you can:

cbq> \set -$a "actionType";
cbq> select * from default.s1.cemo where table = $a;
{
    "requestID": "b5de2664-681d-476d-a030-624436b0c2fc",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "cemo": {
            "table": "actionType"
        }
    }
    ],
    "status": "success",
...

Otherwise I suspect what you’re really looking to do is use a parameterised query in your client - prepare the statement and execute it using the parameters defined:

$ curl -su user:pword http://localhost:8093/query/service -d 'statement=prepare pstmt from select * from default.s1.cemo where table = $param1'
{
"requestID": "590ba4ba-7fdd-4c34-92bb-aeb39dded108",
"signature": "json",
"results": [
...
],
"status": "success",
...

$ curl -su user:pword http://localhost:8093/query/service -d 'statement=execute pstmt using {"param1":"actionType"}'
{
"requestID": "7fa00b21-8ee4-4a67-811b-664f04a611fe",
"signature": {"*":"*"},
"results": [
{"cemo":{"table":"actionType"}}
],
"status": "success",
"metrics": {"elapsedTime": "219.667337ms","executionTime": "219.559005ms","resultCount": 1,"resultSize": 31,"serviceLoad": 3}
}

HTH.

Thank you for your answer.

I would like in the Query Editor.

Under the gear icon in the upper right of the workbench you can define named parameters:

You can then use them in your statements:

image

HTH.

1 Like

Ah great. A big thank you!

I had seen in the documentations the Positional Parameters and Named Parameters but it was using the curl command. But now, I make the link with the documentation I read ;-).

Great !

Thanks a lot