• <1 minute

Azure DocumentDB: JavaScript as Modern Day T-SQL

We're taking the Azure DocumentDB vision another step further by introducing a JavaScript language integrated query API to our JavaScript server-side SDK.

Azure DocumentDB's database engine is purposefully designed from the ground up to provide first class support for JSON and JavaScript. Our vision is for JavaScript developers to build applications without having to deal with the entity mappers, schemas, code generation tools, type adornments and other duct tapes. Our deep commitment to JSON and JavaScript inside the database engine is surfaced in the following ways:  

  1. Schema Agnostic Indexing: DocumentDB does not require developers to specify schemas or secondary indexes in order to support consistent queries. The database engine is designed to automatically index every property inside every JSON document, while simultaneously ingesting a sustained volume of rapid writes. This is a crucial step to removing the friction between ever evolving modern applications and the database.
  2. SQL query dialect rooted inside the JavaScript type system: DocumentDB's SQL query dialect is based on the JavaScript's type system. This in-turn not only removes the type system mismatch between JavaScript applications and DocumentDB, but also enables seamless invocation of user defined functions (UDFs) written entirely in JavaScript from within a SQL query.
  3. JavaScript Language Integrated Transactions: As part of our bet on JavaScript, we allow developers to register stored procedures and triggers written in JavaScript with DocumentDB collections. These stored procedures/triggers get executed in a sandboxed manner inside the database engine within an ambient database transaction. The stored procedure (or trigger) can update multiple documents transactionally. The database transaction is committed upon successful completion of the stored procedure (or trigger); the database transaction is aborted when the JavaScript “throw” keyword is executed!
  4. JavaScript Language Integrated Queries: Today, we are pleased to announce we're taking this vision another step further by introducing a JavaScript language integrated query API to our JavaScript server-side SDK.

 

 

JavaScript Language Integrated Queries: The no SQL alternative for NoSQL

 

“DocumentDB's fluent JS queries allow for a JS chaining syntax that's easy to pick up and familiar to those who've used ES5's array built-ins or JS libraries like Lodash or Underscore.”

John-David Dalton, Creator of lodash

  Consider the following two JSON documents (and their tree representations) in a collection:  

{ "locations": [ 
    {
      "country": "Germany",
      "city": "Berlin" 
    },
    {
      "country": "France",    
      "city": "Paris"
    }
  ],  
  "headquarter": "Belgium", 
  "exports": [ 
    { "city": "Moscow" }, 
    { "city": "Athens" }
  ]
}
{ "locations": [
    {
      "country": "Germany",    
      "city": "Bonn", 
      "revenue": 200
    }
  ], 
  "headquarter": "Italy",      
  "exports": [
    {
      "city":"Berlin",
      "dealers": [
        {"name": "Hans"}
      ]
    },
    { "city": "Athens" }
  ]
}

  Previously, you have been able to execute a stored procedure which can query for all “exports” from documents containing the “headquarter” as “Belgium.”  

function() {
  var filterQuery = 'SELECT * from companies c where c.headquarter = "Belgium"';
  var isAccepted = __.queryDocuments(__.getSelfLink(), filterQuery,
    function(err, docs, options) {
      if (err) throw new Error(err.number + err.message);
      __.response.setBody(docs);
    });
  if (!isAccepted) __.response.setBody('Query timed out');
}

  Upon execution, the stored procedure will return the response document with the exports:  

{ 
  "results": [
    {
      "locations": [
        {
          "country": "Germany",
          "city": "Berlin" 
        },
        {
          "country": "France",
          "city": "Paris" 
        }
      ],           
      "headquarter":"Belgium", 
      "exports": [
        { "city": "Moscow" },
        { "city": "Athens" } 
      ]
    }
  ]
} 

  Notice the queries issued from the stored procedure are still in poorly typed SQL. With today's announcement of language integrated queries, you no longer need to write SQL queries in JavaScript. Contrast this with the new language integrated query below:  

function() {
  var resp = __.filter(function(company) {
    return company.headquarter == 'Belgium';
  });
  if (!resp.isAccepted) __.response.setBody('Query timed out');
}

  Cool isn't it?

SQL to JavaScript Query API Cheat Sheet

The following table illustrates a few equivalent queries using DocumentDB's SQL grammar and the new JavaScript query API:  

DocumentDB SQL JavaScript Language Integrated Query
__.queryDocuments(__.getSelfLink(),
      "SELECT * " +
      "FROM docs " +
      "WHERE ARRAY_CONTAINS(docs.Tags, 123)"
    ,
    function(err, docs, options) {
      __.response.setBody(docs);
    });
__.filter(function(x) {
    return x.Tags && x.Tags.indexOf(123) > -1;
});
__.queryDocuments(__.getSelfLink(),
      "SELECT docs.id, docs.message AS msg " +
      "FROM docs " +
      "WHERE docs.id='X998_Y998'"
    ,
    function(err, docs, options) {
      __.response.setBody(docs);
    });
__.chain()
    .filter(function(doc) {
        return doc.id === "X998_Y998";
    })
    .map(function(doc) {
        return {
            id: doc.id,
            msg: doc.message
        };
    })
    .value();
__.queryDocuments(__.getSelfLink(),
      "SELECT VALUE tag " +
      "FROM docs " +
      "JOIN tag IN docs.Tags " +
      "ORDER BY docs._ts"
    ,
    function(err, docs, options) {
      __.response.setBody(docs);
    });
__.chain()
    .filter(function(doc) {
        return doc.Tags && Array.isArray(doc.Tags);
    })
    .sortBy(function(doc) {
    return doc._ts;
    })
    .pluck("Tags")
    .flatten()
    .value()

  To get started with the DocumentDB server-side JavaScript SDK, sign up for DocumentDB and check out our documentation here. If you need any help, please reach out to us on Stack Overflow, the Azure DocumentDB MSDN Developer Forums, or schedule a 1:1 chat with the DocumentDB engineering team. To stay up to date on the latest DocumentDB news and features, follow us on Twitter.