In this tutorial, we will learn about the JSON type in PostgreSQL, how it works and how to use it in real life using multiple examples. So, let’s get started!
Also Read: PostgreSQL – Data Types
Introduction to JSON Type in PostgreSQL
JSON term is pretty common in the programming world as it is available in almost all programming languages. It stands for Javascript Object Notation and is an open standard format that contains key-value pairs.
You may be wondering why we need the JSON when we can store the key value in plain text format. Let me tell you, JSON is mainly used to transfer data between the server and web application. Apart from that, it is in a human-readable format, unlike XML.
In PostgreSQL, you can have a JSON-type column in your table to store the JSON string. Apart from that, there are a bunch of functions and operators available that you can use to manipulate and play around with the JSON data.
Note that, the JSON string can have nested key-value pairs. That means, there can be key-value pairs in place of a value of a key.
For example:
{"name" : { "first" : "John" , "last" : "Smith" } , "age" : 10 , "city" : "NY" }Code language: JSON / JSON with Comments (json)In the above JSON string, the name key has a value as another JSON string. This is valid in JSON string.
Now let’s see JSON in PostgreSQL.
Only postgresql version greater than 9.2 comes with the native support of JSON datatype.
Create a Table with JSON Type Column
Let’s create a table having a JSON data type. Here, we will create a table to store employees’ details having the id and data as columns.
CREATE TABLE emps (
id SERIAL PRIMARY KEY,
data JSON
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the data column will store the details of emps such as name, age and address in the form of JSON string. Let’s now insert some records into the table.
INSERT INTO emps (data)
VALUES ('{"name": "John", "age": 30, "address": {"city": "New York", "state": "NY"}}'),
('{"name": "Sarah", "age": 25, "address": {"city": "Los Angeles", "state": "CA"}, "email": "sarah@example.com"}'),
('{"name": "Tom", "age": 40, "address": {"city": "Chicago", "state": "IL"}, "email": "tom@example.com"}');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Let’s see how the data looks in the table.
SELECT * FROM emps;
As you can see, the JSON type stores an exact replica of the input string in the table.
Note that, each key and value inside the JSON string must be within the double quotes except the numbers and the whole JSON string must be within the single quote at the time of inserting into the table.
Access JSON Data from Table
It is straightforward to access the data of the JSON-type column. To get all the data, you have to just mention the column name and that’s it.
SELECT id, data FROM emps;
As you can see, the output consists of the JSON string in the exact form that we inserted.
To access the value of a particular key from the JSON string, PostgreSQL provides us with two operators = ->, ->>
- The -> operator returns the JSON object field of the specified key
- The ->> operator returns the JSON object field of the specified key, in text format.
Let’s see these in action.
SELECT id, data->'name' as Name FROM emps;
SELECT id, data->>'name' as Name FROM emps;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
You can see in the output, the first query returned names with quotes that are in the JSON format, whereas the second query returned names without quotes that are in plain text format.
Now let’s say you have to access the address info of employees. However, you want to display only the city from the address. In our case, the value associated with to address key is itself JSON data that further contains key-value pairs.
In this case, you just have to use multiple -> operators. Let’s see.
SELECT id, data->'address'->'city' as city FROM emps;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In the above query, we are first fetching the value of the address key which will return a json string. Now, the returned value has keys inside. So again we use the -> operator to access the value of a particular key.
However, this query will return a city in JSON format. We need to display it in text format. Therefore, after the address key, we will use the ->> operator.
So, the new query will look like this-
SELECT id, data->'address'->>'city' as city FROM emps;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Let’s execute both queries and see the output difference.
As you can see here, the first query returns the names in JSON format, whereas the second query returns the data in text format.
There are a bunch of JSON functions available in PostgreSQL that you can use in your queries. Here you can find the complete list of JSON functions.
JSON in WHERE Clause
Now let’s see how you can use the JSON data in the where clause.
Here, we will display the employees having ages less than or equal to 30. Note that, the age is present in the JSON string and not in a separate column.
SELECT id, data->>'name' as name, data->>'age' as age
FROM emps WHERE
CAST(data->>'age' AS INTEGER) >=30;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, we have used the CAST function to convert the age to the integer format so that we can use the comparison operator with it.
Let’s see the output.
As you can see, the output shows the correct result.
Conclusion
The JSON type is one of the most accessible types you can learn in PostgreSQL and can be useful in many scenarios. For example, let’s say you have a small JSON string that you need to store somewhere. In most cases, developers choose two different stores, such as PostgreSQL and MongoDB. But now, you can skip the mongoDB part and directly store that JSON string in the PostgreSQL database. Apart from this, there are a bunch of real-time scenarios where you will need the JSON type. I hope you found this tutorial helpful.
References
PostgreSQL official documentation on JSON type.
PostgreSQL official documentation on JSON function.