PostgreSQL: JSON Data Type

PostgreSQL supports JSON type (Java Script Object Notation). JSON is an open standard format which contains key-value pairs and it is human-readable text.

PostgreSQL supports JSON data type since the 9.2 version. It also provides many functions and operators for processing JSON data.

The following table includes the JSON type column.

CREATE TABLE Person_json (
	id SERIAL NOT NULL PRIMARY KEY,
	info JSON NOT NULL
);

The above Person_json table has two columns, the id column is a primary key column that identifies the person. The info column has information about a person in the format of JSON. Note that we defined the info column as NOT NULL, so it must be populated with a valid JSON value.

INSERT JSON data

Now to insert data to the Person_Json table, the data of the info column must be in valid JSON format.

INSERT INTO person_json(info)
VALUES
	('{"name":"Annie Smith","details":{"gender": "F","age": 23}}'),
	('{"name":"May Kaasman","details":{"gender": "M","age": 17}}'),
	('{"name":"Charlton Duran","details": {"gender": "M","age": 37}}'),
	('{"name":"Susan Klassen","details": {"age": 45}}');

The above shows, it inserted 4 rows to the Person_json table. You can see we inserted nested Json objects to table column.

A point to note here is that the fourth row JSON is ('{"name": "Susan Klassen", "details": {"age": 45}}') where details JSON does not have a gender column defined. which is there for the other three rows. The INSERT statement does not throw any error. The INSERT statement only validates data to be in JSON format.

As PostgreSQL validates data in JSON format for JSON-defined columns during insertion, If you try to insert some text value that is in non-JSON format, you will get an error like below.

INSERT INTO Person_json(info)
VALUES
	('Non-Json text value');

Querying JSON data

Tfollowing select query gets the data from the Person_json table.

PostgreSQL provides two native operators -> and ->> to help you query JSON data.

The operator -> returns the JSON object field by key.

SELECT info -> 'name' AS name FROM person_json;

The operator ->> returns the JSON object field by text.

SELECT info ->> 'name' AS name FROM person_json;

As the -> operator returns JSON objects, you can further chain it with the ->> operator to retrieve specific detail of the nested JSON object. Here we are querying gender and age information from nested JSON objects as below

SELECT info -> 'name' AS name, 
	info -> 'details' ->> 'gender' AS gender,
	info -> 'details' ->> 'age' AS age
FROM person_json;

Use JSON Operators in WHERE clause

The JSON operators -> and ->> can be used in the WHERE clause to filter out data. For example, let's find out all Female persons from table Person_Json table.

SELECT info -> 'name' as NAME
FROM person_json
WHERE info -> 'details' ->> 'gender' = 'F';

The following query finds out all persons above 25 years of age.

SELECT info -> 'name' as NAME
FROM person_json
WHERE CAST(info -> 'details' ->> 'age' AS NUMERIC) > 25;

PostgreSQL JSON Processing Functions

PostgreSQL supports some in-built functions for processing JSON column data.

Json_each()

The json_each() function allows to extract outermost JSON object as a set of key-value pairs.

SELECT json_each (info)
FROM person_json;

Use the json_each_text() function to return same result as text.

Json_type_of()

The json_type_of() function returns the data type of the outermost JSON value as a string. The values can be string, number, Boolean, null, array, or object.

SELECT json_typeof (info -> 'details' -> 'age')
FROM person_json;

Json_object_keys()

Use the json_object_keys function to get a set of keys in the outermost JSON object. The following query returns all keys of nested details object from the info JSON column.

SELECT json_object_keys (info -> 'details')
FROM person_json;