HTTP Interface for AQL Queries

Explaining and parsing queries

ArangoDB has an HTTP interface to syntactically validate AQL queries. Furthermore, it offers an HTTP interface to retrieve the execution plan for any valid AQL query.

Both functionalities do not actually execute the supplied AQL query, but only inspect it and return meta information about it.

Explain an AQL query

explain an AQL query and return information about it

POST /_api/explain A JSON object describing the query and query parameters.

A JSON object with these properties is required:

  • query: the query which you want explained; If the query references any bind variables, these must also be passed in the attribute bindVars. Additional options for the query can be passed in the options attribute.
  • options:
    • optimizer.rules (string): an array of to-be-included or to-be-excluded optimizer rules can be put into this attribute, telling the optimizer to include or exclude specific rules. To disable a rule, prefix its name with a -, to enable a rule, prefix it with a +. There is also a pseudo-rule all, which will match all optimizer rules.
    • maxNumberOfPlans: an optional maximum number of plans that the optimizer is allowed to generate. Setting this attribute to a low value allows to put a cap on the amount of work the optimizer does.
    • allPlans: if set to true, all possible execution plans will be returned. The default is false, meaning only the optimal plan will be returned.
  • bindVars (object): key/value pairs representing the bind parameters.

To explain how an AQL query would be executed on the server, the query string can be sent to the server via an HTTP POST request. The server will then validate the query and create an execution plan for it. The execution plan will be returned, but the query will not be executed.

The execution plan that is returned by the server can be used to estimate the probable performance of the query. Though the actual performance will depend on many different factors, the execution plan normally can provide some rough estimates on the amount of work the server needs to do in order to actually run the query.

By default, the explain operation will return the optimal plan as chosen by the query optimizer The optimal plan is the plan with the lowest total estimated cost. The plan will be returned in the attribute plan of the response object. If the option allPlans is specified in the request, the result will contain all plans created by the optimizer. The plans will then be returned in the attribute plans.

The result will also contain an attribute warnings, which is an array of warnings that occurred during optimization or execution plan creation. Additionally, a stats attribute is contained in the result with some optimizer statistics. If allPlans is set to false, the result will contain an attribute cacheable that states whether the query results can be cached on the server if the query result cache were used. The cacheable attribute is not present when allPlans is set to true.

Each plan in the result is a JSON object with the following attributes:

  • nodes: the array of execution nodes of the plan. The array of available node types can be found here

  • estimatedCost: the total estimated cost for the plan. If there are multiple plans, the optimizer will choose the plan with the lowest total cost.

  • collections: an array of collections used in the query

  • rules: an array of rules the optimizer applied. An overview of the available rules can be found here

  • variables: array of variables used in the query (note: this may contain internal variables created by the optimizer)

Example:

Valid query

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products RETURN p" 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "plan" : { 
    "nodes" : [ 
      { 
        "type" : "SingletonNode", 
        "dependencies" : [ ], 
        "id" : 1, 
        "estimatedCost" : 1, 
        "estimatedNrItems" : 1 
      }, 
      { 
        "type" : "EnumerateCollectionNode", 
        "dependencies" : [ 
          1 
        ], 
        "id" : 2, 
        "estimatedCost" : 12, 
        "estimatedNrItems" : 10, 
        "database" : "_system", 
        "collection" : "products", 
        "random" : false, 
        "satellite" : false, 
        "outVariable" : { 
          "id" : 0, 
          "name" : "p" 
        } 
      }, 
      { 
        "type" : "ReturnNode", 
        "dependencies" : [ 
          2 
        ], 
        "id" : 3, 
        "estimatedCost" : 22, 
        "estimatedNrItems" : 10, 
        "inVariable" : { 
          "id" : 0, 
          "name" : "p" 
        } 
      } 
    ], 
    "rules" : [ ], 
    "collections" : [ 
      { 
        "name" : "products", 
        "type" : "read" 
      } 
    ], 
    "variables" : [ 
      { 
        "id" : 0, 
        "name" : "p" 
      } 
    ], 
    "estimatedCost" : 22, 
    "estimatedNrItems" : 10, 
    "initialize" : true 
  }, 
  "warnings" : [ ], 
  "stats" : { 
    "rulesExecuted" : 29, 
    "rulesSkipped" : 0, 
    "plansCreated" : 1 
  }, 
  "cacheable" : true, 
  "error" : false, 
  "code" : 200 
}

Example:

