How to do N1QL query properly

I am trying to implement one query in Couchbase by using the web interface of couchbase and also using curl to measure the timing performance. I used this query, but it shows me null result. I used the similar query in MySQL and it is working fine

SELECT avg(`EB1.PYG.R.LWDP.001.INST`) as value_avg1,
avg(`EB1.PYG.T.TOP.001.INST`) as value_avg2, AVG(`EB1.PYG.R.LWDP.001.INST`)
+ ((5.67E-8) * (POWER(AVG(`EB1.PYG.T.TOP.001.INST`) + 273.14, 4))) as 
final_value FROM EBM_DAS1 WHERE META().id >= "1456411717000000" AND
META().id <= "1456412317000000";

The output of above query was Null, so I try to make a simple query, e.g

SELECT avg(`EB1.PYG.R.LWDP.001.INST`) as Average FROM EBM_DAS1 WHERE 
META().id >= "1456411717000000" AND META().id <= "1456412317000000";

and it also result in Null, but If I use the same query to fetch the data, it fetches and returns the data.

SELECT (`EB1.PYG.R.LWDP.001.INST`) FROM EBM_DAS1 WHERE META().id
>= "1456411717000000" AND META().id <= "1456412317000000";

If I just use this query to fetch data, it gives me data and works properly but it doesn’t calculate and return average value
and the data it returns looks like

[
  {
     "EB1.PYG.R.LWDP.001.INST": "-84.23"
  },
  {
    "EB1.PYG.R.LWDP.001.INST": "-83.23"
  },
 {
   "EB1.PYG.R.LWDP.001.INST": "-82.53"
 },
...
...
...
...
 {
   "EB1.PYG.R.LWDP.001.INST": "-80.1"
 } 
]

I also tried with curl command to fetch the data and curl command is as follow.

curl -v http://localhost:8093/query/service -d 'statement=SELECT 
avg(LENGTH(`EB1.PYG.R.LWDP.001.INST`)) as value_avg1, 
avg(LENGTH(`EB1.PYG.T.TOP.001.INST`)) as value_avg2, 
AVG(LENGTH(`EB1.PYG.R.LWDP.001.INST`)) + ((5.67E-8) * 
(POWER(AVG(`EB1.PYG.T.TOP.001.INST`) + 273.14, 4))) as final_value 
FROM EBM_DAS1 WHERE META().id >= "1456411717000000" AND 
META().id <= "1456412317000000"&pretty=false';

and it result in

{
"requestID": "b6d49d1a-6016-4444-914c-4df990e43d9f", 
"errors": [{"code":3000,"msg":"syntax error - at ("}],
"status": "fatal",
"metrics": {"elapsedTime": "625.736µs","executionTime": "586.512µs",
"resultCount": 0,"resultSize": 0,"errorCount": 1}
}

I run successfully similiar query in MySQL to obtain the desire results.
Can someone guide me why I am getting ‘null’ results and curl is also not working. I am beginner to database technology and specially to couchbase. I try my best to figure out the problem but unable to find. I would be grateful. Thanks

Your values are strings. JSON is type less and you can’t do SUM or AVG on strings. MYSQL has type so it does automatic promotion. You need to manually convert in the query like avg(TONUMBER(EB1.PYG.R.LWDP.001.INST)).

1 Like

@vsr1 Thanks for your kind response. it works for this query and output average of those fields.

SELECT avg(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) as Average, 
avg(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) as value_avg2 FROM EBM_DAS1 WHERE
META().id >= "1456411717000000" AND META().id <= "1456412317000000"; 

But when I run this query, it still shows null values

SELECT avg(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) as value_avg1, 
avg(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) as value_avg2, 
AVG(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) + ((5.67E-8) *
(POWER(AVG(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) + 273.14, 4))) as final_value 
FROM EBM_DAS1 WHERE META().id >= 1456411717000000 AND 
META().id <= 1456412317000000;

META().id needs to be string so add quotes around number

1 Like

@vsr1 oh I forget the double quotes again. .thanks a lot for your quick response. It works perfectly fine now with the select command. and if i use this curl command for average two simple fields, it return me average values

curl -v http://localhost:8093/query/service -d 'statement=SELECT 
avg(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) as Average, 
avg(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) as value_avg2 FROM EBM_DAS1 WHERE 
META().id >= "1456411717000000" AND META().id <= "1456412317000000"
&pretty=false';

but the curl command for overall query is showing some problem. .Can you give it a look. .because I try to look at this but didn’t see any mistake.

curl -v http://localhost:8093/query/service -d 'statement=SELECT 
avg(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) as value_avg1, 
avg(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) as value_avg2, 
AVG(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) + ((5.67E-8) *
(POWER(AVG(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) + 273.14, 4)))
as final_value FROM EBM_DAS1 WHERE META().id >= "1456411717000000" AND
META().id <= "1456412317000000"&pretty=false';

it gives me this error:

{
"requestID": "eed40198-3924-4795-b8da-d7159f55c41a",  
"errors": [{"code":3000,"msg":"syntax error - at ("}],
"status": "fatal",
"metrics": {"elapsedTime": "659.878µs","executionTime": "620.858µs",
"resultCount": 0,"resultSize": 0,"errorCount": 1}
}

If predicte doesn’t qualify any items aggregates has to give NULL. What version of the Couchbase you are using.

@vsr1 I am using version 4.6.2. But if i use the first curl command, it returns me average of two fields, but if i use the second curl command, it gives the above mention error. I would be grateful if you give it a look cox I am trying to use curl for performance measurement. and i try to change it but every-time i get the same error.

@vsr1 I figure out the problem. Should I ask this as a new question. . the curl statement has a problem with + sign. If i use * sign instead of +, it works and give results. but with + sign, it shows error.

You need to escape + because it conflicting with http args.

You should use the following

curl -v http://localhost:8093/query/service -H "Content-Type: application/json" -d '{"statement": "SELECT avg(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)), avg(TONUMBER(`EB1.PYG.T.TOP.001.INST`)), (AVG(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) + ((5.67E-8) * (POWER(AVG(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) + 273.14, 4)))) FROM default WHERE META().id >= \"1456411717000000\" AND META().id <= \"1456412317000000\"","pretty":false}';
OR
curl -v http://localhost:8093/query/service -d 'statement=SELECT
avg(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)),
avg(TONUMBER(`EB1.PYG.T.TOP.001.INST`)),
(AVG(TONUMBER(`EB1.PYG.R.LWDP.001.INST`)) %2B ((5.67E-8) *
(POWER(AVG(TONUMBER(`EB1.PYG.T.TOP.001.INST`)) %2B 273.14, 4))))
FROM default WHERE META().id >= "1456411717000000" AND
META().id <= "1456412317000000"';
1 Like

@vsr1 When i run this statement, it give me result

{
"requestID": "05165595-0d71-4911-b8d6-c3bea7e7a50f",
"errors": [{"code":3000,"msg":"syntax error - at 0"}],
"status": "fatal",
"metrics": {"elapsedTime": "1.400477ms","executionTime": "1.364751ms",
"resultCount": 0,"resultSize": 0,"errorCount": 1}
}

It looks like back ticks are removed. Please re-try from previous post

1 Like

@vsr1 it works completely fine now. Yes I didn’t notice the back ticks. Thank you so much for your precious help and time. Have a good day. .:slightly_smiling_face::slightly_smiling_face: