Query Filters for Couchbase

Hello,

I’m receiving an error when using query filters on a couchbase server. The query executes successfully without the query filter but I receieve “Error running query: Couchbase connection error” when using a “<column_name>::filter” within the final select statement.

Is this a known issue with couchbase or is there a solution?

Example:
SELECT
MILLIS_TO_STR(t.addedDate*1000, ‘1111-11-11’) AS date
,t.utm AS “utm::filter”"

,COUNT(DISTINCT t.id) AS registrations
,COUNT(DISTINCT p.userId) AS premium_conversions
,COUNT(DISTINCT p.userId)/COUNT(DISTINCT t.id)*100 AS f2p

FROM default AS t
LEFT JOIN default AS p ON t.id = p.userId AND p.type = ‘payment’

WHERE t.type = ‘user’
AND t.addedDate >= (MILLIS(’{{ Date Range.start }}’)/1000)
AND t.addedDate < (MILLIS(DATE_ADD_STR(’{{ Date Range.end }}’,1,‘day’))/1000)

GROUP BY
MILLIS_TO_STR(t.addedDate*1000, ‘1111-11-11’)
,t.utm
ORDER BY registrations DESC
;

Two ideas:

Incorrect Syntax

It looks like your query above has a typo after the first filter declaration. You double-typed the closing quotation character.

Try removing one of them and see if it works.

Invalid identifier syntax

Couchbase uses backticks (not quotation marks) for qualified identifiers. Try the following:

SELECT
MILLIS_TO_STR(t.addedDate*1000, ‘1111-11-11’) AS date
,t.utm AS `utm::filter`

,COUNT(DISTINCT t.id) AS registrations
,COUNT(DISTINCT p.userId) AS premium_conversions
,COUNT(DISTINCT p.userId)/COUNT(DISTINCT t.id)*100 AS f2p

FROM default AS t
LEFT JOIN default AS p ON t.id = p.userId AND p.type = ‘payment’

WHERE t.type = ‘user’
AND t.addedDate >= (MILLIS(’{{ Date Range.start }}’)/1000)
AND t.addedDate < (MILLIS(DATE_ADD_STR(’{{ Date Range.end }}’,1,‘day’))/1000)

GROUP BY
MILLIS_TO_STR(t.addedDate*1000, ‘1111-11-11’)
,t.utm
ORDER BY registrations DESC
;