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
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
- Update it using the
- Delete it using any of the
MongoDB also provides the
mongoimport utility for importing JSON, CSV, or TSV files into a MongoDB database.
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.