A plan with some optimizer rules applied

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products LET a = p.id FILTER a == 4 LET name = p.name SORT p.id LIMIT 1 RETURN name" 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "plan" : { 
    "nodes" : [ 
      { 
        "type" : "SingletonNode", 
        "dependencies" : [ ], 
        "id" : 1, 
        "estimatedCost" : 1, 
        "estimatedNrItems" : 1 
      }, 
      { 
        "type" : "IndexNode", 
        "dependencies" : [ 
          1 
        ], 
        "id" : 11, 
        "estimatedCost" : 1.5, 
        "estimatedNrItems" : 1, 
        "database" : "_system", 
        "collection" : "products", 
        "satellite" : false, 
        "outVariable" : { 
          "id" : 0, 
          "name" : "p" 
        }, 
        "indexes" : [ 
          { 
            "id" : "10114", 
            "type" : "skiplist", 
            "fields" : [ 
              "id" 
            ], 
            "unique" : false, 
            "sparse" : false, 
            "deduplicate" : true 
          } 
        ], 
        "condition" : { 
          "type" : "n-ary or", 
          "subNodes" : [ 
            { 
              "type" : "n-ary and", 
              "subNodes" : [ 
                { 
                  "type" : "compare ==", 
                  "subNodes" : [ 
                    { 
                      "type" : "attribute access", 
                      "name" : "id", 
                      "subNodes" : [ 
                        { 
                          "type" : "reference", 
                          "name" : "p", 
                          "id" : 0 
                        } 
                      ] 
                    }, 
                    { 
                      "type" : "value", 
                      "value" : 4 
                    } 
                  ] 
                } 
              ] 
            } 
          ] 
        }, 
        "reverse" : false 
      }, 
      { 
        "type" : "CalculationNode", 
        "dependencies" : [ 
          11 
        ], 
        "id" : 4, 
        "estimatedCost" : 2.5, 
        "estimatedNrItems" : 1, 
        "expression" : { 
          "type" : "compare ==", 
          "subNodes" : [ 
            { 
              "type" : "attribute access", 
              "name" : "id", 
              "subNodes" : [ 
                { 
                  "type" : "reference", 
                  "name" : "p", 
                  "id" : 0 
                } 
              ] 
            }, 
            { 
              "type" : "value", 
              "value" : 4 
            } 
          ] 
        }, 
        "outVariable" : { 
          "id" : 4, 
          "name" : "3" 
        }, 
        "canThrow" : false, 
        "expressionType" : "simple" 
      }, 
      { 
        "type" : "FilterNode", 
        "dependencies" : [ 
          4 
        ], 
        "id" : 5, 
        "estimatedCost" : 3.5, 
        "estimatedNrItems" : 1, 
        "inVariable" : { 
          "id" : 4, 
          "name" : "3" 
        } 
      }, 
      { 
        "type" : "LimitNode", 
        "dependencies" : [ 
          5 
        ], 
        "id" : 9, 
        "estimatedCost" : 4.5, 
        "estimatedNrItems" : 1, 
        "offset" : 0, 
        "limit" : 1, 
        "fullCount" : false 
      }, 
      { 
        "type" : "CalculationNode", 
        "dependencies" : [ 
          9 
        ], 
        "id" : 6, 
        "estimatedCost" : 5.5, 
        "estimatedNrItems" : 1, 
        "expression" : { 
          "type" : "attribute access", 
          "name" : "name", 
          "subNodes" : [ 
            { 
              "type" : "reference", 
              "name" : "p", 
              "id" : 0 
            } 
          ] 
        }, 
        "outVariable" : { 
          "id" : 2, 
          "name" : "name" 
        }, 
        "canThrow" : false, 
        "expressionType" : "attribute" 
      }, 
      { 
        "type" : "ReturnNode", 
        "dependencies" : [ 
          6 
        ], 
        "id" : 10, 
        "estimatedCost" : 6.5, 
        "estimatedNrItems" : 1, 
        "inVariable" : { 
          "id" : 2, 
          "name" : "name" 
        } 
      } 
    ], 
    "rules" : [ 
      "move-calculations-up", 
      "remove-redundant-calculations", 
      "remove-unnecessary-calculations", 
      "move-calculations-up-2", 
      "use-indexes", 
      "use-index-for-sort", 
      "remove-unnecessary-calculations-2", 
      "move-calculations-down" 
    ], 
    "collections" : [ 
      { 
        "name" : "products", 
        "type" : "read" 
      } 
    ], 
    "variables" : [ 
      { 
        "id" : 6, 
        "name" : "5" 
      }, 
      { 
        "id" : 4, 
        "name" : "3" 
      }, 
      { 
        "id" : 2, 
        "name" : "name" 
      }, 
      { 
        "id" : 1, 
        "name" : "a" 
      }, 
      { 
        "id" : 0, 
        "name" : "p" 
      } 
    ], 
    "estimatedCost" : 6.5, 
    "estimatedNrItems" : 1, 
    "initialize" : true 
  }, 
  "warnings" : [ ], 
  "stats" : { 
    "rulesExecuted" : 29, 
    "rulesSkipped" : 0, 
    "plansCreated" : 1 
  }, 
  "cacheable" : true, 
  "error" : false, 
  "code" : 200 
}

