Collection Operators

Collection operators enable you to evaluate expressions over collections or objects. The operators include ANY, EVERY, ARRAY, FIRST, EXISTS, IN, and WITHIN.

ANY

ANY is a range predicate that tests a Boolean condition over the elements or attributes of a collection, object, or objects. It uses the IN and WITHIN operators to range through the collection.

Syntax ANY SOME EVERY expr

ANY var1 ( IN | WITHIN ) expr1
    [ , var2 ( IN | WITHIN ) expr2 ]*
    SATISFIES condition  END
Arguments
var1, var2, … , varX

A string or expression that evaluates to a string representing the variable name in the ANY loop.

expr1, expr2, … , exprX

A string or expression that evaluates to a string representing the array to loop through.

condition

An expression representing the limiting or matching clause to test against.

Return Values

If at least one item in the array satisfies the ANY expression, then it returns the entire array; otherwise, it returns an empty array.

ANY Example 1a: Retrieve the array of airline flight schedules from Albuquerque (ABQ) to Atlanta (ATL) if any of the flights are after 23:40.

SELECT * FROM `travel-sample`
WHERE type="route" AND airline="KL" AND sourceairport="ABQ" AND destinationairport="ATL"
AND ANY departure IN schedule SATISFIES departure.utc > "23:41" END;

Since the last flight departs at 23:41, this query results in the entire array.

Results:
[
  {
    "travel-sample": {
      "airline": "KL",
      "airlineid": "airline_3090",
      "destinationairport": "ATL",
      "distance": 2038.3535078909663,
      "equipment": "757 320",
      "id": 36159,
      "schedule": [
        {
          "day": 0,
          "flight": "KL938",
          "utc": "03:54:00"
        },
        {
          "day": 0,
          "flight": "KL270",
          "utc": "16:57:00"
        },
        {
          "day": 0,
          "flight": "KL222",
          "utc": "05:17:00"
        },
        {
          "day": 0,
          "flight": "KL198",
          "utc": "05:07:00"
        },
        {
          "day": 0,
          "flight": "KL858",
          "utc": "18:59:00"
        },
        {
          "day": 1,
          "flight": "KL672",
          "utc": "19:19:00"
        },
        {
          "day": 2,
          "flight": "KL832",
          "utc": "15:54:00"
        },
        {
          "day": 2,
          "flight": "KL173",
          "utc": "07:18:00"
        },
        {
          "day": 2,
          "flight": "KL520",
          "utc": "09:05:00"
        },
        {
          "day": 3,
          "flight": "KL259",
          "utc": "11:05:00"
        },
        {
          "day": 3,
          "flight": "KL065",
          "utc": "17:11:00"
        },
        {
          "day": 4,
          "flight": "KL469",
          "utc": "06:06:00"
        },
        {
          "day": 4,
          "flight": "KL509",
          "utc": "06:53:00"
        },
        {
          "day": 4,
          "flight": "KL096",
          "utc": "10:29:00"
        },
        {
          "day": 5,
          "flight": "KL347",
          "utc": "08:51:00"
        },
        {
          "day": 5,
          "flight": "KL281",
          "utc": "06:26:00"
        },
        {
          "day": 5,
          "flight": "KL567",
          "utc": "03:54:00"
        },
        {
          "day": 5,
          "flight": "KL169",
          "utc": "23:41:00"
        },
        {
          "day": 6,
          "flight": "KL884",
          "utc": "00:36:00"
        },
        {
          "day": 6,
          "flight": "KL886",
          "utc": "14:13:00"
        },
        {
          "day": 6,
          "flight": "KL636",
          "utc": "17:40:00"
        }
      ],
      "sourceairport": "ABQ",
      "stops": 0,
      "type": "route"
    }
  }
]

ANY Example 1b: But if you change the SATISFIES clause to 1 minute after the last flight ("23:42"), then the resulting array is empty.

{
  "results": []
}

ANY Example 1c: If you want to list only the matching arrays of flights instead of all the flight arrays, then see the ARRAY Example.

The SOME and ANY operators perform the same function and can be used interchangeably.

ARRAY

The ARRAY operator lets you map and filter the elements or attributes of a collection, object, or objects. It evaluates to an array of the operand expression, that satisfies the WHEN clause, if provided.

Syntax

ARRAY expr 
ARRAY var1 FOR var1 ( IN | WITHIN ) expr1
   [ ,  var2 ( IN | WITHIN ) expr2 ]*
   [ ( WHEN cond1 [ AND cond2 ] ) ] END
Arguments
var1, var2, … , varX

A string or expression that evaluates to a string representing the variable name in the ARRAY loop.

