Example 5: Sort the data using a field not part of the index

Example 10-5 Fetch the id, income, and age of users belonging to the state CA and whose city of residence is Santaclara and have income between 1000 and 10000. Sort the results by age

SELECT id, income,age FROM Users u WHERE u.address.state = "CA"
AND u.address.city ="Santaclara" AND 1000 < income AND
income < 10000 ORDER BY age

Query execution plan:

{
  "iterator kind" : "SORT",
  "order by fields at positions" : [ 2 ],
  "input iterator" :
  {
    "iterator kind" : "RECEIVE",
    "distribution kind" : "ALL_SHARDS",
    "input iterator" :
    {
      "iterator kind" : "SELECT",
      "FROM" :
      {
        "iterator kind" : "TABLE",
        "target table" : "users",
        "row variable" : "$$u",
        "index used" : "idx_state_city_income",
        "covering index" : false,
        "index scans" : [
          {
            "equality conditions" : {"address.state":"CA","address.city":"Santaclara"},
            "range conditions" : { "income" : { "start value" : 1000, "start inclusive" : false, "end value" : 10000, "end inclusive" : false } }
          }
        ]
      },
      "FROM variable" : "$$u",
      "SELECT expressions" : [
        {
          "field name" : "id",
          "field expression" :
          {
            "iterator kind" : "FIELD_STEP",
            "field name" : "id",
            "input iterator" :
           {
              "iterator kind" : "VAR_REF",
              "variable" : "$$u"
            }
          }
        },
        {
          "field name" : "income",
          "field expression" :
          {
            "iterator kind" : "FIELD_STEP",
            "field name" : "income",
            "input iterator" :
            {
              "iterator kind" : "VAR_REF",
              "variable" : "$$u"
            }
          }
        },
        {
          "field name" : "age",
          "field expression" :
          {
            "iterator kind" : "FIELD_STEP",
            "field name" : "age",
            "input iterator" :
            {
              "iterator kind" : "VAR_REF",
              "variable" : "$$u"
            }
          }
        }
      ]
    }
  }
}

Explanation of the query execution plan :

In this example, you perform a sort in addition to filtering the data. The results need to be sorted by age. The age is not part of the idx_state_city_income index. So in this example, you need a separate SORT operator.
The sorting is done by a SORT iterator, which is the parent of the RECEIVE iterator. The order by fields at positions property specifies the field used for sorting. The value of this array depends on the position of the field that is sorted in the SELECT expression. In this example, age is the third field in the SELECT expression. So order by fields at positions has a value of 2.
```
"order by fields at positions" : [ 2 ]
```
The index idx_state_city_income is used here and in this example, it is not a covering index as the query has the age field that is not part of the entries of the index.
The index scan property contains the start and stop conditions that define the index scans to be performed.
```
"index scans" : [
   {
      "equality conditions" : {"address.state":"CA","address.city":"Santaclara"},
      "range conditions" : { "income" : { "start value" : 1000, "start inclusive" : false, 
                                          "end value" : 10000, "end inclusive" : false } }
   }
]
```
In this example, only one index scan will be performed. The conditions correspond to the predicates u.address.state = "CA" and u.address.city = "Santaclara" from the query. Specifically, the starting index entry must have the value CA on the address.state field. All subsequent entries must have CA as the value of their address.state field, and the scan will stop as soon as an entry with a different state value is encountered. There is a range condition to be applied here on the income field.
When the FROM iterator is a TABLE iterator, the FROM variable is the same as either the index row variable or the row variable of the TABLE iterator, depending on whether the used index is covering or not. In this example, the FROM variable is same as the row variable as the index is not covering.
This row variable ($$u) will be referenced by iterators implementing the other clauses of the SELECT expression.
In the SELECT expression, three fields (id, income and age) are fetched. These correspond to three field names and field expressions in the SELECT expression clause.
```
{
   "field name" : "id",
   "field expression" :
   {
      "iterator kind" : "FIELD_STEP",
      "field name" : "#id",
      "input iterator" :
      {
         "iterator kind" : "VAR_REF",
         "variable" : "$$u"
      }
   }
}
```
For every field to be fetched by the SELECT expression, the field expression is computed by a FIELD_STEP iterator. The FIELD_STEP iterator extracts and returns the value of a field (id as shown above) from the records produced by its input iterator. The same is done for every field to be fetched in the SELECT expression.