Example:

Using some options

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products LET a = p.id FILTER a == 4 LET name = p.name SORT p.id LIMIT 1 RETURN name", 
  "options" : { 
    "maxNumberOfPlans" : 2, 
    "allPlans" : true, 
    "optimizer" : { 
      "rules" : [ 
        "-all", 
        "+use-index-for-sort", 
        "+use-index-range" 
      ] 
    } 
  } 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "plans" : [ 
    { 
      "nodes" : [ 
        { 
          "type" : "SingletonNode", 
          "dependencies" : [ ], 
          "id" : 1, 
          "estimatedCost" : 1, 
          "estimatedNrItems" : 1 
        }, 
        { 
          "type" : "IndexNode", 
          "dependencies" : [ 
            1 
          ], 
          "id" : 11, 
          "estimatedCost" : 11, 
          "estimatedNrItems" : 10, 
          "database" : "_system", 
          "collection" : "products", 
          "satellite" : false, 
          "outVariable" : { 
            "id" : 0, 
            "name" : "p" 
          }, 
          "indexes" : [ 
            { 
              "id" : "10152", 
              "type" : "skiplist", 
              "fields" : [ 
                "id" 
              ], 
              "unique" : false, 
              "sparse" : false, 
              "deduplicate" : true 
            } 
          ], 
          "condition" : { 
          }, 
          "reverse" : false 
        }, 
        { 
          "type" : "CalculationNode", 
          "dependencies" : [ 
            11 
          ], 
          "id" : 3, 
          "estimatedCost" : 21, 
          "estimatedNrItems" : 10, 
          "expression" : { 
            "type" : "attribute access", 
            "name" : "id", 
            "subNodes" : [ 
              { 
                "type" : "reference", 
                "name" : "p", 
                "id" : 0 
              } 
            ] 
          }, 
          "outVariable" : { 
            "id" : 1, 
            "name" : "a" 
          }, 
          "canThrow" : false, 
          "expressionType" : "attribute" 
        }, 
        { 
          "type" : "CalculationNode", 
          "dependencies" : [ 
            3 
          ], 
          "id" : 4, 
          "estimatedCost" : 31, 
          "estimatedNrItems" : 10, 
          "expression" : { 
            "type" : "compare ==", 
            "subNodes" : [ 
              { 
                "type" : "reference", 
                "name" : "a", 
                "id" : 1 
              }, 
              { 
                "type" : "value", 
                "value" : 4 
              } 
            ] 
          }, 
          "outVariable" : { 
            "id" : 4, 
            "name" : "3" 
          }, 
          "canThrow" : false, 
          "expressionType" : "simple" 
        }, 
        { 
          "type" : "FilterNode", 
          "dependencies" : [ 
            4 
          ], 
          "id" : 5, 
          "estimatedCost" : 41, 
          "estimatedNrItems" : 10, 
          "inVariable" : { 
            "id" : 4, 
            "name" : "3" 
          } 
        }, 
        { 
          "type" : "CalculationNode", 
          "dependencies" : [ 
            5 
          ], 
          "id" : 6, 
          "estimatedCost" : 51, 
          "estimatedNrItems" : 10, 
          "expression" : { 
            "type" : "attribute access", 
            "name" : "name", 
            "subNodes" : [ 
              { 
                "type" : "reference", 
                "name" : "p", 
                "id" : 0 
              } 
            ] 
          }, 
          "outVariable" : { 
            "id" : 2, 
            "name" : "name" 
          }, 
          "canThrow" : false, 
          "expressionType" : "attribute" 
        }, 
        { 
          "type" : "CalculationNode", 
          "dependencies" : [ 
            6 
          ], 
          "id" : 7, 
          "estimatedCost" : 61, 
          "estimatedNrItems" : 10, 
          "expression" : { 
            "type" : "attribute access", 
            "name" : "id", 
            "subNodes" : [ 
              { 
                "type" : "reference", 
                "name" : "p", 
                "id" : 0 
              } 
            ] 
          }, 
          "outVariable" : { 
            "id" : 6, 
            "name" : "5" 
          }, 
          "canThrow" : false, 
          "expressionType" : "attribute" 
        }, 
        { 
          "type" : "LimitNode", 
          "dependencies" : [ 
            7 
          ], 
          "id" : 9, 
          "estimatedCost" : 62, 
          "estimatedNrItems" : 1, 
          "offset" : 0, 
          "limit" : 1, 
          "fullCount" : false 
        }, 
        { 
          "type" : "ReturnNode", 
          "dependencies" : [ 
            9 
          ], 
          "id" : 10, 
          "estimatedCost" : 63, 
          "estimatedNrItems" : 1, 
          "inVariable" : { 
            "id" : 2, 
            "name" : "name" 
          } 
        } 
      ], 
      "rules" : [ 
        "use-index-for-sort" 
      ], 
      "collections" : [ 
        { 
          "name" : "products", 
          "type" : "read" 
        } 
      ], 
      "variables" : [ 
        { 
          "id" : 6, 
          "name" : "5" 
        }, 
        { 
          "id" : 4, 
          "name" : "3" 
        }, 
        { 
          "id" : 2, 
          "name" : "name" 
        }, 
        { 
          "id" : 1, 
          "name" : "a" 
        }, 
        { 
          "id" : 0, 
          "name" : "p" 
        } 
      ], 
      "estimatedCost" : 63, 
      "estimatedNrItems" : 1, 
      "initialize" : true 
    } 
  ], 
  "warnings" : [ ], 
  "stats" : { 
    "rulesExecuted" : 1, 
    "rulesSkipped" : 28, 
    "plansCreated" : 1 
  }, 
  "error" : false, 
  "code" : 200 
}