expr1, expr2, … , exprX

A string or expression that evaluates to a string representing the array to loop through.

cond1, cond2, … , condX

An expression representing the limiting or matching clause to test against.

Return Values

The array or arrays that satisfy the ARRAY expression; otherwise, it returns an empty array.

ARRAY Example 1a: List only the arrays of Friday night flights from Albuquerque to Atlanta after 7pm.

SELECT ARRAY v FOR v IN schedule WHEN v.utc > "19:00" AND v.day = 5 END AS fri_evening_flights
FROM `travel-sample`
WHERE type="route" AND airline="KL" AND sourceairport="ABQ" AND destinationairport="ATL"
AND ANY v IN schedule SATISFIES v.utc > "19:00" END;

This results in:

[
  {
    "fri_evening_flights": [
      {
        "day": 5,
        "flight": "KL169",
        "utc": "23:41:00"
      }
    ]
  }
]

ARRAY Example 1b: The same results can be reached by writing Example 1a as follows:

SELECT ARRAY v FOR v IN schedule, w IN schedule WHEN v.utc > "19:00" AND w.day = 5 END AS fri_evening_flights
FROM `travel-sample`
WHERE type="route" AND airline="KL" AND sourceairport="ABQ" AND destinationairport="ATL"
AND ANY v IN schedule SATISFIES v.utc > "19:00" END;

EVERY

EVERY is a range predicate that tests a Boolean condition over the elements or attributes of a collection, object, or objects. It uses the IN and WITHIN operators to range through the collection.

Syntax ANY SOME EVERY expr

EVERY var1 ( IN | WITHIN ) expr1
   [ , var2 ( IN | WITHIN ) expr2 ]*
   SATISFIES condition  END
Arguments
var1, var2, … , varX

A string or expression that evaluates to a string representing the variable name in the EVERY loop.

expr1, expr2, … , exprX

A string or expression that evaluates to a string representing the array to loop through.

condition

An expression representing the limiting or matching clause to test against.

Return Values

If all the items in the array satisfies the EVERY expression, then it returns the entire array; otherwise, it returns an empty array.

EVERY Example 1a: Retrieve the array of airline flight schedules from Albuquerque (ABQ) to Atlanta (ATL) if all of the flights are after 00:35.

SELECT * FROM `travel-sample`
WHERE type="route" AND airline="KL" AND sourceairport="ABQ" AND destinationairport="ATL"
AND EVERY departure IN schedule SATISFIES departure.utc > "00:35" END;

Since the earliest flight departs at 00:36, this query results in the entire array.

Results:
[
  {
    "travel-sample": {
      "airline": "KL",
      "airlineid": "airline_3090",
      "destinationairport": "ATL",
      "distance": 2038.3535078909663,
      "equipment": "757 320",
      "id": 36159,
      "schedule": [
        {
          "day": 0,
          "flight": "KL938",
          "utc": "03:54:00"
        },
        {
          "day": 0,
          "flight": "KL270",
          "utc": "16:57:00"
        },
        {
          "day": 0,
          "flight": "KL222",
          "utc": "05:17:00"
        },
        {
          "day": 0,
          "flight": "KL198",
          "utc": "05:07:00"
        },
        {
          "day": 0,
          "flight": "KL858",
          "utc": "18:59:00"
        },
        {
          "day": 1,
          "flight": "KL672",
          "utc": "19:19:00"
        },
        {
          "day": 2,
          "flight": "KL832",
          "utc": "15:54:00"
        },
        {
          "day": 2,
          "flight": "KL173",
          "utc": "07:18:00"
        },
        {
          "day": 2,
          "flight": "KL520",
          "utc": "09:05:00"
        },
        {
          "day": 3,
          "flight": "KL259",
          "utc": "11:05:00"
        },
        {
          "day": 3,
          "flight": "KL065",
          "utc": "17:11:00"
        },
        {
          "day": 4,
          "flight": "KL469",
          "utc": "06:06:00"
        },
        {
          "day": 4,
          "flight": "KL509",
          "utc": "06:53:00"
        },
        {
          "day": 4,
          "flight": "KL096",
          "utc": "10:29:00"
        },
        {
          "day": 5,
          "flight": "KL347",
          "utc": "08:51:00"
        },
        {
          "day": 5,
          "flight": "KL281",
          "utc": "06:26:00"
        },
        {
          "day": 5,
          "flight": "KL567",
          "utc": "03:54:00"
        },
        {
          "day": 5,
          "flight": "KL169",
          "utc": "23:41:00"
        },
        {
          "day": 6,
          "flight": "KL884",
          "utc": "00:36:00"
        },
        {
          "day": 6,
          "flight": "KL886",
          "utc": "14:13:00"
        },
        {
          "day": 6,
          "flight": "KL636",
          "utc": "17:40:00"
        }
      ],
      "sourceairport": "ABQ",
      "stops": 0,
      "type": "route"
    }
  }
]

