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:

sudo su - Postgres

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:

psql user_name -h 127.0.0.1 -d database_name

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:
{"name": "Michael"}
  • the Boolean datatype for true or false values:
{"correct": true}
  • the Number datatype for integers and whole numbers:
{"Age": 50}
  • an Array, or ordered list, can be nested inside of a JSON key to store multiple values:
{"Department": ["MIS","HR","QC"]}
  • the Object datatype is used for an unordered list of key-value pairs:
{"student_info": "name":"Lesley", "Age":20, "enrolled":true}

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:

CREATE DATABASE sample;

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:

CREATE TABLE demo(
    id SERIAL NOT NULL PRIMARY KEY,
    about JSON NOT NULL
);

Insert records in table

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

INSERT INTO demo(about) VALUES
('{"name":"Shenai", "info":{"Age":50, "address":"Mabalacat", "graduated":true}}');

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:

SELECT about FROM demo;

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

                                     about
-------------------------------------------------------------------------------
 {"name":"Shenai", "info":{"Age":50, "address":"Mabalacat", "graduated":TRUE}}
 {"name":"Kirkis", "info":{"Age":20, "address":"America", "graduated":FALSE}}
 {"name":"Lissa", "info":{"Age":25, "address":"Australia", "graduated":TRUE}}
 {"name":"Frams", "info":{"Age":30, "address":"India", "graduated":TRUE}}
(4 ROWS)

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:

SELECT about -> 'name' AS name FROM demo;
   name
----------
 "Shenai"
 "Kirkis"
 "Lissa"
 "Frams"

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

SELECT about ->> 'name' AS name FROM demo;
  name
--------
 Shenai
 Kirkis
 Lissa
 Frams

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:

SELECT about -> 'info' ->> 'address' AS location FROM demo;
 location
-----------
 Mabalacat
 America
 Australia
 India

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:

SELECT about ->> 'name' AS name
FROM demo WHERE about -> 'info' ->> 'address' = 'America';

The results would look like this:

  name
--------
 Kirkis
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.