Example:

Returning all plans

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products FILTER p.id == 25 RETURN p", 
  "options" : { 
    "allPlans" : true 
  } 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "plans" : [ 
    { 
      "nodes" : [ 
        { 
          "type" : "SingletonNode", 
          "dependencies" : [ ], 
          "id" : 1, 
          "estimatedCost" : 1, 
          "estimatedNrItems" : 1 
        }, 
        { 
          "type" : "IndexNode", 
          "dependencies" : [ 
            1 
          ], 
          "id" : 6, 
          "estimatedCost" : 1.99, 
          "estimatedNrItems" : 1, 
          "database" : "_system", 
          "collection" : "products", 
          "satellite" : false, 
          "outVariable" : { 
            "id" : 0, 
            "name" : "p" 
          }, 
          "indexes" : [ 
            { 
              "id" : "10100", 
              "type" : "hash", 
              "fields" : [ 
                "id" 
              ], 
              "selectivityEstimate" : 1, 
              "unique" : false, 
              "sparse" : false, 
              "deduplicate" : true 
            } 
          ], 
          "condition" : { 
            "type" : "n-ary or", 
            "subNodes" : [ 
              { 
                "type" : "n-ary and", 
                "subNodes" : [ 
                  { 
                    "type" : "compare ==", 
                    "subNodes" : [ 
                      { 
                        "type" : "attribute access", 
                        "name" : "id", 
                        "subNodes" : [ 
                          { 
                            "type" : "reference", 
                            "name" : "p", 
                            "id" : 0 
                          } 
                        ] 
                      }, 
                      { 
                        "type" : "value", 
                        "value" : 25 
                      } 
                    ] 
                  } 
                ] 
              } 
            ] 
          }, 
          "reverse" : false 
        }, 
        { 
          "type" : "ReturnNode", 
          "dependencies" : [ 
            6 
          ], 
          "id" : 5, 
          "estimatedCost" : 2.99, 
          "estimatedNrItems" : 1, 
          "inVariable" : { 
            "id" : 0, 
            "name" : "p" 
          } 
        } 
      ], 
      "rules" : [ 
        "use-indexes", 
        "remove-filter-covered-by-index", 
        "remove-unnecessary-calculations-2" 
      ], 
      "collections" : [ 
        { 
          "name" : "products", 
          "type" : "read" 
        } 
      ], 
      "variables" : [ 
        { 
          "id" : 2, 
          "name" : "1" 
        }, 
        { 
          "id" : 0, 
          "name" : "p" 
        } 
      ], 
      "estimatedCost" : 2.99, 
      "estimatedNrItems" : 1, 
      "initialize" : true 
    } 
  ], 
  "warnings" : [ ], 
  "stats" : { 
    "rulesExecuted" : 29, 
    "rulesSkipped" : 0, 
    "plansCreated" : 1 
  }, 
  "error" : false, 
  "code" : 200 
}

