Working with JSON data in PostgreSQL

PostgreSQL supports working with JSON. In this post, I’ll explore ways to work with JSON data in PostgreSQL.

Why JSON data in PostgreSQL

Here are a few major reasons:

• JSON data allows flexibility of schema. JSON is the way to go if there are rapidly changing data structures.
• It helps to avoid complicated joins on isolated data. For example, you can store a product metadata as a JSON column. Then you can fetch it with other fields in a single query without any joins or sub-queries.
• You might need to store data from external services e.g. Payment gateways like Stripe or Razorpay, which will have nested data. you can store it as it is without spending time on normalization. You can still query it.
• You might be supporting a legacy system where data was not modeled properly and you need to support JSON columns. The features, operators, and functions described in this post might become handy.

JSONB (JSON Binary) in PostgreSQL

JSON and JSONB types

PostgreSQL provides 2 data types for storing the JSON data. There are 2 notable differences:

• json stores the data as text, whereas jsonb stores the data in binary format.
• json type is faster for inserting the data, whereas jsonb allows faster querying of the data

In short, jsonb should be used when the data is nested and large, also the data needs querying.

Creating a column with jsonb

Here is a simple example.

Inserting JSON data

Let’s insert some rows in the above table

Querying the value in JSON Object

Suppose you want to query all the colors of the products with the above data. You can use -> or ->> operators.

The output will be like this

Notice the “quotes” around the values.

Let’s use ->> operator

Notice that there are no quotes in the output now

Difference between -> and ->> operators

-> operator extracts a JSON object field by a key but it doesn’t parse the output into text, it keeps the output as jsonb or json depending upon the original object.

->> operator extracts a JSON object field as a text (as you saw in the example above)

Getting the nested value from JSON using -> and ->> operators

Let’s take an example where you want to get the length of each product in the above example. It is a nested property under the dimensions property

You need to use both operators to get the desired value, like this

Output

JSON property inside where clause

If you want to get all the products with a length of more than 20, you need to use the ->> operator in WHERE Clause

Removing an attribute while fetching JSON field

There can be instances when you don’t need all the fields of the JSON column in the output. You can use the minus - operator like this.

This query will remove dimensions and sizes from the metadata in the output.

Indexing in JSON fields

To improve the performance of your queries. You can create an index on a single field nested inside JSON.

For example, if you wanted to index the length property, you can do it like this.

You can also use GIN indexing for even more complex queries. GIN (Generalized Inverted Index) helps in improving the performance of queries involving columns with multiple components like JSON.

Updating JSON column with jsonb_set()

jsonb_set() is used to insert or update the value inside the json object.

jsonb_set() takes 3 parameters:

1. source jsonb object
2. path to be updated
3. new value

Working with JSON arrays

Let’s explore a few ways to work with json arrays in PostgreSQL

Create json arrays using json_agg()

This function will take an array of objects and convert it to json. Let’s have a look at the example.

Here is the data inside categories table

The following query will create json array of categories

Output

Getting value at index in JSON array

You can use the -> and ->> operators to get the value at a particular index.

Suppose we want to get the first element in the sizes array of the product metadata. The first element has a 0 index, so here is the query.

Here is the output

Notice the same difference when we used these operators for accessing JSON Object.

Querying JSON Array with jsonb_array_elements_text

json_array_elements expands the elements in JSON array into multiple SQL rows. Let’s understand with the help of an example.

In the example of the product, if you wanted to list all the products which have XXXL size available.

You can use json_array_elements_text(), json_array_elements(), or jsonb_array_elements() depending on the data types and requirements.

Output

Finding the length of json array using json_array_length()

As it is clear from the name, json_array_length is used to find out the length of json array elements. Use jsonb_array_length() , if the type of column is jsonb[]

For example, if you want to find out a number of sizes available for each product.

Output for the above query

Checking the existence of a key in JSON Object using?

If you want to check if a particular top-level key exists, then you can use the? operator.

Suppose you want to know which of the product dimensions has a height key

Output

Expanding the JSON Object into key-value pairs using jsonb_each()

We can list all the key-value pairs of a JSON object using jsonb_each

For example, if we want to list all the metadata information of a product with id=2

Output

This is similar to jsonb_array_elements but it works for objects instead of arrays

Conclusion

PostgreSQL provides a very comprehensive set of tools for working with JSON Objects as well as JSON arrays. I have just given a very brief introduction here.

There are many other operators like #> , #>> , @> , <@ , ? , #- , @? , @@ which we haven’t explored in this post. Similarly, there are many other in-built Postgres functions as well.

Let me know if you have any questions or ideas regarding this post. Reach out if you need any help in PostgreSQL.