PostgreSQL JSON Data Type

This article describes how to use the JSON type in PostgreSQL and show you some useful functions for working with JSON data.

JSON is a universal, lightweight data transfer format that can represent complex, structured data, and it is also easy to read and write.

PostgreSQL supports the native JSON data type since version 9.2 and provides many functions and operators for manipulating JSON data.

Introduction to JSON

JSON defines the following types of values:

  • object
  • array
  • string
  • number
  • true
  • false
  • null

A JSON object is a combination of key-value pairs. The key is a text of type string, and the value can be any of the above types.

A JSON array is a combination of values. The value in it can be any of the above types.

A JSON value can be any of the above types.

For example, here are some plain JSON values:

"a"
"abc"
"Hello World"
1
1.23
123.456
true
false
null

The following is an example of a JSON object:

{
  "x": "Hollo World",
  "y": { "a": 1, "b": true },
  "z": [1, "a", true, false, null],
  "o": null
}

The following is an example of a JSON array:

[1, "a", { "a": 1, "b": true }, [1, "a", true, false, null], null]

Typically, we use JSON objects and JSON arrays when transferring data.

Introduction to PostgreSQL JSON

PostgreSQL supports two JSON types: JSON and JSONB. Their processing efficiency is different. JSON save input data as text, keep spaces in original data, and repeat json_object_keys. Then JSONB save the input data in binary, it will remove unnecessary spaces and duplicate keys.

Texts to JSON values

To convert a string of JSON text to a value of type JSON, you can use the following two methods:

  • Use the JSON() constructor to convert JSON text to a value of type JSON:

    json('[1, 2, 3]')

  • Use typecasting to convert JSON text to JSON-typed value:

    '{"a": [1, 2, 3]}'::json

JSON operators

PostgreSQL provides several operators for JSON values, as follows:

  • ->: Get an element of a JSON array or a field in a JSON object, and return the value of JSON type.
  • ->>: Get the element of the JSON array or the field in the JSON object, and return the value as a text.
  • #>: Get the value of the specified path, and return the value of JSON type.
  • #>>: Get the value of the specified path, and return the value as a text.

PostgreSQL JSON Examples

Create a new table named login_logs:

CREATE TABLE login_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  detail JSON NOT NULL
);

The login_logs table consists of two columns:

  1. The id column is the primary key column that identifies each row, and it is an identity column.
  2. The detail is a JSON column.

Insert JSON data

To insert data into a JSON column, you must ensure that the data is in valid JSON format. The following INSERT statement inserts several new rows into the login_logs table.

INSERT INTO login_logs (detail)
VALUES
  ('{ "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}'),
  ('{ "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}'),
  ('{ "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}');

Here, the JSON values ​​in the detail columns are JSON objects with two members:

  • The name is the name of the logged user.
  • The address is the address of the logged user, and it is also a JSON object with two members:
    • The ip is the IP address where the logged user was from.
    • The country is the country where the logged user is located.

Query JSON data

To query JSON data, use this SELECT statement :

SELECT * FROM login_logs;

 id |                               detail
----+---------------------------------------------------------------------
  1 | { "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}
  2 | { "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}
  3 | { "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}
(3 rows)

If you want to display fields inside a JSON object, use the -> or ->> operator. The -> operator returned value is of JSON type and the ->> operator returned value is of text type.

The following query uses two operators -> and ->> to get the names of all logged in users in JSON:

SELECT
  detail -> 'name' AS name_json,
  detail ->> 'name' AS name_text
FROM
  login_logs;

 name_json | name_text
-----------+-----------
 "Tom"     | Tom
 "Tim"     | Tim
 "Jim"     | Jim
(3 rows)

To get all countries and IPs, use the following statement:

SELECT
  detail -> 'address' ->> 'country' as country,
  detail -> 'address' ->> 'ip' as ip
FROM login_logs;

 country |      ip
---------+--------------
 US      | 192.168.1.23
 US      | 192.168.1.24
 UK      | 192.168.1.25
(3 rows)

First, detail -> 'address' return the address as a JSON object.

Then detail -> 'address' ->> 'country' return all countries as text.

Using JSON operators in WHERE conditions

You can use JSON operators in the WHERE clause to filter the returned rows.

For example, to find all logged in users from US, use the following query:

SELECT
  detail ->> 'name' AS name,
  detail -> 'address' ->> 'country' AS country
FROM
  login_logs
WHERE
  detail -> 'address' ->> 'country' = 'US';

 name | country
------+---------
 Tom  | US
 Tim  | US
(2 rows)

PostgreSQL JSON functions

PostgreSQL provides many JSON-related functions, as follows:

  • array_to_json(): Convert an SQL array to JSON array and return.
  • json_array_elements(): Expands the top-level JSON array into a collection of JSON values.
  • json_array_length(): Returns the length of a specified JSON array.
  • json_build_array(): Creates a possibly heterogeneous JSON array from a mutable parameter list.
  • json_each(): Expands a specified JSON object into a set of key-value pairs.
  • json_extract_path(): Extract the value of the specified path from a specified JSON value.
  • json_object_keys(): Returns a set of top-level keys in the specified JSON object.
  • json_object(): Builds a JSON object from a text array, or builds a JSON object from two arrays as keys and values, respectively.
  • json_to_record(): Expands the specified top-level JSON object into a row with the conforming type defined in the AS clause.
  • json_to_recordset(): Expands the specified top-level JSON array (elements are objects) into a collection of rows with the conforming type defined in the AS clause.
  • json_typeof(): Returns the type of the specified JSON value as a string.
  • row_to_json(): Convert a SQL composite type value to JSON object and return it.
  • to_json(): Convert an SQL value to JSON value and return it.

Conclusion

This article discussed the PostgreSQL JSON data type, as well as some commonly used JSON operators and functions.