Example:

A query that produces a warning

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR i IN 1..10 RETURN 1 / 0" 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "plan" : { 
    "nodes" : [ 
      { 
        "type" : "SingletonNode", 
        "dependencies" : [ ], 
        "id" : 1, 
        "estimatedCost" : 1, 
        "estimatedNrItems" : 1 
      }, 
      { 
        "type" : "CalculationNode", 
        "dependencies" : [ 
          1 
        ], 
        "id" : 2, 
        "estimatedCost" : 2, 
        "estimatedNrItems" : 1, 
        "expression" : { 
          "type" : "range", 
          "subNodes" : [ 
            { 
              "type" : "value", 
              "value" : 1 
            }, 
            { 
              "type" : "value", 
              "value" : 10 
            } 
          ] 
        }, 
        "outVariable" : { 
          "id" : 2, 
          "name" : "1" 
        }, 
        "canThrow" : false, 
        "expressionType" : "simple" 
      }, 
      { 
        "type" : "CalculationNode", 
        "dependencies" : [ 
          2 
        ], 
        "id" : 4, 
        "estimatedCost" : 3, 
        "estimatedNrItems" : 1, 
        "expression" : { 
          "type" : "value", 
          "value" : null 
        }, 
        "outVariable" : { 
          "id" : 4, 
          "name" : "3" 
        }, 
        "canThrow" : false, 
        "expressionType" : "json" 
      }, 
      { 
        "type" : "EnumerateListNode", 
        "dependencies" : [ 
          4 
        ], 
        "id" : 3, 
        "estimatedCost" : 13, 
        "estimatedNrItems" : 10, 
        "inVariable" : { 
          "id" : 2, 
          "name" : "1" 
        }, 
        "outVariable" : { 
          "id" : 0, 
          "name" : "i" 
        } 
      }, 
      { 
        "type" : "ReturnNode", 
        "dependencies" : [ 
          3 
        ], 
        "id" : 5, 
        "estimatedCost" : 23, 
        "estimatedNrItems" : 10, 
        "inVariable" : { 
          "id" : 4, 
          "name" : "3" 
        } 
      } 
    ], 
    "rules" : [ 
      "move-calculations-up", 
      "move-calculations-up-2" 
    ], 
    "collections" : [ ], 
    "variables" : [ 
      { 
        "id" : 4, 
        "name" : "3" 
      }, 
      { 
        "id" : 2, 
        "name" : "1" 
      }, 
      { 
        "id" : 0, 
        "name" : "i" 
      } 
    ], 
    "estimatedCost" : 23, 
    "estimatedNrItems" : 10, 
    "initialize" : true 
  }, 
  "warnings" : [ 
    { 
      "code" : 1562, 
      "message" : "division by zero" 
    } 
  ], 
  "stats" : { 
    "rulesExecuted" : 29, 
    "rulesSkipped" : 0, 
    "plansCreated" : 1 
  }, 
  "cacheable" : false, 
  "error" : false, 
  "code" : 200 
}

Example:

Invalid query (missing bind parameter)

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products FILTER p.id == @id LIMIT 2 RETURN p.n" 
}
EOF

HTTP/1.1 400 Bad Request
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "error" : true, 
  "code" : 400, 
  "errorNum" : 1551, 
  "errorMessage" : "no value specified for declared bind parameter 'id' (while parsing)" 
}

Example:

The data returned in the plan attribute of the result contains one element per AQL top-level statement (i.e. FOR, RETURN, FILTER etc.). If the query optimizer removed some unnecessary statements, the result might also contain less elements than there were top-level statements in the AQL query.

