• Introduction
• Basic Concepts
• Your First Query
• Formulating Simple Queries
• Constraints and Joins
• Counting, Limiting, Sorting
• Complex Values and Relationships
• Going Meta
• Content Data
• Search
• Namespaces and Keys
• Writing Data
• Programming Queries

Counting, Limiting, Sorting

Counting

We've seen counting in the previous section in the form of

"return" : "count"

You can also use this form

"count" : null

The two forms differ in how they affect the results. The first form removes all results and returns only the count, e.g.,

[{
  "name": null,
  "!/film/film/genre": [{
    "name": null,
    "/film/film/directed_by": {
      "id": "/en/robert_redford"
    },
    "return": "count"
  }]
}]

[
  {
    "name": "Comedy",
    "!/film/film/genre": [
      1
    ]
  },
  {
    "name": "Drama",
    "!/film/film/genre": [
      7
    ]
  },
  ...
]

The second form returns the results as usual, but also adds the count to every single result, e.g.,

[{
  "name": null,
  "!/film/film/genre": [{
    "name":  null,
    "/film/film/directed_by": {
      "id": "/en/robert_redford"
    },
    "count": null
  }]
}]

[
  {
    "name": "Comedy",
    "!/film/film/genre": [{
      "/film/film/directed_by": {
        "id": "/en/robert_redford"
      },
      "count": 1,
      "name":  "The Legend of Bagger Vance"
    }]
  },
  {
    "name": "Drama",
    "!/film/film/genre": [
      {
        "/film/film/directed_by": {
          "id": "/en/robert_redford"
        },
        "count": 7,
        "name":  "Ordinary People"
      },
      {
        "/film/film/directed_by": {
          "id": "/en/robert_redford"
        },
        "count": 7,
        "name":  "The Horse Whisperer"
      },
      ...
    ]
  },
  {
    "name": "Family",
    "!/film/film/genre": [{
      "/film/film/directed_by": {
        "id": "/en/robert_redford"
      },
      "count": 1,
      "name":  "A River Runs Through It"
    }]
  },
  {
    "name": "Coming of age",
    "!/film/film/genre": [
      {
        "/film/film/directed_by": {
          "id": "/en/robert_redford"
        },
        "count": 2,
        "name":  "Ordinary People"
      },
      {
        "/film/film/directed_by": {
          "id": "/en/robert_redford"
        },
        "count": 2,
        "name":  "A River Runs Through It"
      }
    ]
  },
  ...
]

In addition to "return" : "count" and "count" : null, there are also "return" : "estimate-count" and "estimate-count" : null. Whereas counts are computed accurately and can incur a lot of time, estimate counts are computed approximately and should be quicker. It is trading accuracy for speed. How accurate an estimate count is depends on the individual query. In general, the more joins a query has, the less accurate the estimate count will be.

Limiting

By default, wherever in your query you have an array [ ... ] or [ { ... } ], the MQL query engine will return at most 100 results. This applies to both the outermost root node of your query as well as inner nodes deep inside the query. The deeper your query is (when visualized as a tree), arrays in inner nodes will cause the more data overall to get returned. Suppose that you have a query 3 levels deep, and the node at each level is an array, then you could potentially get 100 x 100 x 100 = 1,000,000 results in the innermost level. That's costly to retrieve and process.

Sometimes you don't want all results, or even 100 results, for every query node. To get fewer results for a [ { ... } ] node, use "limit" : number. For example, this query returns only 3 films directed by Robert Redford, although he has directed a lot more:

{
  "type":  "/film/film",
  "name":  null,
  "limit": 3,
  "directed_by": {
    "id": "/en/robert_redford"
  }
}

The result is:

[
  {
    "directed_by": {
      "id": "/en/robert_redford"
    },
    "name": "Ordinary People",
    "type": "/film/film"
  },
  {
    "directed_by": {
      "id": "/en/robert_redford"
    },
    "name": "Quiz Show",
    "type": "/film/film"
  },
  {
    "directed_by": {
      "id": "/en/robert_redford"
    },
    "name": "The Horse Whisperer",
    "type": "/film/film"
  }
]

Note that the MQL query engine dutifully returns the constraint

  "directed_by": {
    "id": "/en/robert_redford"
  }

back in every single result entry. That's useless as it doesn't contain any more information that we don't already know, and it takes up precious bandwidth. To eliminate that constraint from the result, use "limit" : 0.

{
  "type":  "/film/film",
  "name":  null,
  "limit": 3,
  "directed_by": {
    "limit" : 0,
    "id": "/en/robert_redford"
  }
}

In order to use "limit" on a [] query node, expand it into a [{ ... }] node:

[ {
  "value" : null,
  "limit" : 3
} ]

or

[ {
  "name" : null,
  "limit" : 3
} ]

When the "limit" directive is applied on the topmost query node, you can then use the "cursor" or "page" envelope parameters to page through the results. We will explain this in the Programming Queries section.

Sorting

Wherever in your query you have an array query node [ { ... } ], you can specify a sorting order for the result entries corresponding to that node. For example, we can get the latest film for each genre of films that Robert Redford has directed using this query:

[{
  "name": null,
  "!/film/film/genre": [{
    "type": "/film/film",
    "name": null,
    "initial_release_date": null,
    "directed_by": {
      "limit": 0,
      "id": "/en/james_cameron"
    },
    "sort": "-initial_release_date",
    "limit": 1
  }]
}]

The films are sorted by their initial release dates, newest first (using "sort": "-initial_release_date"), and then only the first one (for each genre) is returned (using "limit" : 1). The minus sign - means "in descending order" for strings and numbers, or "latest first" for dates.

The expression following the "sort" directive can be a property key in the same query node, or a dot-separated list of property keys that dig deeper into inner query nodes. For example, in order to get the most expensive films for each genre rather than the latest, we can use this query instead:

[{
  "name": null,
  "!/film/film/genre": [{
    "type": "/film/film",
    "name": null,
    "estimated_budget": [{
      "amount": null,
      "currency": "US$"
    }],
    "directed_by": {
      "limit": 0,
      "id": "/en/james_cameron"
    },
    "sort": "-estimated_budget.amount",
    "limit": 1
  }]
}]

Note that the sort expression is a concatenation of two property keys, estimated_budget and amount.

You can also sort by count. This query returns a list of genres of films directed by James Cameron, sorted by how many films in each genre, from highest to lowest:

[{
  "name": null,
  "!/film/film/genre": [{
    "type": "/film/film",
    "directed_by": {
      "id": "/en/james_cameron"
    },
    "return": "count"
  }],
  "sort": "-!/film/film/genre.count"
}]

Grouping

While MQL's support for counting, sorting, and limiting is similar to that of SQL, MQL's grouping is totally different. In fact, there's no explicit support for grouping. How the result entries in a query is grouped depends on how it is phrased. Simply rephrase/re-dangle the query for a different grouping.