Postgres JSON data type and how to query it?

Postgres has been a rock-solid database choice for many decades. There is a huge ecosystem of tools, plugins, and extensions that play very well with Postgres. Although there was this era when there was too much hype around these new database paradigms (ahem MongoDB 🙄), and SQL databases were considered NOT COOL. But soon people realized the value of having a strict schema and the simplicity of SQL queries to get results. Through all this, SQL databases kept evolving and adding new features that made them an even better choice for a large number of use cases.

JSON and JSONB support were some of those features. Since version 9.2, Postgres has provided many operators and functions to handle and manipulate JSON data. Which gives you the flexibility to just dump some JSON data in a field that you can query later.

So, let’s take a look at the Postgres JSON data type, how it works, and when to use it and when not to. 🧐

JSON? JSONB? What is it anyway? 🤷‍♂️

JSON stands for JavaScript Object Notation. It is an open standard format that consists of key-value pairs. JSON is mainly used to send and receive data from servers. In Postgres, the JSON data is stored as an exact copy of the input text. That makes the writing fast, but every time you have to read the field, the processing functions reparse the value, which makes the reading slow. Postgres JSONB overcomes this issue by storing the data in a decomposed binary format that makes it significantly faster to process since no parsing is needed, but slightly slower to input due to the added data conversion overhead. To take it further, Postgres JSONB also supports indexing.

How to create JSON fields in Postgres 🔨

Creating a table with a JSON/JSONB field is very straightforward. You can create a table and define the data type of the field as JSON or JSONB.

CREATE TABLE author (
	id SERIAL NOT NULL PRIMARY KEY,
	name TEXT NOT NULL,
	email TEXT NOT NULL,
	social_profiles JSON NOT NULL
);

This will create a table with the following columns:

• id - a primary key field
• name - a field that can store text/string values
• email - a field that can store text/string values
• social_profiles - a JSON/JSONB that can store key-value pairs of data.

Insert values in JSON fields in Postgres 📝

To add an entry in a table with a JSON field, you can add the values in quotes.

INSERT INTO author(name, email, social_profiles) VALUES(
	'Jitendra Nirnejak',
	'hello@nirnejak.com',
	'
		{"github": "https://github.com/nirnejak"},
		{"codepen": "https://codepen.io/nirnejak"},
		{"linkedin": "https://linkedin/com/in/nirnejak"},
		{"nirnejak": "https://nirnejak.com/"}
	'
) RETURNING (id)

How to Query JSON fields in PostgreSQL? 🔍

You can get your JSON fields just like any other field. To pull out specific keys and details from JSON fields, you will have to use the JSON operators and functions.

// List social profiles of all the users
SELECT social_profiles FROM author

Postgres JSON Operators

Operators are stock functions for manipulating and accessing data types, and JSONB has its own special ones. You can use these operators to handle and manipulate the Postgres JSON data. They’ll help you filter and aggregate your data.

So, let’s take a look at a few of them and what they do.

• -> and ->> Operator

  This operator helps you get the JSON object field by its key; you can chain them to get data from deeper levels in your JSON object. The difference between -> and ->> is that the latter returns the result in text format.

  // Author's name, email, and github where the Twitter profile matches URL string
  SELECT
      name,
      email,
      social_links -> 'github' AS github
  FROM
      author
  WHERE
      social_links ->> 'twitter' = 'https://twitter.com/jeetnirnejak'
  

• ? Operator

  This operator helps you check if a key exists in your JSON object. It also has some modifiers to check for an array of keys. ?& will check if all the keys defined in an array are in the object, while ?| will check if any of the keys defined in the array exist on the object. Below are a few examples of this.

  // Users with Github
  SELECT * FROM user WHERE social_links ? 'github'
  
  // Users with Github, Codepen and Linkedin
  SELECT * FROM user WHERE social_links ?& ARRAY('github', 'codepen', 'linkedin')
  
  // Users with Github, Codepen or Linkedin
  SELECT * FROM user WHERE social_links ?| ARRAY('github', 'codepen', 'linkedin')
  

