Where clause query is returning all records irrespective of matching clause

SQL++
1

Hi All,

I have created two documents in the couchbase server with the below data
1){
“Flight”: {
“FlightLegs”: [
{
“FltLegKey”: “UA8812019-04-16ORDNRT”,
“FltNbr”: “881”,
“DepDate”: “2019-03-16”,
“ArrAirport”: “NRT”,
“DepOccurNbr”: “0”,
“ArrOccurNbr”: “0”,
“CarrCd”: “UA”,
“CarrType”: “UA”,
“FltLclOrigDt”: “2019-03-16”,
“FltLegOrigKey”: “UA8812019-03-16ORDNRT”,
“LegNbr”: 1,
“STDLcl”: “2019-03-16 13:05”,
“STDGmt”: “2019-03-16 18:05”,
“STALcl”: “2019-03-17 16:30”,
“STAGmt”: “2019-03-17 07:30”,
“ETDLcl”: “2019-03-16 13:05”,
“ETDGmt”: “2019-03-16 18:05”,
“ETALcl”: “2019-03-17 16:31”,
“ETAGmt”: “2019-03-17 07:31”,
“SchedBRDStartLcl”: “2019-03-16 12:15”,
“SchedBRDStartGmt”: “2019-03-16 17:15”,
“SchedBRDEndLcl”: “2019-03-16 12:50”,
“SchedBRDEndGmt”: “2019-03-16 17:50”,
“EstBRDStartLcl”: “2019-03-16 12:15”,
“EstBRDStartGmt”: “2019-03-16 17:15”,
“EstBRDEndLcl”: “2019-03-16 12:50”,
“EstBRDEndGmt”: “2019-03-16 17:50”,
“NoseNbr”: “2018”,
“EqpCd”: “77Q”,
“IcrEqpCd”: “G3F”,
“PrevNoseNbr”: “2897”,
“PrevEqpCd”: “77Q”,
“PrevIcrEqpCd”: “G3F”,
“OutInd”: 1,
“OffInd”: 1,
“OnInd”: 1,
“InInd”: 1,
“ActiveInd”: 1,
“CnclInd”: 1,
“LegDepStatus”: “Early”,
“LegArrStatus”: “Early”,
“LegStatus”: “Arrived Gate 1 Minute Early”,
“DepDlyRsnCd”: “”,
“ArrDlyRsnCd”: “”,
“DepCntryCd”: “USA”,
“ArrCntryCd”: “JPN”,
“DepGateNbr”: “C31”,
“ArrGateNbr”: “B2”,
“MrktData”: [
{
“MrktCarrCd”: “NH”,
“MrktFltNbr”: “7009”,
“MrktCarrNm”: “ANA All Nippon Airways”
}
],
“PBTs”: [
{
“CabinType”: “J”,
“Capcity”: 44,
“Auth”: 44,
“Booked”: 44,
“Avail”: 0,
“Checkedin”: 24,
“Boarded”: 0,
“Held”: 0
},
{
“CabinType”: “O”,
“Capcity”: 21,
“Auth”: 21,
“Booked”: 21,
“Avail”: 0,
“Checkedin”: 10,
“Boarded”: 0,
“Held”: 0
},
{
“CabinType”: “Y”,
“Capcity”: 243,
“Auth”: 243,
“Booked”: 243,
“Avail”: 1,
“Checkedin”: 160,
“Boarded”: 0,
“Held”: 0
}
],
“Summary”: [
{
“WhlChairCnt”: 6,
“PriorityBagCnt”: 10,
“CheckedBagCnt”: 250,
“UMNRCnt”: 0
}
]
}
]
}
}

2){
“Flight”: {
“FlightLegs”: [
{
“ETAGmt”: “2019-03-17 07:31”,
“ArrCntryCd”: “JPN”,
“DepOccurNbr”: “0”,
“LegArrStatus”: “Early”,
“FltLegOrigKey”: “UA8812019-03-16ORDNRT”,
“FltNbr”: “881”,
“OffInd”: 1,
“CnclInd”: 1,
“PrevNoseNbr”: “2897”,
“DepCntryCd”: “USA”,
“FltLclOrigDt”: “2019-03-16”,
“ArrDlyRsnCd”: “”,
“LegNbr”: 1,
“EstBRDStartLcl”: “2019-03-16 12:15”,
“EstBRDEndLcl”: “2019-03-16 12:50”,
“EqpCd”: “77Q”,
“OutInd”: 1,
“ArrGateNbr”: “B2”,
“FltLegKey”: “UA8812019-04-16ORDNRT”,
“PrevIcrEqpCd”: “G3F”,
“STDGmt”: “2019-03-16 18:05”,
“EstBRDEndGmt”: “2019-03-16 17:50”,
“STDLcl”: “2019-03-16 13:05”,
“PBTs”: [
{
“CabinType”: “J”,
“Avail”: 0,
“Boarded”: 0,
“Auth”: 44,
“Checkedin”: 24,
“Held”: 0,
“Booked”: 44,
“Capcity”: 44
},
{
“CabinType”: “O”,
“Avail”: 0,
“Boarded”: 0,
“Auth”: 21,
“Checkedin”: 10,
“Held”: 0,
“Booked”: 21,
“Capcity”: 21
},
{
“CabinType”: “Y”,
“Avail”: 1,
“Boarded”: 0,
“Auth”: 243,
“Checkedin”: 160,
“Held”: 0,
“Booked”: 243,
“Capcity”: 243
}
],
“CarrType”: “UA”,
“SchedBRDEndGmt”: “2019-03-16 17:50”,
“SchedBRDEndLcl”: “2019-03-16 12:50”,
“ArrAirport”: “NRT”,
“ArrOccurNbr”: “0”,
“STAGmt”: “2019-03-17 07:30”,
“STALcl”: “2019-03-17 16:30”,
“EstBRDStartGmt”: “2019-03-16 17:15”,
“NoseNbr”: “2018”,
“CarrCd”: “UA”,
“ETDLcl”: “2019-03-16 13:05”,
“DepDlyRsnCd”: “”,
“MrktData”: [
{
“MrktCarrNm”: “ANA All Nippon Airways”,
“MrktFltNbr”: “7009”,
“MrktCarrCd”: “NH”
}
],
“SchedBRDStartLcl”: “2019-03-16 12:15”,
“IcrEqpCd”: “G3F”,
“ETDGmt”: “2019-03-16 18:05”,
“LegDepStatus”: “Early”,
“PrevEqpCd”: “77Q”,
“OnInd”: 1,
“InInd”: 1,
“DepGateNbr”: “C31”,
“DepDate”: “2019-03-16”,
“Summary”: [
{
“PriorityBagCnt”: 10,
“WhlChairCnt”: 6,
“UMNRCnt”: 0,
“CheckedBagCnt”: 250
}
],
“ActiveInd”: 1,
“SchedBRDStartGmt”: “2019-03-16 17:15”,
“LegStatus”: “Arrived Gate 1 Minute Early”,
“ETALcl”: “2019-03-17 16:31”
}
]
}
}

Each document has different document Id’s.
I have created the primary index with below statement
CREATE PRIMARY INDEX #primary ON United
and created below two indexes as well
CREATE INDEX idx_sched ON United((distinct (array (v.flight) for v in schedule end)))
CREATE INDEX flight_key ON United(FltLegKey)

Now am using the below query
select * from United where ‘United.FltLegKey=“UA8812017-03-16ORDNRT”’
ideally it should return only one record but its returning both the above records also when I give not matching value in the where clause also its returning both the records.
What need to done to get only matching records,why it is listing all records when i give where clause too.
Could you please help in this.

2

There are multiple issues

  1. The problem is ‘United.FltLegKey=“UA8812017-03-16ORDNRT”’ single quote around whole predicate which is constant and always true.

  2. FltLegKey is under Flight.FlightLegs array

    CREATED INDEX ix1 ON United(DISTINCT ARRAY v.FltLegKey FOR v IN Flight.FlightLegs END);
    SELECT d.*
    FROM United AS d
    WHERE ANY v IN d.Flight.FlightLegs SATISFIES v.FltLegKey = “UA8812017-03-16ORDNRT” END;

3

Hi ,

It’s working fine now without any issues.Thanks for the detailed clarification

Thanks,
Prasanth