The following example shows a query with a non-sensible filter condition that the optimizer has removed so that there are less top-level statements.

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ "query" : "FOR i IN [ 1, 2, 3 ] FILTER 1 == 2 RETURN i" }
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "plan" : { 
    "nodes" : [ 
      { 
        "type" : "SingletonNode", 
        "dependencies" : [ ], 
        "id" : 1, 
        "estimatedCost" : 1, 
        "estimatedNrItems" : 1 
      }, 
      { 
        "type" : "CalculationNode", 
        "dependencies" : [ 
          1 
        ], 
        "id" : 2, 
        "estimatedCost" : 2, 
        "estimatedNrItems" : 1, 
        "expression" : { 
          "type" : "array", 
          "subNodes" : [ 
            { 
              "type" : "value", 
              "value" : 1 
            }, 
            { 
              "type" : "value", 
              "value" : 2 
            }, 
            { 
              "type" : "value", 
              "value" : 3 
            } 
          ] 
        }, 
        "outVariable" : { 
          "id" : 2, 
          "name" : "1" 
        }, 
        "canThrow" : false, 
        "expressionType" : "json" 
      }, 
      { 
        "type" : "NoResultsNode", 
        "dependencies" : [ 
          2 
        ], 
        "id" : 7, 
        "estimatedCost" : 0.5, 
        "estimatedNrItems" : 0 
      }, 
      { 
        "type" : "EnumerateListNode", 
        "dependencies" : [ 
          7 
        ], 
        "id" : 3, 
        "estimatedCost" : 0.5, 
        "estimatedNrItems" : 0, 
        "inVariable" : { 
          "id" : 2, 
          "name" : "1" 
        }, 
        "outVariable" : { 
          "id" : 0, 
          "name" : "i" 
        } 
      }, 
      { 
        "type" : "ReturnNode", 
        "dependencies" : [ 
          3 
        ], 
        "id" : 6, 
        "estimatedCost" : 0.5, 
        "estimatedNrItems" : 0, 
        "inVariable" : { 
          "id" : 0, 
          "name" : "i" 
        } 
      } 
    ], 
    "rules" : [ 
      "move-calculations-up", 
      "move-filters-up", 
      "remove-unnecessary-filters", 
      "remove-unnecessary-calculations" 
    ], 
    "collections" : [ ], 
    "variables" : [ 
      { 
        "id" : 4, 
        "name" : "3" 
      }, 
      { 
        "id" : 2, 
        "name" : "1" 
      }, 
      { 
        "id" : 0, 
        "name" : "i" 
      } 
    ], 
    "estimatedCost" : 0.5, 
    "estimatedNrItems" : 0, 
    "initialize" : true 
  }, 
  "warnings" : [ ], 
  "stats" : { 
    "rulesExecuted" : 29, 
    "rulesSkipped" : 0, 
    "plansCreated" : 1 
  }, 
  "cacheable" : true, 
  "error" : false, 
  "code" : 200 
}

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products RETURN p" 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

A plan with some optimizer rules applied

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products LET a = p.id FILTER a == 4 LET name = p.name SORT p.id LIMIT 1 RETURN name" 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

Using some options

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products LET a = p.id FILTER a == 4 LET name = p.name SORT p.id LIMIT 1 RETURN name", 
  "options" : { 
    "maxNumberOfPlans" : 2, 
    "allPlans" : true, 
    "optimizer" : { 
      "rules" : [ 
        "-all", 
        "+use-index-for-sort", 
        "+use-index-range" 
      ] 
    } 
  } 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

Returning all plans

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products FILTER p.id == 25 RETURN p", 
  "options" : { 
    "allPlans" : true 
  } 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

A query that produces a warning

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR i IN 1..10 RETURN 1 / 0" 
}
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

Invalid query (missing bind parameter)

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ 
  "query" : "FOR p IN products FILTER p.id == @id LIMIT 2 RETURN p.n" 
}
EOF

HTTP/1.1 400 Bad Request
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

The data returned in the plan attribute of the result contains one element per AQL top-level statement (i.e. FOR, RETURN, FILTER etc.). If the query optimizer removed some unnecessary statements, the result might also contain less elements than there were top-level statements in the AQL query. The following example shows a query with a non-sensible filter condition that the optimizer has removed so that there are less top-level statements.

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/explain <<EOF
{ "query" : "FOR i IN [ 1, 2, 3 ] FILTER 1 == 2 RETURN i" }
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

Parse an AQL query

parse an AQL query and return information about it

POST /_api/query

This endpoint is for query validation only. To actually query the database, see /api/cursor.

A JSON object with these properties is required:

  • query: To validate a query string without executing it, the query string can be passed to the server via an HTTP POST request.

