Core (SQL) API – Nonrelational Databases in Azure

Core (SQL) API

The Azure Cosmos DB Core (SQL) API is a document database service and is the default API for Azure Cosmos DB. As the name implies, the Core (SQL) API is the core, or native, API for working with NoSQL data in Azure Cosmos DB. This API is recommended for new applications that require high performance and global distribution and when migrating to Azure from other NoSQL database platforms. The Core (SQL) API is also the recommended migration option for relational databases that require the benefits of a NoSQL database.

The data model for the Core (SQL) API uses the default hierarchy of resources where an account hosts one or more databases, a database hosts a set of containers, and a container stores data as items. Items are formatted as JSON documents and can be interacted with using SQL.

SQL syntax used by the Core (SQL) API is very familiar to T-SQL with some additional functionality that is specialized for interacting with JSON data. For example, an application that is querying the Persons container of a database in an Azure Cosmos DB Core (SQL) API account might want to retrieve information about the user stored in the following JSON document:

{
    “firstname”: “John”,
    “lastname”: “Smith”,
    “age”: 23,
    “favoriteSports”: {
        “mostFavorite”: “Basketball”,
        “secondFavorite”: “Baseball”
    },
    “id”: “de5760d6-64fd-4dc3-8cb9-cc914ee860b0”,
}

The application can use the following SQL query to return the user’s first name and their most favorite sport:

SELECT p.firstname, p.favoriteSports.mostFavorite
FROM Persons AS p
WHERE p.id = ‘de5760d6-64fd-4dc3-8cb9-cc914ee860b0’

The results from the query are:

[
    {
        “firstname”: “John”,
        “mostFavorite”: “Basketball”
    }
]

Along with SQL, the Core (SQL) API supports user-defined functions and stored procedures written in JavaScript.

API for MongoDB

MongoDB is a popular document database platform that stores data items as Binary JSON (BSON) documents. Organizations wanting to take advantage of the scalability, performance, high availability, and ease of maintenance that Azure Cosmos DB provides without changing any existing code can do so by migrating their MongoDB databases to the Azure Cosmos DB API for MongoDB.

Data can be migrated to the API for MongoDB using tools such as mongodump, mongorestore, or the Azure native Azure Database Migration Service. Once the data is in Azure, organizations can continue using their existing MongoDB applications by just changing the connection string. Tools that are native to MongoDB such as the MongoDB shell and MongoDB Compass can interact with databases hosted on the API for MongoDB just as they would with MongoDB hosted in an on-premises datacenter.