Редактиране

Споделяне чрез

JavaScript query API in Azure Cosmos DB

  • Статия

APPLIES TO: NoSQL

In addition to issuing queries using the API for NoSQL in Azure Cosmos DB, the Azure Cosmos DB server-side SDK provides a JavaScript interface for performing optimized queries in Azure Cosmos DB Stored Procedures and Triggers. You don't have to be aware of the SQL language to use this JavaScript interface. The JavaScript query API allows you to programmatically build queries by passing predicate functions into sequence of function calls, with a syntax similar to ECMAScript5's array built-ins and popular JavaScript libraries like Lodash. Queries are parsed by the JavaScript runtime and efficiently executed using Azure Cosmos DB indices.

Supported JavaScript functions

Function Description
chain() ... .value([callback] [, options]) Starts a chained call that must be terminated with value().
filter(predicateFunction [, options] [, callback]) Filters the input using a predicate function that returns true/false in order to filter in/out input documents into the resulting set. This function behaves similar to a WHERE clause in SQL.
flatten([isShallow] [, options] [, callback]) Combines and flattens arrays from each input item into a single array. This function behaves similar to SelectMany in LINQ.
map(transformationFunction [, options] [, callback]) Applies a projection given a transformation function that maps each input item to a JavaScript object or value. This function behaves similar to a SELECT clause in SQL.
pluck([propertyName] [, options] [, callback]) This function is a shortcut for a map that extracts the value of a single property from each input item.
sortBy([predicate] [, options] [, callback]) Produces a new set of documents by sorting the documents in the input document stream in ascending order by using the given predicate. This function behaves similar to an ORDER BY clause in SQL.
sortByDescending([predicate] [, options] [, callback]) Produces a new set of documents by sorting the documents in the input document stream in descending order using the given predicate. This function behaves similar to an ORDER BY x DESC clause in SQL.
unwind(collectionSelector, [resultSelector], [options], [callback]) Performs a self-join with inner array and adds results from both sides as tuples to the result projection. For instance, joining a person document with person.pets would produce [person, pet] tuples. This is similar to SelectMany in .NET LINQ.

When included inside predicate and/or selector functions, the following JavaScript constructs get automatically optimized to run directly on Azure Cosmos DB indices:

  • Simple operators: = + - * / % | ^ & == != === !== < > <= >= || && << >> >>> ~
  • Literals, including the object literal: {}
  • var, return

The following JavaScript constructs do not get optimized for Azure Cosmos DB indices:

  • Control flow: if for while
  • Function calls

For more information, see the Azure Cosmos DB Server Side JavaScript Documentation.

SQL to JavaScript cheat sheet

The following table presents various SQL queries and the corresponding JavaScript queries. As with SQL queries, properties (for example, item.id) are case-sensitive.

Note

__ (double-underscore) is an alias to getContext().getCollection() when using the JavaScript query API.

SQL JavaScript Query API Description
SELECT *
FROM docs		 __.map(function(doc) {
    return doc;
});		 Results in all documents (paginated with continuation token) as is.
SELECT
   docs.id,
   docs.message AS msg,
   docs.actions
FROM docs		 __.map(function(doc) {
    return {
        id: doc.id,
        msg: doc.message,
        actions:doc.actions
    };
});		 Projects the id, message (aliased to msg), and action from all documents.
SELECT *
FROM docs
WHERE
   docs.id="X998_Y998"		 __.filter(function(doc) {
    return doc.id ==="X998_Y998";
});		 Queries for documents with the predicate: id = "X998_Y998".
SELECT *
FROM docs
WHERE
   ARRAY_CONTAINS(docs.Tags, 123)		 __.filter(function(x) {
    return x.Tags && x.Tags.indexOf(123) > -1;
});		 Queries for documents that have a Tags property and Tags is an array containing the value 123.
SELECT
   docs.id,
   docs.message AS msg
FROM docs
WHERE
   docs.id="X998_Y998"		 __.chain()
    .filter(function(doc) {
        return doc.id ==="X998_Y998";
    })
    .map(function(doc) {
       return {
            id: doc.id,
            msg: doc.message
       };
    })
.value();		 Queries for documents with a predicate, id = "X998_Y998", and then projects the id and message (aliased to msg).
SELECT VALUE tag
FROM docs
JOIN tag IN docs.Tags
ORDER BY docs._ts		 __.chain()
    .filter(function(doc) {
        return doc.Tags && Array.isArray(doc.Tags);
    })
    .sortBy(function(doc) {
        return doc._ts;
    })
    .pluck("Tags")
    .flatten()
    .value()		 Filters for documents that have an array property, Tags, and sorts the resulting documents by the _ts timestamp system property, and then projects + flattens the Tags array.

Next steps

Learn more concepts and how-to write and use stored procedures, triggers, and user-defined functions in Azure Cosmos DB: