In Neon, you can provision Postgres databases in < 1s. Don't believe us? Check out this demo
Postgres guides/Functions/JSON functions

Postgres array_to_json() function

Converts an SQL array to a JSON array

You can use the array_to_json function to convert a Postgres array into its JSON representation, transforming an array of values into a JSON array. This helps facilitate integration with web services, APIs, and web frameworks that heavily rely on JSON.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Sign Up

Function signature

array_to_json(anyarray [, pretty_bool])

Line feeds will be added between dimension 1 elements if pretty_bool is true.

array_to_json example

Let's consider a scenario where an e-commerce platform stores customer preferences as an array of string values in a customers table.

customers

CREATE TABLE customers (
 id SERIAL PRIMARY KEY,
 name TEXT NOT NULL,
 preferences TEXT[]
);

INSERT INTO customers (name, preferences)
VALUES ('John Doe', '{clothing, electronics}');

INSERT INTO customers (name, preferences)
VALUES ('Jane Doe', '{books, music, travel}');
id  |   name   |      preferences
----+----------+------------------------
  1 | John Doe | {clothing,electronics}
  2 | Jane Doe | {books,music,travel}

You can use the array_to_json function as shown to transform the array of string values into a JSON array:

SELECT id, name, array_to_json(preferences) AS json_preferences
FROM customers;

This query returns the following result:

id  |   name   |      json_preferences
----+----------+----------------------------
  1 | John Doe | ["clothing","electronics"]
  2 | Jane Doe | ["books","music","travel"]

Advanced examples

Let's now take a look at a few advanced examples.

Use array_to_json with array_agg

Imagine you have an e-commerce website with user's shopping cart items, as shown in the following cart_items table:

cart_items

CREATE TABLE cart_items (
 id SERIAL PRIMARY KEY,
 user_id INTEGER NOT NULL,
 product_id INTEGER NOT NULL,
 quantity INTEGER NOT NULL
);

INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (1, 123, 1), (1, 456, 2), (1, 789, 3);


INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (2, 123, 2), (2, 456, 3), (2, 789, 4);
id  | user_id | product_id | quantity
----+---------+------------+----------
  1 |       1 |        123 |        1
  2 |       1 |        456 |        2
  3 |       1 |        789 |        3
  4 |       2 |        123 |        2
  5 |       2 |        456 |        3
  6 |       2 |        789 |        4

You can utilize array_to_json to create a clean and efficient JSON representation of the cart contents for a specific user.

In the example below, the row_to_json function converts each row of the result set into a JSON object.

The array_agg function is an aggregate function that aggregates multiple values into an array. It is used here to aggregate the JSON objects created by row_to_json into a JSON array.

SELECT array_to_json(
 array_agg(row_to_json(t))
) AS items
FROM (
     SELECT product_id, quantity FROM cart_items WHERE user_id = 1
   ) t;

This query returns the following result:

items
---------------------------------------------------------------------------------------------------
 [{"product_id":123,"quantity":1},{"product_id":456,"quantity":2},{"product_id":789,"quantity":3}]

And this is the resulting JSON structure:

[
  {
    "product_id": 123,
    "quantity": 1
  },
  {
    "product_id": 456,
    "quantity": 2
  },
  {
    "product_id": 789,
    "quantity": 3
  }
]

Handling NULL in array_to_json

The array_to_json function handles NULL values gracefully, representing them as JSON null within the resulting array.

Let's consider a survey_responses table representing a survey where each participant can provide multiple responses to different questions. Some participants may not answer all questions, leading to NULL values in the data.

CREATE TABLE survey_responses (
   participant_id SERIAL PRIMARY KEY,
   participant_name VARCHAR(50),
   responses VARCHAR(50)[]
);

-- Insert sample data with NULL responses
INSERT INTO survey_responses (participant_name, responses) VALUES
   ('Participant A', ARRAY['Yes', 'No', 'Maybe']),
   ('Participant B', ARRAY['Yes', NULL, 'No']),
   ('Participant C', ARRAY[NULL, 'No', 'Yes']),
   ('Participant D', ARRAY['Yes', 'No', NULL]);
participant_id  | participant_name |   responses
----------------+------------------+----------------
              1 | Participant A    | {Yes,No,Maybe}
              2 | Participant B    | {Yes,NULL,No}
              3 | Participant C    | {NULL,No,Yes}
              4 | Participant D    | {Yes,No,NULL}

The output correctly represents NULL values as JSON null in the responses_json array.

SELECT
   participant_id,
   participant_name,
   array_to_json(COALESCE(responses, ARRAY[]::VARCHAR[])) AS responses_json
FROM
   survey_responses;

This query returns the following result:

participant_id | participant_name | responses_json
---------------+-----------------=+---------------------
             1 | Participant A    | ["Yes","No","Maybe"]
             2 | Participant B    | ["Yes",null,"No"]
             3 | Participant C    | [null,"No","Yes"]
             4 | Participant D    | ["Yes","No",null]

Additional considerations

This section outlines additional considerations when using the array_to_json function.

JSON functions

In scenarios where more control over the JSON structure is required, consider using the json_build_array and json_build_object functions. These functions allow for a more fine-grained construction of JSON objects and arrays.

Formatting array_to_json output with pretty_bool

The pretty_bool parameter, when set to true, instructs array_to_json to format the output with indentation and line breaks for improved readability.

Execute the earlier query with pretty_bool as true:

SELECT array_to_json(
 array_agg(row_to_json(t)), true
) AS items
FROM (
     select product_id, quantity from cart_items WHERE user_id = 1
   ) t;

This query returns the following result:

items
-----------------------------------
 [{"product_id":123,"quantity":1},+
  {"product_id":456,"quantity":2},+
  {"product_id":789,"quantity":3}]

note

The output displayed in psql might be truncated or wrap long lines for visual clarity.

Resources

Last updated on

Edit this page
Was this page helpful?