Reserved Keyword VALUE doesn't work on collections even after using escape character

SQL++
1

We have a reserved word as an identifier and are not able to run n1ql queries on them.

The reserved word we have used is ‘value’ and it stores an array of value.

According to this documentation (http://developer.couchbase.com/documentation/server/4.0/n1ql/n1ql-language-reference/reservedwords.html) we have used back ticks to escape the identifier but it doesn’t seem to be working inside that of a collection expression.

select config.* from default AS config where config.documentType = ‘Configuration’ and name =‘AirlineCode’ and ANY val IN config.value SATISFIES val.IsActive = false

Error:
[{“code”:3000,“msg”:“syntax error - at )”},{“original_query”:“select cbq_query_workbench_limit.* from (select config.* from UCShoppingCart AS config where config.documentType = ‘Configuration’ and config.scope=‘AirlineMapping’ and category = 'Mapping’and name =‘AirlineCodeDesc’ and ANY val IN config.value SATISFIES val.IsActive = false ) cbq_query_workbench_limit limit 500;”}]

This executes without an error
select config.* from default AS config where config.documentType = ‘Configuration’ name =‘AirlineCode’ and config.value[0].IsActive = true;

Question is will the escape character work within the collection operator, if not we will have to change the identifier.

Please advise.

2

@srbhaski

I believe your problem is that each identifier should be independently escaped with backticks, rather than wrapping the whole dotted pair of identifiers:

select config.* from default AS config where config.documentType = 'Configuration' and name ='AirlineCode' and ANY val IN `config`.`value` SATISFIES val.IsActive = false END

Brant

1 Like
3

Hi @btburnett3,

Thanks for your reply, but that doesn’t work either.

My Json stored in couchbase is something like this and I am trying to get all inactive or active records.

{
Name : “AirlineCode”,
DocumentType : “Configuration”,
Value : [
{
code : 43,
isActive : true
},
{
code : 42,
isActive : false
}
]
}

4

@srbhaski

The identifiers are also case-sensitive. So if your attribute is named “Value” then you’ll need to adjust your case as well. Several other attributes also have casing issues, I think I’ve adjusted them all below:

select config.* from default AS config where config.DocumentType = 'Configuration' and Name = 'AirlineCode' and ANY val IN `config`.`Value` SATISFIES val.isActive = false END
5

@btburnett3,

Thanks for your reply. It helped a lot.