Example:

a Valid query

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/query <<EOF
{ "query" : "FOR p IN products FILTER p.name == @name LIMIT 2 RETURN p.n" }
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "error" : false, 
  "code" : 200, 
  "parsed" : true, 
  "collections" : [ 
    "products" 
  ], 
  "bindVars" : [ 
    "name" 
  ], 
  "ast" : [ 
    { 
      "type" : "root", 
      "subNodes" : [ 
        { 
          "type" : "for", 
          "subNodes" : [ 
            { 
              "type" : "variable", 
              "name" : "p", 
              "id" : 0 
            }, 
            { 
              "type" : "collection", 
              "name" : "products" 
            } 
          ] 
        }, 
        { 
          "type" : "filter", 
          "subNodes" : [ 
            { 
              "type" : "compare ==", 
              "subNodes" : [ 
                { 
                  "type" : "attribute access", 
                  "name" : "name", 
                  "subNodes" : [ 
                    { 
                      "type" : "reference", 
                      "name" : "p", 
                      "id" : 0 
                    } 
                  ] 
                }, 
                { 
                  "type" : "parameter", 
                  "name" : "name" 
                } 
              ] 
            } 
          ] 
        }, 
        { 
          "type" : "limit", 
          "subNodes" : [ 
            { 
              "type" : "value", 
              "value" : 0 
            }, 
            { 
              "type" : "value", 
              "value" : 2 
            } 
          ] 
        }, 
        { 
          "type" : "return", 
          "subNodes" : [ 
            { 
              "type" : "attribute access", 
              "name" : "n", 
              "subNodes" : [ 
                { 
                  "type" : "reference", 
                  "name" : "p", 
                  "id" : 0 
                } 
              ] 
            } 
          ] 
        } 
      ] 
    } 
  ] 
}

Example:

an Invalid query

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/query <<EOF
{ "query" : "FOR p IN products FILTER p.name = @name LIMIT 2 RETURN p.n" }
EOF

HTTP/1.1 400 Bad Request
content-type: application/json; charset=utf-8
x-content-type-options: nosniff

{ 
  "error" : true, 
  "errorMessage" : "syntax error, unexpected assignment near '= @name LIMIT 2 RETURN p.n' at position 1:33", 
  "code" : 400, 
  "errorNum" : 1501 
}

Return Codes

  • 200: If the query is valid, the server will respond with HTTP 200 and return the names of the bind parameters it found in the query (if any) in the bindVars attribute of the response. It will also return an array of the collections used in the query in the collections attribute. If a query can be parsed successfully, the ast attribute of the returned JSON will contain the abstract syntax tree representation of the query. The format of the ast is subject to change in future versions of ArangoDB, but it can be used to inspect how ArangoDB interprets a given query. Note that the abstract syntax tree will be returned without any optimizations applied to it.
  • 400: The server will respond with HTTP 400 in case of a malformed request, or if the query contains a parse error. The body of the response will contain the error details embedded in a JSON object.

Examples

a Valid query

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/query <<EOF
{ "query" : "FOR p IN products FILTER p.name == @name LIMIT 2 RETURN p.n" }
EOF

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

an Invalid query

shell> curl -X POST --data-binary @- --dump - http://localhost:8529/_api/query <<EOF
{ "query" : "FOR p IN products FILTER p.name = @name LIMIT 2 RETURN p.n" }
EOF

HTTP/1.1 400 Bad Request
content-type: application/json; charset=utf-8
x-content-type-options: nosniff
show response body

Query tracking

ArangoDB has an HTTP interface for retrieving the lists of currently executing AQL queries and the list of slow AQL queries. In order to make meaningful use of these APIs, query tracking needs to be enabled in the database the HTTP request is executed for.

Returns the properties for the AQL query tracking

returns the configuration for the AQL query tracking

GET /_api/query/properties

Returns the current query tracking configuration. The configuration is a JSON object with the following properties:

  • enabled: if set to true, then queries will be tracked. If set to false, neither queries nor slow queries will be tracked.

  • trackSlowQueries: if set to true, then slow queries will be tracked in the list of slow queries if their runtime exceeds the value set in slowQueryThreshold. In order for slow queries to be tracked, the enabled property must also be set to true.

  • maxSlowQueries: the maximum number of slow queries to keep in the list of slow queries. If the list of slow queries is full, the oldest entry in it will be discarded when additional slow queries occur.

  • slowQueryThreshold: the threshold value for treating a query as slow. A query with a runtime greater or equal to this threshold value will be put into the list of slow queries when slow query tracking is enabled. The value for slowQueryThreshold is specified in seconds.

  • maxQueryStringLength: the maximum query string length to keep in the list of queries. Query strings can have arbitrary lengths, and this property can be used to save memory in case very long query strings are used. The value is specified in bytes.

