JSON Server is a library that connects to a SQL database instance, stores JSON documents in tables and queries JSON documents through a JSON query language. It currently supports SQL Server (version 2012 and onward) and MariaDB (10.0 and onward) as the underlying database, and three query languages: MongoDB query language, Azure DocumentDB query language and JSON Server’s own query language. JSON Server is not an independent database, but a middleware that accepts operations against JSON documents and translates them to SQL queries executed in the underlying system. As such, JSON Server provides a view of a JSON database, while completely operating on SQL Server or MariaDB.

Features

JSON Server provides features a standard JSON database is expected to have. In addition, since JSON Server relies on SQL DBs, it inherits many features that are common in the relational world that are often missing in native JSON databases.

• Databases and collections. A database in JSON Server is a conventional SQL database instance, within which one or more document collections are created through JSON Server’s API’s. Unlike most JSON databases in which operations must be bound to a single collection, JSON Server supports cross-collection operations.
• Data manipulations. JSON Server provides API’s to insert JSON documents to a collection. Document deletion is done by a DELETE statement specifying documents matching criteria. JSON Server does not support modifying an existing document at the moment, a feature that will be supported in a future release. Currently, document modifications can be done by first deleting the document from the collection, modifying it in the application, and then inserting the new document back to the collection. JSON Server provides transaction guarantees. The three steps can be wrapped in one transaction, resulting in no intermediate states in case some steps fail.
• Queries. JSON Server provides a query language to query JSON documents. The language is akin to XQuery, providing much more expressive power than the languages of other alternatives. JSON Server also supports two other popular languages, MongoDB and Azure DocumentDB.
• Transactions. All operations in JSON Server are transaction-safe. What is more, a transaction’s scope can span more than one document, in the same collection or separate collections.
• SQL-related features. JSON Server inherits many features from the SQL database it connects to. Below is a short list of features that are crucial to administration tasks:
  • Security. JSON Server uses the authentication mechanism of the SQL DB it connects to for access control. A user can access a database if SQL DB says so. JSON Server also enjoys other security features of SQL DBs, e.g., encryption.
  • Replication. JSON Server stores JSON documents in SQL databases. A replication of the database will result in a replication of all JSON data. As such, it is convenient to deliver a JSON solution for various in-production environment.
  • Backup. JSON Server maintains SQL databases that are visible to SQL DB administrators. Administrators can apply backup operations to the database explicitly。

Dependency

JSON Server needs the following libraries: (1) Json.NET (opens in new tab), (2)  ADO.NET driver for MySQL (opens in new tab), when you connect to MariaDB.

Getting Started

JSON Server is a DLL library. You reference the library in your application and open a database using a SQL DB connection string.

string connectionString = @"Data Source=(local);Initial Catalog=JsonTesting;Integrated Security=true;";
JsonServerConnection jdb = new JsonServerConnection(connectionString);
try {
  // Connect to a JSON database
  jdb.Open(true);
  jdb.Close();
}
catch(DatabaseException e) {
  // Exception handling goes here
}

string connectionString = @"server=127.0.0.1;uid=root;pwd=xxx;database=jsontesting;";
JsonServerConnection jdb = new JsonServerConnection(connectionString, DatabaseType.Mariadb);
try {
  // Connect to a JSON database
  jdb.Open(true);
  jdb.Close();
}
catch(DatabaseException e) {
  // Exception handling goes here
}

try {
  jdb.Open();
  if (!jdb.ContainsCollection("ExampleCollection")) {
    jdb.CreateCollection("ExampleCollection");
  }
  string doc1 = @"{""name"":""sue"", ""age"":19, ""badges"":[""blue"", ""black""]}";
  string doc2 = @"{""name"":""bob"", ""age"":42, ""badges"":["green"]}";
  jdb.InsertJson(doc1, "ExampleCollection");
  jdb.InsertJson(doc2, "ExampleCollection");
  
  string queryString = @"for d in ('ExampleCollection') WHERE d.age < 30 SELECT Doc(d)";
  using(IDataReader reader = jdb.ExecuteReader(queryString)) {
    while (reader.Read()) {
      // Retrieve results through IDataReader
    }
  }
  jdb.Close();
}
catch (Exception e) {
  // Exception handling goes here  
}

JSON Server 

Copyright (c) 2016 Microsoft Corporation 

All rights reserved. 

MIT License 

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.