How to use JSON with a Database

Some database management systems store data as JSON documents. Here's how you can add JSON to such a database.

There's been an insurgence of database management systems (DBMS) that use JSON to store data. While some of these are relational databases, most are from the NoSQL breed of DBMS. NoSQL databases typically use a different data model to the relational model that's been so popular for many years.

Database management systems that store data as JSON documents are often referred to as document store databases. They are also known as document-oriented database, aggregate database, or simply document store or document database.

How to use JSON with a Document Store Database

Each database management system has its own ways for inserting, updating, and retrieving data.

Document oriented databases store their data as documents. So this means, when you insert data into a document store, you're actually using the DBMS to create a JSON document.

For example, in MongoDB, you can use insert(), insertOne(), or insertMany() to insert JSON documents into a MongoDB database.

Here's an example:

As you can see, the insert() method accepts the JSON as an argument.

The above statement will insert one document into the database. All the data provided is stored in one document.

Once the data is in the database, you can do things like:

Query it using MongoDB's find() method.
Update it using the update() or save() methods.
Delete it using any of the deleteOne(), deleteMany(), or remove() methods.

MongoDB also provides the mongoimport utility for importing JSON, CSV, or TSV files into a MongoDB database.

BSON

MongoDB actually stores the data in BSON format, which is an extension of JSON. BSON, which is short for Binary JSON, contains extensions that allow representation of data types that aren't supported by JSON. For example, BSON has a Date type.

BSON adds some extra information to documents, like length of strings and subobjects. This can speed up traversal.

Check out my MongoDB tutorial to see more about how JSON can work with document stores.

Relational Databases with JSON Support

Although many document oriented databases like MongoDB store data as JSON documents, not all do. Some use XML to store their data, but also have a certain level of support for JSON.

Also, some relational database management systems, such as MySQL, Oracle, PostgreSQL, and SQL Server now offer JSON support. In fact, JSON support in SQL server was one of the most highly ranked requests before it was implemented in SQL Server 2016.

SQL Server 2016 introduced the ability to format and export data as JSON string, load JSON text in tables, extract values from JSON text, index properties in JSON text stored in columns, and more.

One of the JSON features is the FOR JSON clause that can be used to export data from SQL Server as JSON, or format query results as JSON.

Here's an example of using the FOR JSON clause in a SELECT statement to format query results as JSON:

If you're interested to know more about storing JSON in databases, take a look through this list of JSON databases.