Return Codes

  • 200: Is returned if properties were retrieved successfully.
  • 400: The server will respond with HTTP 400 in case of a malformed request,

Changes the properties for the AQL query tracking

changes the configuration for the AQL query tracking

PUT /_api/query/properties

A JSON object with these properties is required:

  • slowQueryThreshold: The threshold value for treating a query as slow. A query with a runtime greater or equal to this threshold value will be put into the list of slow queries when slow query tracking is enabled. The value for slowQueryThreshold is specified in seconds.
  • enabled: If set to true, then queries will be tracked. If set to false, neither queries nor slow queries will be tracked.
  • maxSlowQueries: The maximum number of slow queries to keep in the list of slow queries. If the list of slow queries is full, the oldest entry in it will be discarded when additional slow queries occur.
  • trackSlowQueries: If set to true, then slow queries will be tracked in the list of slow queries if their runtime exceeds the value set in slowQueryThreshold. In order for slow queries to be tracked, the enabled property must also be set to true.
  • maxQueryStringLength: The maximum query string length to keep in the list of queries. Query strings can have arbitrary lengths, and this property can be used to save memory in case very long query strings are used. The value is specified in bytes.

The properties need to be passed in the attribute properties in the body of the HTTP request. properties needs to be a JSON object.

After the properties have been changed, the current set of properties will be returned in the HTTP response.

Return Codes

  • 200: Is returned if the properties were changed successfully.
  • 400: The server will respond with HTTP 400 in case of a malformed request,

Returns the currently running AQL queries

returns a list of currently running AQL queries

GET /_api/query/current

Returns an array containing the AQL queries currently running in the selected database. Each query is a JSON object with the following attributes:

  • id: the query's id

  • query: the query string (potentially truncated)

  • bindVars: the bind parameter values used by the query

  • started: the date and time when the query was started

  • runTime: the query's run time up to the point the list of queries was queried

  • state: the query's current execution state (as a string)

Return Codes

  • 200: Is returned when the list of queries can be retrieved successfully.
  • 400: The server will respond with HTTP 400 in case of a malformed request,

Returns the list of slow AQL queries

returns a list of slow running AQL queries

GET /_api/query/slow

Returns an array containing the last AQL queries that are finished and have exceeded the slow query threshold in the selected database. The maximum amount of queries in the list can be controlled by setting the query tracking property maxSlowQueries. The threshold for treating a query as slow can be adjusted by setting the query tracking property slowQueryThreshold.

Each query is a JSON object with the following attributes:

  • id: the query's id

  • query: the query string (potentially truncated)

  • bindVars: the bind parameter values used by the query

  • started: the date and time when the query was started

  • runTime: the query's total run time

  • state: the query's current execution state (will always be "finished" for the list of slow queries)

Return Codes

  • 200: Is returned when the list of queries can be retrieved successfully.
  • 400: The server will respond with HTTP 400 in case of a malformed request,

Clears the list of slow AQL queries

clears the list of slow AQL queries

DELETE /_api/query/slow

Clears the list of slow AQL queries

Return Codes

  • 200: The server will respond with HTTP 200 when the list of queries was cleared successfully.
  • 400: The server will respond with HTTP 400 in case of a malformed request.

    Killing queries

Running AQL queries can also be killed on the server. ArangoDB provides a kill facility via an HTTP interface. To kill a running query, its id (as returned for the query in the list of currently running queries) must be specified. The kill flag of the query will then be set, and the query will be aborted as soon as it reaches a cancelation point.

Kills a running AQL query

kills an AQL query

DELETE /_api/query/{query-id}

Path Parameters

  • query-id (required): The id of the query.

Kills a running query. The query will be terminated at the next cancelation point.

Return Codes

  • 200: The server will respond with HTTP 200 when the query was still running when the kill request was executed and the query's kill flag was set.
  • 400: The server will respond with HTTP 400 in case of a malformed request.
  • 404: The server will respond with HTTP 404 when no query with the specified id was found.

© ArangoDB - the native multi-model NoSQL database