Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL
Tags: database, sql, programming, discuss
Published: 2021-02-03 21:39:58

---
cover_image: https://blog.arctype.com/content/images/2021/02/Screen-Shot-2021-02-03-at-2.02.27-PM.png
title: Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL
published: true
date: 2021-02-03 20:42:00 UTC
tags: Database, SQL, Programming, Discuss
canonical_url: https://blog.arctype.com/json-in-postgresql/
---
Have you ever started a project and asked - "should I use a SQL or NoSQL database?"

It’s a big decision. There are multiple horror stories of developers [choosing a NoSQL database and later regretting it](http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-mongodb/).

But now you can get the best of both worlds with JSON in PostgreSQL.

In this article I cover the benefits of using JSON, anti-patterns to avoid, and an example of how to use JSON in Postgres.

## Table of Contents
- [When to use JSON in Postgres](#chapter-1)
- [JSON Basics.](#chapter-2)
- [Types of JSON in PostgreSQL.](#chapter-3)
- [Creating a JSON table](#chapter-4)
- [How to query JSON data](#chapter-5)
- [JSONPath: advanced JSON syntax](#chapter-6)
- [Arctype: An Easy Tool for Databases](#chapter-7)

## Why Use a SQL Database for Non-Relational Data? <a name="chapter-1"></a>

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xy2q9dydnlvzan5my9ti.png)
<figcaption>Example of normalized data in a school database</figcaption>

First we have to briefly cover the advantages of using SQL vs NoSQL.

The difference between SQL and NoSQL is the data model. SQL databases use a _relational data model_, and NoSQL databases usually use a _document model_. A key difference is how each data model handles **data normalization**.

Data normalization is the process of splitting data into “normal forms” to reduce _data redundancy_. [The concept was first introduced in the 1970s as a way to reduce spending on expensive disk storage](https://blog.arctype.com/sql-50-years/).

In the example above, we have a normalized entity relationship diagram for a school database. The `StudentClass` table stores every class a student has taken. By normalizing the data, we only keep one row for each class in the `Class` table, instead of duplicating class data for every student in the class.

But what if we also wanted to track every lunch order (entree, sides, drink, snacks, etc) to send each student a summary at the end of every week?

In this case it would make more sense to store the data in a single _document_ instead of normalizing it. Students will always be shown their entire lunch order, so we can avoid expensive joins by keeping the lunch order data together.

```
{
	"student_id": 100,
    "order_date": "2020-12-11",
	"order_details": {
    	"cost": 5.87,
        "entree": ["pizza"],
        "sides": ["apple", "fries"],
        "snacks": ["chips"]
	}
}
```
<figcaption>Example schema for lunch orders using JSON</figcaption>

Instead of maintaining a separate NoSQL database, we now  store lunch orders as JSON objects inside an existing relational Postgres database.

## What is JSON? <a name="chapter-2"></a>

```
{
	"student_id": 100,
    "order_date": "2020-12-11",
	"order_details": {
    	"cost": 5.87,
        "entree": ["pizza"],
        "sides": ["apple", "fries"],
        "snacks": ["chips"]
	}
}
```
<figcaption>JSON Example</figcaption>

| Name | Data type |
| --------- |:---------:|
| student_id | Integer |
| order_date | Date |
| order_details | Object |
| sides | Array |
 
JSON, or Javascript Object Notation, is a flexible format to pass data between applications, similar to a csv file. However, instead of rows and columns, JSON objects are collections of key/value pairs.

According to Stack Overflow, JSON is now the [most popular data interchange format](https://insights.stackoverflow.com/trends?tags=json%2Cxml%2Cprotocol-buffers%2Cyaml%2Ccsv), beating csv, yaml, and xml.

The original creator of JSON, Douglas Crockford, attributes the [success of JSON](https://twobithistory.org/2017/09/21/the-rise-and-rise-of-json.html) to its readability by both developers and machines, similar to [why SQL has been dominant for almost 50 years](https://blog.arctype.com/sql-50-years/).

The JSON format is easy to understand, but also flexible enough to handle both primitive and complex data types.

## Evolution of JSON in PostgreSQL <a name="chapter-3></a>
### Plain JSON type

In 2012, PostgreSQL 9.2 introduced the first `JSON` data type in Postgres. It had syntax validation but underneath it stored the incoming document directly as text with white spaces included. It wasn’t very useful for real world querying, index based searching and other functionalities you would normally do with a JSON document.

### JSONB

In late 2014, PostgreSQL 9.4 introduced the `JSONB` data type and most importantly improved the querying efficiency by adding **[indexing](https://blog.arctype.com/database-index/).**

The `JSONB` data type stores JSON as a binary type. This introduced overhead in processing since there was a conversion involved but it offered the ability to index the data using GIN/Full text based indexing and included additional operators for easy querying.

### JSONPath

With JSON’s increasing popularity, the 2016 SQL Standard brought in a new   standard/path language for navigating JSON data. It’s a powerful way of     searching JSON data very similar to XPath for XML data. PostgreSQL 12 introduced support for the JSON Path standard.

We will see examples of JSON, JSONB, and JSONPath in the sections below. An important thing to note is that all JSON functionality is natively present in the database. There is no need for a contrib module or an external package to be installed.

## JSON Example in Postgres <a name="chapter-4"></a>

Lets create a Postgres table to store lunch orders with a JSON data type.

```
create table LunchOrders(student_id int, order json);
```

Now we can insert JSON formatted data into our table with an `INSERT` statement.

```
insert into LunchOrders values(100, '{
	"order_date": "2020-12-11",
	"order_details": {
    	"cost": 4.25,
        "entree": ["pizza"],
    	"sides": ["apple", "fries"],
    	"snacks": ["chips"]}
	}'      
);

insert into LunchOrders values(100, '{
	"order_date": "2020-12-12",
	"order_details": {
    	"cost": 4.89,
        "entree": ["hamburger"],
    	"sides": ["apple", "salad"],
    	"snacks": ["cookie"]}
	}'      
);
```

If you do a  `Select *`  from the table, you would see something like below.

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-30-at-10.10.32-PM.png)<figcaption>Get JSON objects. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

Inserting data into a `JSONB` column is exactly the same, except we change the data type to `jsonb`.

```
create table LunchOrders(student_id int, orders jsonb);
```
## How to Query JSON Data in Postgres <a name="chapter-5"></a>

Querying data from JSON objects uses slightly different operators than the ones that we use for regular data types ( `=`, `<` , `>`, etc).

Here are some of the most common JSON operators:

| Operator | Description |
| --------- |:---------:|
| -> | Select a key/value pair |
| ->> | Filter query results |
| #> | Selecting a nested object |
| #>> | Filter query results in a nested object |
| @> | Check if an object contains a value |

[Full list of JSON operators](https://www.postgresql.org/docs/12/functions-json.html).

The `->` and `->>` operators work with both `JSON` and `JSONB` type data. The rest of the operators are _full text search_ operators and only work with the `JSONB` data type.

Let's see some examples of how to use each operator to query data in our `LunchOrders` table.

### Getting values from a JSON object

We can use the `->` operation to find every day that a specific student bought a school lunch.

```
select orders -> 'order_date'
from lunchorders
where student_id = 100;
```

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-30-at-10.13.44-PM.png)<figcaption>Select JSON data. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

### Filtering JSON data using a `WHERE` clause

We can use the `->>` operator to filter for only lunch orders on a specific date.

```
select orders
from lunchorders
where orders ->> 'order_date' = '2020-12-11';
```

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-30-at-10.44.10-PM.png)<figcaption>Filter JSON by date. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

This query is similar to the `=` operator that we would normally use, except we have to first add a `->>` operator to tell Postgres that the `order_date` field is in the `orders` column.

### Getting data from an array in a JSON object 

Let's say we wanted to find every side dish that a specific student has ordered.

The `sides` field is nested inside the `order_details` object, but we can access it by chaining two `->` operators together.

```
select
  orders -> 'order_date',
  orders -> 'order_details' -> 'sides'
from
  lunchorders
where
  student_id = 100;
```

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-30-at-11.05.10-PM.png)<figcaption>Getting nested values from a JSON object. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

Great now we have arrays of the sides that student 100 ordered each day! What if we only wanted the first side in the array? We can chain together a _third_ `->` operator and give it the **array index** we're looking for.

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-30-at-11.10.14-PM.png)<figcaption>Getting array values at a specific index. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

### Retrieving nested values from a JSON object

Instead of chaining together multiple `->` operators, we can also use the `#>` operator to specify a path for retrieving a nested value.

```
select orders #> '{order_details, sides}'
from lunchorders;

      ?column?      
--------------------
 ["apple", "fries"]
 ["apple", "salad"]
(2 rows)
```

### Checking if a JSON object contains a value

Lets say we wanted to see every order a student made that had a side salad. We can't use the previous `->>` for filtering because `sides` is an array of values.

To check if an array or object contains a specific value, we can use the `@>` operator:

```
select
  orders
from
	lunchorders
where
	orders -> 'order_details' -> 'sides' @> '["salad"]';

orders                                                                   
----------
 {"order_date": "2020-12-12", "order_details": {"cost": 4.89, "sides": ["apple", "salad"], "entree": ["hamburger"], "snacks": ["cookie"]}}
(1 row)
```
## JSONPath: The Final Boss <a name="chapter-6"></a>

JSON Path is a powerful tool for searching and manipulating a JSON object in SQL using _[JavaScript-like syntax](https://www.postgresql.org/docs/12/datatype-json.html#DATATYPE-JSONPATH)_:

- Dot (.) is used for member access.
- Square brackets ("[]") are used for array access.
- SQL/JSON arrays are 0-indexed, unlike regular SQL arrays that start from 1.

### Built-in functions

JSONPath also includes powerful built-in functions like `size()` to find the length of arrays.

Let's use the JSONPath `size()` function to get every order that had >= 1 snack.

```
select *
from lunchorders
where orders @@ '$.order_details.snacks.size() > 0';
```

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/01/Screen-Shot-2021-01-31-at-10.51.50-AM.png)<figcaption>JSONPath Build-in functions example. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

### Comparison without type casting

JSONPath also enables comparisons without explicit type casting:

```
select *
from lunchorders
where orders @@ '$.order_details.cost > 4.50';
```

This is what the same query would look like with our regular JSON comparisons:
```
select *
from lunchorders
where (orders -> 'order_details' ->> 'cost')::numeric > 4.50;
```

## JSON Summary <a name="chapter-7"></a>

In this article we've covered:
- When to use SQL vs NoSQL
- A history of JSON in Postgres
- Examples of how to work with JSON data
- JSON query performance with indexing
- JSON anti-patterns

Working with JSON data can be complicated. [Arctype is a free, modern SQL editor](https://arctype.com?utm_campaign=json-postgres&utm_medium=blog&utm_source=devto) that makes working with databases easier. Try it out today and leave a comment below with your thoughts!

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/02/json_update-2.gif)<figcaption>JSONPath Build-in functions example. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

Sneak peek at part 2 of the JSON in Postgres series:

With our existing table, the database engine has to scan through the entire table to find a record. This is called a _sequential scan_.

![Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL](https://blog.arctype.com/content/images/2021/02/Screen-Shot-2021-02-03-at-9.51.26-AM.png)<figcaption>Analyzing JSON query performance. SQL Editor: <a href="https://arctype.com?utm_campaign=json-postgres&amp;utm_medium=blog&amp;utm_source=devto">Arctype</a></figcaption>

Sequential scans become degrade in performance as the dataset grows.

Follow to get notified of Part 2 where I will show show how to index a dataset with 700k rows to improve query performance by 350X.