Index Required for the N1QL

SQL++
1

Hi,

I need help on index for the below query and explain plan. Please help me what is index required for the below query.

explain select object_remove(success, ‘child’).* from SucceedData.RQS_MOUNT success UNNEST success.data AS child where child.order_Number=‘SZZXNFQ’ limit 1

Explain Plan:

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “SucceedData”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “success”,
“keyspace”: “SucceedData”,
“namespace”: “default”,
“projection”: “RQS_MOUNT”
},
{
"#operator": “Unnest”,
“as”: “child”,
“expr”: “(success.data)”
},
{
"#operator": “Filter”,
“condition”: “((child.order_Number) = “S171225K0ZZXNFQ”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “object_remove(success, “child”)”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
},
“text”: “select object_remove(success, ‘child’).* from SucceedData.RQS_MOUNT success UNNEST success.data AS child where child.order_Number=‘SZZXNFQ’ limit 1”
}
]

2

If you are using CB 4.5 and above

CREATE INDEX ix1 ON SucceedData(DISTINCT ARRAY rd.order_Number FOR rd IN RQS_MOUNT.data END);

SELECT ARRAY v FOR v IN RQS_MOUNT.data WHEN v.order_Number != "SZZXNFQ" END FROM SucceedData
WHERE ANY rd IN RQS_MOUNT.data SATISFIES rd.order_Number = "SZZXNFQ" END;
3

Thank you for your response, i created index and tried to run the above mentioned query got below response. Please let me know how to resolve this issue.

[
{
“code”: 3000,
“msg”: “syntax error - at SATISIFIES”,
“query_from_user”: “SELECT ARRAY v FOR v IN RQS_MOUNT.data WHEN v.order_Number != “SZZXNFQ” END FROM SucceedData WHERE ANY rd IN RQS_MOUNT.data SATISIFIES rd.order_Number = “SZZXNFQ” END”
}
]

4

fixed typo in SATISFIES

5

Yes, able to get response from CB, thank you for help.