PostgreSQL JSON

Summary: In this tutorial, you’ll learn how to use the PostgreSQL JSON data types to store JSON data in the databases.

JSON Overview #

JSON, or JavaScript Object Notation, is a lightweight data-interchange format that is simple for developers to work with and easy for computers to manage.

Here are the key features of JSON:

• Human-readable: JSON uses a simple text format.
• Lightweight: JSON has a minimal syntax and structure.
• Structured data: JSON uses arrays and objects to represent data.
• Language-dependent: JSON works seamlessly with many programming languages.

The following are typical scenarios where you find JSON useful:

• RESTful APIs
• Configuration files (.json format)
• Data storage

JSON uses two structures to store data:

• Objects: A collection of key-value pairs enclosed in curly braces ({}).
• Arrays: An order list of values enclosed in square brackets ([]).

Objects and arrays can be nested and mixed.

Objects #

A JSON object is a list of key-value pairs enclosed in curly braces. Each pair consists of:

• A key: appears within double quotes (").
• A value: is any valid JSON value such as a number, a string, an object, an array, etc.
• A colon (:) separates the key and value.

For example, the following shows a JSON object:

{"product_name": "iPhone 16", "price": 1299.99 }Code language: JSON / JSON with Comments (json)

The JSON object has two keys, product_name, and price, with corresponding values.

Arrays #

A JSON array is an ordered list of values enclosed in square brackets ([]). The values can have different types.

For example, the following is a JSON array that stores the product features:

["Camera", "Face Recognition", "AI"]Code language: JSON / JSON with Comments (json)

JSON Data Types #

Besides object and array, JSON supports the following simple data types:

• string
• number
• boolean
• null

PostgreSQL JSON data types #

PostgreSQL has two built-in data types for storing JSON:

• JSON: stores an exact copy of JSON data.
• JSONB: stores the JSON data in binary format.

The following table shows the key differences between JSON and JSONB types in PostgreSQL:

PostgreSQL recommends using the JSONB data type for smaller storage and better query efficiency. PostgreSQL offers the JSONPATH data type to make querying JSON data more efficient.

PostgreSQL JSONB example #

First, create a new table called phones to store phone data:

CREATE TABLE phones (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  properties JSONB
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The data type of the properties column is JSONB.

Second, insert three rows with JSON data into the phones table:

INSERT INTO phones(name, properties ) VALUES
('iPhone 16 Pro', 
 '{
    "display": "6.1-inch OLED",
    "features": ["Face ID", "ProMotion 120Hz", "MagSafe", "iOS 18"]
 }'),

('Galaxy S23 Ultra', 
 '{
    "display": "6.8-inch AMOLED",
    "features": ["S Pen", "120Hz Display", "One UI 5", "Wireless Charging"]
 }'),

('Pixel 7 Pro', 
 '{
    "display": "6.7-inch OLED",
    "features": ["Magic Eraser", "Pure Android Experience", "Face Unlock", "5G Connectivity"]
 }');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, retrieve data from the phones table:

SELECT
  id,
  name,
  properties
FROM
  phones;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |       name       |                                                       properties

----+------------------+-------------------------------------------------------------------------------------------------------------------------
  1 | iPhone 16 Pro    | {"display": "6.1-inch OLED", "features": ["Face ID", "ProMotion 120Hz", "MagSafe", "iOS 18"]}
  2 | Galaxy S23 Ultra | {"display": "6.8-inch AMOLED", "features": ["S Pen", "120Hz Display", "One UI 5", "Wireless Charging"]}
  3 | Pixel 7 Pro      | {"display": "6.7-inch OLED", "features": ["Magic Eraser", "Pure Android Experience", "Face Unlock", "5G Connectivity"]}Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, retrieve the phones with the display key:

SELECT
  id,
  name,
  properties -> 'display' AS display
FROM
  phones;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |       name       |      display
----+------------------+-------------------
  1 | iPhone 16 Pro    | "6.1-inch OLED"
  2 | Galaxy S23 Ultra | "6.8-inch AMOLED"
  3 | Pixel 7 Pro      | "6.7-inch OLED"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we use the operator -> to extract a JSON value by a key. A JSON value is surrounded by double quotes.

To return a JSON value as text, you use the operator ->>. For example:

SELECT
  id,
  name,
  properties ->> 'display' AS display
FROM
  phones;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |       name       |     display
----+------------------+-----------------
  1 | iPhone 16 Pro    | 6.1-inch OLED
  2 | Galaxy S23 Ultra | 6.8-inch AMOLED
  3 | Pixel 7 Pro      | 6.7-inch OLEDCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, retrieve the main feature of each phone:

SELECT
  id,
  name,
  properties -> 'features' ->> 0 AS main_feature
FROM
  phones;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |       name       | main_feature
----+------------------+--------------
  1 | iPhone 16 Pro    | Face ID
  2 | Galaxy S23 Ultra | S Pen
  3 | Pixel 7 Pro      | Magic EraserCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• The properties->'features' returns a JSON array.
• The properties->'features' ->> 0 returns the first element of the JSON array as text.

Summary #

• Use JSONB data type to store data.
• Use the operator -> to extract a JSON value from a JSON object by a key.
• Use the operator ->> to extract a JSON value as text.