• @ Operator

  This operator helps you check if a JSONB element exists at the top level of the JSON object. You can check out the example below to understand it in detail.

  // Does the right JSONB element exists at the top level of the left element
  SELECT * FROM users WHERE social_links @> '{"github": "https://github.com/nirnejak"}'::jsonb
  
  // Does the left JSONB element exists at the top level of the right element
  SELECT * FROM users WHERE '{"github": "https://github.com/nirnejak"}'::jsonb <@ social_links
  

• #> and #>> Operator

  This operator is called the path operator and can help you get the object at a specified path. The difference between #> and #>> is that the latter returns the result as text.

  // Get the Github links of the users
  SELECT social_links #> '{github}' FROM users
  
  /*
  // Get the public Instagram profiles of the users as text
  {
  	github: "",
  	instagram: { personal: "", public: "" },
  }
  */
  SELECT social_links #>> '{instagram,public}' FROM users
  

These are the basic and most common Postgres JSON operators used to query JSON/JSONB data. You can see the detailed list of operators here.

Postgres JSON Functions

Along with a set of operators, Postgres also provides some functions to help you manipulate your Postgres JSON data.

There are mainly two types of functions. Creation and Processing.

• Creation Functions - These functions are available for creating JSON values.
• Processing Functions - These functions are there to help you process and format your JSON data.

Here are some of the common Postgres JSON Functions:

• to_json or to_jsonb()

  This function helps you convert any value to a JSON or JSONB value.

• json_each() or jsonb_each()

  This function will expand the outermost JSON/JSONB object into a set of key/value pairs.

• json_each_text() or jsonb_each_text()

  This is similar to the above function. This function will expand the outermost JSON/JSONB object into a set of key/value pairs and return it as a text value.

• json_object_keys() or jsonb_object_keys()

  This function will return the outermost keys present in the JSON/JSONB object.

• jsonb_pretty()

  This function will return a formatted (prettified) JSON value.

There are more Postgres JSON functions; to learn about them, you can follow this link.

When should you use Postgres JSON fields? 👍

1. Unpredictable data structures - Postgres JSON or JSONB fields can be a good choice for storing data whose schema might be unpredictable. The fields might change frequently, and data coming in that you might not control and can’t fully predict.
2. Lots of attributes that are rarely used - Another really good use case for Postgres JSON and JSONB fields is to store attributes or data that is rarely used. It wouldn’t make sense to store them as a dedicated field since most of the time they will be empty.
3. Replacing Entity-attribute-value model tables - If you have a table that’s used to store key-value data, i.e., the first column is treated as the key and the second column is treated as its associated value, you can replace those with JSON/JSONB fields.
4. Arbitrary fields with dynamic values - If you need to store unknown fields and some data associated with them, you can use Postgres JSON or JSONB fields for that. Since those fields are unknown at the moment, you can’t create a table for them.

Along with these, also consider how often you’ll be querying the data in the JSON/JSONB field, whether they have a default value, and how much control you want over the schema of the JSON object. In general, it can often be used to store data that’s not part of your schema, data you might not want to write migrations for, tags, temporary fields, configs, and settings.

When should you not use Postgres JSON fields? 🙅‍♂️

1. Fields that (almost) always exist - It’s not the best idea to store the fields in JSON or JSONB if they exist in most of the entries. For each column, both the key and the value are stored. So, if your field has millions of entries and most of them have few JSON or JSONB fields in common, it makes more sense to store them in a dedicated table field.
2. Indexing Concerns - JSONB fields aren’t easy to index.
3. Disk Space Limitations - Since it takes more memory to store the JSON/JSONB fields, you should keep this in consideration if you have disk space limitations.

Along with these points, you should also consider if your fields need a default value. It might be hard to enforce it if your data is in the JSON/JSONB field. You should also consider how you’re going to do analytics on the fields. A JSON field without a strict schema might be harder to use for analytical queries. Before making a decision, make sure you’re clear on the pros, cons, and use cases.

I hope this blog helped you learn about JSON and JSONB fields in Postgres enough to get started. To learn about Postgres JSON and JSONB in detail, you can follow the links below. You can also go through the Postgres documentation. There’s also an HStore data type field in Postgres, which is similar to JSONB, and I’m hoping to cover it in the upcoming blogs. Till then, Adios!

Useful Links

Postgres JSON Functions Documentation: JSON Functions and Operations

Postgres JSON Types Documentation: JSON Types

- Jitendra Nirnejak