EVERY Example 1b: But if you change the SATISFIES clause to 1 minute after the first flight ("00:37"), then the resulting array is empty.

{
  "results": []
}

EXISTS

The EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one result.

It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

EXISTS expr 
search_expr EXISTS target_expr
Arguments
search_expr

A string or expression that evaluates to a string representing the expression or variable being searched for.

target_expr

A string or expression that evaluates to a string representing the array or collection being searched through.

Return Values

The EXISTS operator evaluates to TRUE if the value is an array and contains at least one element.

EXISTS Example 1: Of the 1641 cities with an airport, search for all cities that have a landmark as well.

SELECT DISTINCT city FROM `travel-sample` AS l WHERE type = "landmark"
AND EXISTS (SELECT city FROM `travel-sample` AS a WHERE type = "airport");

This results in the 626 cities that contain both an airport and a landmark.

[
  {
    "city": null
  },
  {
    "city": "Abbeville"
  },
  {
    "city": "Abbots Langley"
  },
  {
    "city": "Aberdeenshire"
  },
...

FIRST

The FIRST operator enables you to map and filter the elements or attributes of a collection, object, or objects. It evaluates to a single element based on the operand expression that satisfies the WHEN clause, if provided.

Syntax ARRAY FIRST OBJECT expr

FIRST var1 FOR var1 ( IN | WITHIN ) expr1
   [ , var2 ( IN | WITHIN ) expr2]*
   [ ( WHEN cond1 [ AND cond2 ] ) ] END
Arguments
var1, var2, … , varX

A string or expression that evaluates to a string representing the variable name in the ARRAY loop.

expr1, expr2, … , exprX

A string or expression that evaluates to a string representing the array to loop through.

cond1, cond2, … , condX

An expression representing the limiting or matching clause to test against.

Return Values

The first array that satisfies the WHEN expression; otherwise, it returns an empty array.

FIRST Example 1a: List the first array of flights from Albuquerque to Atlanta after 7pm.

SELECT FIRST v FOR v IN schedule WHEN v.utc > "19:00" END AS evening_flights
FROM `travel-sample`
WHERE type="route" AND airline="KL" AND sourceairport="ABQ" AND destinationairport="ATL"
AND ANY v IN schedule SATISFIES v.utc > "19:00" END;

This results in:

[
  {
    "evening_flights": [
      {
        "day": 1,
        "flight": "KL672",
        "utc": "19:19:00"
      }
    ]
  }
]

IN

The IN operator specifies the search depth to include only the current level of an array and not to include any child or descendant arrays.

Syntax

IN expr 
search_expr [ NOT ] IN target_expr
Arguments
search_expr

A string or expression that evaluates to a string representing the expression or variable being searched for.

target_expr

A string or expression that evaluates to a string representing the array or collection being searched through.

Return Values

The IN operator evaluates to TRUE if the right-side value is an array and directly contains the left-side value.

The NOT IN operator evaluates to TRUE if the right-side value is an array and does not directly contain the left-side value.

IN Example 1: Search for all airlines from the United Kingdom or France.

SELECT * FROM `travel-sample` AS t
WHERE type = "airline" AND country IN ["United Kingdom", "France"];

This results in 60 documents:

[
  {
    "t": {
      "callsign": "CORSAIR",
      "country": "France",
      "iata": "SS",
      "icao": "CRL",
      "id": 1908,
      "name": "Corsairfly",
      "type": "airline"
    }
  },
...

IN Example 2: Search for the author "Walton Wolf" in the hotel documents.

SELECT * FROM `travel-sample` AS t WHERE type = "hotel" AND "Walton Wolf" IN t;

This results in an empty set because authors are not in the current level (the root level) of the travel-sample documents.

{
  "results": []
}

The authors are listed inside the reviews array (a child element) and would need the WITHIN keyword to search all child elements along with the root level.

WITHIN

The WITHIN operator specifies the search depth to include the current level of an array and all of its child and descendant arrays.

Syntax

WITHIN expr 
search_expr [ NOT ] WITHIN target_expr
Arguments
search_expr

A string or expression that evaluates to a string representing the expression or variable being searched for.

target_expr

A string or expression that evaluates to a string representing the array or collection being searched through.

Return Values

The WITHIN operator evaluates to TRUE if the right-side value is an array and directly contains the left-side value as a child or descendant, that is, directly or indirectly.

The NOT WITHIN operator evaluates to TRUE if the right-side value is an array and no child or descendant contains the left-side value.

WITHIN Example 1: Search all elements for the author "Walton Wolf" in the hotel documents.

SELECT * FROM `travel-sample` AS t WHERE type = "hotel" AND "Walton Wolf" WITHIN t;

This results in 1 document since his name appears inside the reviews array.

[
  {
    "t": {
      "address": "Gilsland, CA8 7DA",
      "alias": null,
      "checkin": null,
      "checkout": null,
      "city": null,
      "country": "United Kingdom",
      "description": "Tantallon House offers accommodation around 10 minutes walk from the National Trail. It also has a holiday cottage.",
      "directions": null,
      "email": null,
      "fax": null,
      "free_breakfast": true,
      "free_internet": true,
      "free_parking": false,
      "geo": {
        "accuracy": "ROOFTOP",
        "lat": 54.99304,
        "lon": -2.58142
      },
      "id": 10851,
      "name": "Tantallon House B&B",
      "pets_ok": true,
      "phone": null,
      "price": "From £44 (no cards)",
      "public_likes": [
        "Victor Russel"
      ],
      "reviews": [
        {
          "author": "Walton Wolf",
          "content": "Myself and a mate stayed here the last week of April 2010. On arrival were were told that the travel agent booked us a king bed and not a double room as we requested when booking and made sure it was a double, as 2 blokes. the guy on reception was not moving on it, he said there will be a double room available on the tuesday, this was sunday when we came. he also advised us that it will be an extra 20 dollars per night in the double room. in fairness we were not impressed with his attitude and inflexibility. nothing we could do so we jst grinned and beared it. then to make things worse, our room was in the basement, beside the breakfeast room and the ice machines and vending machines. Pure pissed off afer paying 500euro each for this $@!#%. The room was serioulsy small, no room for our suitcases, a major squash round the king bed. pure noisy the following morning frm the machines outside our room, this should not be a room it should be a storage room, so not impressed. After one night in that room we asked the girl on reception if ther is any chance we could change room to a double, she checked and said yes, one is available and they are jst cleaning it and after that we can change room. Obviously the guy on reception the day b4 jst cudnt be bothered informing us of this. After we chaged rooms it was fine, room was ok for ny, not alot of space to move about, have stayed in better ones in ny. had no problem after we changed rooms, the girl on reception was very nice and helpful, she let me do check my online banking on her pc and let us use the phone when we neede to, at no cost, she couldnt be more nice. Love ny, done all the usual haunts and sites, grand location, great italian restaurant a few mins walk away, great food, and u get a discount when u mention u are staying in the gem hotel, the girl on reception gave us a card for the restaruant. What more can i say, the hotel was fine after the inital ignorance, our stay was more pleasant chatting away to the nice girl on reception, whos name i cant remember lol, long culry dark hair ha ha.",
          "date": "2014-06-07 03:54:50 +0300",
          "ratings": {
            "Cleanliness": 4,
            "Location": 4,
            "Overall": 4,
            "Rooms": 2,
            "Service": 3,
            "Sleep Quality": 4,
            "Value": 3
          }
        },
        {
          "author": "Catharine Funk",
          "content": "My first trip to new york city was a disaster because of this hotel. My wedding is only 2 months away and my fiance and I have bed bug bites all over our body. (30+ bites each) I'm hoping the 6 welts on my face won't scar before our wedding day. The guy that was working there that night helped us find a different hotel. He was really kind. But who wants to stay at a staff friendly bed bug infested hotel? I should have known better to not book a room that's $114 a night. Do yourself and your family a favor and do NOT go to this hotel no matter what cheap rate they offer.",
          "date": "2012-01-02 21:29:27 +0300",
          "ratings": {
            "Cleanliness": 1,
            "Location": 2,
            "Overall": 1,
            "Rooms": 1,
            "Service": 5,
            "Sleep Quality": 1,
            "Value": 3
          }
        },
        {
          "author": "Tavares Denesik",
          "content": "This was my second time to stay at this hotel. Although my stay was for only two days, I felt very comfortable there. More like an exclusive appartment than a hotel. The flat was small but extreamly adequate. I enjoyed my stay whilst saving BIG money. My next business trip to NYC I will definately stay there.",
          "date": "2014-03-27 18:11:46 +0300",
          "ratings": {
            "Cleanliness": 5,
            "Location": 5,
            "Overall": 5,
            "Rooms": 4,
            "Service": 5,
            "Value": 5
          }
        },
        {
          "author": "Lucy Graham",
          "content": "SUMMARY - Although the GEM Hotel is associated with the Choice Hotels chain (Quality, Comfort, Econo, etc.) this is the first time I've heard of this particular branding. They are billed as a \"Botique Hotel\" and certainly fit the bill as much as a chain can be considered a \"Botique\". The place is very bare bones and minimal, yet very stylish and modern. They just give you the very basics of what you need and not much else, but the price reflects that. So it's hard to complain. There are 10 floors and approximately 4 small rooms/floor. It's not the kind of place I'd spend a whole week because it's rather small, has limited storage, and very few of the \"extras\" you'd need for an extended stay. But for staying a night or two in NYC when you just need a place to sleep and shower it does just fine. I forgot to take photos this time, but as of today the photos the hotel has provided on various travel websites are fairly accurate. You just have to take a close look at them to see how small the space really is and understand that the photos really show the WHOLE room. There's not much cropped out! BATHROOM: Clean, basic, and modern. It was pretty much a standard hotel sized bathroom, even though the main room was small. BEDS: Two double beds. Very comfortable, leaning towards the soft side of hotel beds. Sheets were clean and more modern/stylish than you'd find in most chain hotels. BILLING: Reserved via choicehotels.com only a few days ahead of time. The front desk had no problems getting us checked in, and the taxes/surcharges only added up to another 16% of the cost of the room itself. BREAKFAST: There is free coffee/tea in the room, but i never looked for a continental breakfast. There were vending machines in the basement with the ice/water machine. CLEANING: The room was as clean as it gets. While the building is obviously old, it seems that the entire interior is brand new. CLIMATE CONTROL: There was a brand new heater/air conditioning unit that was very quiet and easily adjustable. ELEVATORS: One main, new looking elevator in the lobby. It worked just fine and was fairly large. FRONT DESK: The front desk was helpful and competent. We had to wait about 15-20 minutes for a room to be cleaned. They may have had the smallest, most basic lobby i've ever seen in my life. But if you're looking for water fountains and gold trim you're not going to get it in a hotel with sub-$200 rates! GYM: None. HOT TUB: None. INTERNET: Didn't try it. I was too busy enjoying the city. LOCATION: On the west-side on 36th very close to the corner of 10th. It's a much more sparsely populated and quiet area than most of Manhattan, as it borders on more of an industrial zone. However, it's only a few blocks to Times Square, Penn Station, and Madison Square Garden. It's a block from the entrace to the Lincoln Tunnel. It was nice to be so close to everything, yet just off the \"beaten path\" in a calmer area. There's a gas station on the corner (one of the few in Manhattan) and a few other random bodegas and restaurants nearby. NOISE: It's NYC, there's always going to be some noise, but there was nothing excessive. Lots of trucks roll down 10th avenue at night, but they mostly just blended into the background. PARKING: None. It's best to find a park & ride outside the city and take a train in. Penn Station is just 2 short blocks and then 2 long blocks away. POOL: None. RECREATION: None. RESTAURANT: None, but it's New York. I'm sure you can find something to eat. For the less adventurous, I did notice a McDonald's on 10th, just a short walk from the hotel. ROOM: The room was small and there wasn't much room around the beds. I'll admit, for 3 people, it was a bit cramped. If you like your space I'd limit occupancy to 2 or find another, bigger, hotel. There was limited storage, just one small wardrobe with 2 drawers underneath, a small nightstand, alarm clock/ipod player, desk, desk chair, and flat screen TV. Again, the decor was very modern. SHUTTLE: None. STORE: None on site, a gas station on the corner, several bodegas nearby. SUPPLIES: Standard Soap, Shampoo, Conditioner, Mouthwash & Towels. ===== ===== ===== RATING NOTE: My ratings are generally based on value vs money. Thus, sometimes a simple hotel will receive a 4 or 5, while a fancy hotel can rank a 1 or 2 if I don't feel it was worth the cost. Also, a hotel can do everything right and still only rank as a 4, unless there's something really exceptional about it. 5 = Hotel gets everything right, and goes above and beyond. 4 = Hotel gets everything right. 3 = Hotel is acceptable value for money spent. 2 = Hotel isn't worth the money spent. 1 = Hotel should pay ME to stay there.",
          "date": "2014-09-30 09:41:30 +0300",
          "ratings": {
            "Check in / front desk": 3,
            "Cleanliness": 4,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 3,
            "Value": 5
          }
        }
      ],
      "state": null,
      "title": "Hadrian's Wall",
      "tollfree": null,
      "type": "hotel",
      "url": "http://www.tantallonhouse.co.uk/",
      "vacancy": false
    }
  }
]