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:
1 | 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:
1 | 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
Stringdatatype for plain text characters:
1 | {"name": "Michael"} |
- the
Booleandatatype for true or false values:
1 | {"correct": true} |
- the
Numberdatatype for integers and whole numbers:
1 | {"Age": 50} |
- an
Array, or ordered list, can be nested inside of a JSON key to store multiple values:
1 | {"Department": ["MIS","HR","QC"]} |
- the
Objectdatatype is used for an unordered list of key-value pairs:
1 | {"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:
1 | 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:
1 2 3 4 | 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:
1 2 | 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:
1 | SELECT about FROM demo; |
This SQL statement will return the JSON file as shown below:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 | 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:
1 2 | SELECT about ->> 'name' AS name FROM demo WHERE about -> 'info' ->> 'address' = 'America'; |
The results would look like this:
1 2 3 4 | 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