> This page location: Postgres guides > Functions > JSON functions > array_to_json > Full Neon documentation index: https://neon.com/docs/llms.txt > IMPORTANT: If this page contains inaccurate or outdated information, report it: POST to https://neon.com/api/docs-feedback with {"feedback": "describe the issue", "path": "/docs/functions/array_to_json"} # 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`. ## Function signature ```sql 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** ```sql 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: ```sql 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** ```sql 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. ```sql 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: ```shell items --------------------------------------------------------------------------------------------------- [{"product_id":123,"quantity":1},{"product_id":456,"quantity":2},{"product_id":789,"quantity":3}] ``` And this is the resulting `JSON` structure: ```json [ { "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. ```sql 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. ```sql 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`: ```sql 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 - [PostgreSQL documentation: JSON Functions and Operators](https://www.postgresql.org/docs/current/functions-json.html) - [PostgreSQL documentation: JSON Types](https://www.postgresql.org/docs/current/datatype-json.html) ß --- ## Related docs (JSON functions) - [json](https://neon.com/docs/functions/json) - [json_agg](https://neon.com/docs/functions/json_agg) - [json_array_elements](https://neon.com/docs/functions/json_array_elements) - [json_build_object](https://neon.com/docs/functions/json_build_object) - [json_each](https://neon.com/docs/functions/json_each) - [json_exists](https://neon.com/docs/functions/json_exists) - [json_extract_path](https://neon.com/docs/functions/json_extract_path) - [json_extract_path_text](https://neon.com/docs/functions/json_extract_path_text) - [json_object](https://neon.com/docs/functions/json_object) - [json_populate_record](https://neon.com/docs/functions/json_populate_record) - [json_query](https://neon.com/docs/functions/json_query) - [json_scalar](https://neon.com/docs/functions/json_scalar) - [json_serialize](https://neon.com/docs/functions/json_serialize) - [json_table](https://neon.com/docs/functions/json_table) - [json_to_record](https://neon.com/docs/functions/json_to_record) - [json_value](https://neon.com/docs/functions/json_value) - [jsonb_array_elements](https://neon.com/docs/functions/jsonb_array_elements) - [jsonb_each](https://neon.com/docs/functions/jsonb_each) - [jsonb_extract_path](https://neon.com/docs/functions/jsonb_extract_path) - [jsonb_extract_path_text](https://neon.com/docs/functions/jsonb_extract_path_text) - [jsonb_object](https://neon.com/docs/functions/jsonb_object) - [jsonb_populate_record](https://neon.com/docs/functions/jsonb_populate_record) - [jsonb_to_record](https://neon.com/docs/functions/jsonb_to_record)