Database and JSON

Go back to Tutorial

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.

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:

db.artists.insert({

artistname : “Deep Purple”,

albums : [

{

album : “Machine Head”,

year : 1972,

genre : “Rock”

},

 

{

album : “Stormbringer”,

year : 1974,

genre : “Rock”

}

]

})

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.

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:

SELECT artistname, formed

FROM artists

FOR JSON AUTO

Go back to Tutorial

Get industry recognized certification – Contact us

Menu