Need help on N1QL query my model many to many relationship

Couchbase Server
,
1

my model is here:
“rule”: {
“calculationOrder”: “1”,
“currencyOfSale”: “USD”,
“geographicIdentifier”: {
“geographicInfos”: [
{
“airports”: [
{
“action”: “2”,
“applicationTag”: “Y”,
“value”: “SCQ”
}
],
“cities”: [
{
“action”: “2”,
“applicationTag”: “Y”,
“value”: “GZT”
}
],
“countries”: ,
“iataAreas”: ,
“markets”: ,
“states”: ,
“tableNo”: 1,
“zones”:
},
{
“airports”: [
{
“action”: “2”,
“applicationTag”: “Y”,
“value”: “YOW”
}
],
“cities”: ,
“countries”: [
{
“action”: “2”,
“applicationTag”: “Y”,
“value”: “DE”
}
],
“iataAreas”: ,
“markets”: ,
“states”: ,
“tableNo”: 2,
“zones”:
}
]
}

and I write n1ql query. query is here:
select oms.*
from MERCH_RULE oms
where oms.identifier.recordType = ‘ATPCO_RECORD_X_1’
and oms.identifier.mode = ‘ON’
and oms.identifier.kind IN [‘MANUAL’, ‘AUTO’]
and (oms.geographicIdentifier.geographicInfos.cities.value IN [‘AR’, ‘IST’] OR
oms.geographicIdentifier.geographicInfos.countries.value IN [‘AR’, ‘IST’] OR
oms.geographicIdentifier.geographicInfos.airports.value IN [‘AR’, ‘IST’] OR
oms.geographicIdentifier.geographicInfos.zones.value IN [‘AR’, ‘IST’] OR
oms.geographicIdentifier.geographicInfos.markets.value IN [‘AR’, ‘IST’])

but When I compile this query, I get error. where is my fault consider my model and query.

2

What does the error message say?

  {
    "code": 3000,
    "column": 54,
    "line": 6,
    "msg": "syntax error - line 6, column 54, near '...graphicInfos.cities.', at: value (reserved word)",
    "query": "select oms.*\nfrom my_bucket oms\nwhere oms.identifier.recordType = \"ATPCO_RECORD_X_1\"\nand oms.identifier.mode = \"ON\"\nand oms.identifier.kind IN [\"MANUAL\", \"AUTO\"]\nand (oms.geographicIdentifier.geographicInfos.cities.value IN [\"AR\", \"IST\"] OR\noms.geographicIdentifier.geographicInfos.countries.value IN [\"AR\", \"IST\"] OR\noms.geographicIdentifier.geographicInfos.airports.value IN [\"AR\", \"IST\"] OR\noms.geographicIdentifier.geographicInfos.zones.value IN [\"AR\", \"IST\"] OR\noms.geographicIdentifier.geographicInfos.markets.value IN [\"AR\", \"IST\"])"
  }
3

Parsing of the input failed {“completed”:true,“coreId”:“0x65e9c79c00000002”,“errors”:[{“code”:3000,“message”:“syntax error - at value”,“retry”:false}],“httpStatus”:400,“idempotent”:false,“lastDispatchedFrom”:“10.27.34.45:62020”,“lastDispatchedTo”:“192.168.44.97:8093”,“requestId”:576169,“requestType”:“QueryRequest”,“retried”:0,“service”:{“operationId”:“33055180-e478-4d50-aa81-cd22a92eb511”,“statement”:"select oms.*\r\nfrom MERCH_RULE oms\r\nwhere oms.identifier.recordType = ‘ATPCO_RECORD_X_1’\r\n and oms.identifier.mode = ‘ON’\r\n and oms.identifier.kind IN [‘MANUAL’, ‘AUTO’]\r\n and ( oms.geographicIdentifier.geographicInfos.cities.value IN [‘AR’, ‘IST’]\r\n OR oms.geographicIdentifier.geographicInfos.countries.value IN [‘AR’, ‘IST’]\r\n OR oms.geographicIdentifier.geographicInfos.airports.value IN [‘AR’, ‘IST’]\r\n OR oms.geographicIdentifier.geographicInfos.zones.value IN [‘AR’, ‘IST’]\r\n OR oms.geographicIdentifier.geographicInfos.markets.value IN [‘AR’, 'IST …

4

It seems to indicate that there is a reserved word “value” at line 6, column 54.
If that was the only issue, then escaping all the “value” attribute names with back-ticks would solve it. (i.e. oms.geographicIdentifier.geographicInfos.cities.`value` )

But looking at your model, it appears that countries, airports, zones and markets are all arrays. So you would need to use SATISFIES. Collection Operators | Couchbase Docs

5

thanks for suggestions. I apply them.

6 
SELECT oms.*
FROM MERCH_RULE oms
WHERE oms.`identifier`.recordType = "ATPCO_RECORD_X_1"
      AND oms.`identifier`.mode = "ON"
      AND oms.`identifier`.kind IN ["MANUAL", "AUTO"]
      AND ANY v IN ARRAY_CONCAT(oms.geographicIdentifier.geographicInfos.cities,
                                oms.geographicIdentifier.geographicInfos.countries,
                                oms.geographicIdentifier.geographicInfos.airports,
                                oms.geographicIdentifier.geographicInfos.zones,
                                oms.geographicIdentifier.geographicInfos.markets)
           SATIESFIES v.`value` IN  ["AR", "IST"] END;
7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.