How to Use the PostgreSQL JSON Query

Introduction

One key benefit of PostgreSQL is that even though it’s a relational database, users still have the ability to work with unstructured data by storing that data in a JSON colum. PostgreSQL supports JSON-compliant data types and offers multiple operators to assist in querying JSON records. In this article, we’ll show you how to perform a PostgreSQL JSON query, searching specific keys and data fields in the JSON column of a PostgreSQL table.

Prerequisites

Before proceeding with the instructions in this tutorial, you’ll need to make sure a PostgreSQL database cluster is installed on your device. To check if it’s already installed, use the command sudo systemctl status PostgreSQL; after you get a response, press Q to exit. You can also see if PostgreSQL is installed by typing psql -V in the terminal.

Connect to the psql

In order to perform a PostgreSQL JSON query, we’ll need to first enter the psql command-line interface:

You’ll be asked for a password– type it in and press ENTER. Then, use the following command to access a specific database with specific user credentials:

The JSON data type in PostgreSQL

JSON, which stands for Javascript Object Notation, is a data format that consists of attributes in the form of key-value pairs. It’s readable by humans and serves as a standard file format that can transmit data between a server and web app.

The JSON format supports different datatypes in a PostgreSQL JSON query:

• the String datatype for plain text characters:

• the Boolean datatype for true or false values:

• the Number datatype for integers and whole numbers:

• an Array, or ordered list, can be nested inside of a JSON key to store multiple values:

• the Object datatype is used for an unordered list of key-value pairs:

Create database and table

Let’s begin by creating a database that we can use in our examples. To create a database in PostgreSQL, use the following command:

Next, enter the database and use the \c command, followed by the database name, to connect to a PostgreSQL database. We’ll create a table inside this database using the JSON datatype.

To create a table in PostgreSQL, we’ll use the statement shown below:

Insert records in table

Now that we’ve created a table, let’s insert a record into it using the JSON format:

NOTE: You can also insert multiple JSON data records using the same command syntax.

Let’s see if we can display the JSON data that we just inserted. We’ll use the SELECT statement:

This SQL statement will return the JSON file as shown below:

PostgreSQL JSON query operators

There are two types of operators that can be used to query JSON data:

• (->) and (->>) operators: We can pass a string or integer to these operators to identify a specific element in a JSON array that matches either the numeric position or the string match for the key. The difference between the two operators is that the first one will return a JSON object, but the ->> operator will return a JSONB (binary JSON) object.

The following example will use -> to return all of the name values in a JSON column:

Now let’s look at the same query using the ->> operator to return the JSON strings in binary format:

NOTE: As you can see, the difference between them is that the first operator returns the key object field of the JSON while the second returns the text.

In the next query, we’ll try to retrieve specific data using both of the operators:

Using where clause

We can also filter the rows that are returned by using the WHERE clause in a query.

Let’s imagine we want to return records for anyone in who is living in America:

The results would look like this:

Conclusion

Being able to store and manage JSON data in your PostgreSQL database gives you the best of both worlds: the flexibility of the JSON format paired with the advantages of a relational database model. Fortunately, PostgreSQL makes it easy to query for whatever JSON data you might need using a simple SQL statement. In this article, we showed you several examples of creating a PostgreSQL JSON query. With these examples and instructions, you’ll be able to create queries for your own JSON data.