PostgreSQL – array_to_json Function

The array_to_json function in PostgreSQL is a powerful tool that allows you to convert an array of values into its corresponding JSON representation. This function is part of PostgreSQL’s rich set of functions for working with JSON data, providing a seamless way to transform arrays into a format that’s compatible with JSON.

Basic Syntax

The basic syntax of the array_to_json function is as follows:

array_to_json(array_expression)

where array_expression is the expression representing the array you want to convert to JSON.

Examples

Let’s delve into some examples to better understand how to use the array_to_json function.

Converting Integer Array to JSON Array:

SELECT array_to_json(ARRAY[1, 2, 3, 4, 5]) AS json_array; 

In this example, the integer array [1, 2, 3, 4, 5] is converted into a JSON array. The query result will have a single column named json_array containing the JSON representation of the array.

Converting String Array to JSON Array:

SELECT array_to_json(ARRAY['apple', 'banana', 'cherry']) AS json_array; 

This query converts a string array ['apple', 'banana', 'cherry'] into a JSON array. The resulting JSON array will contain the strings 'apple', 'banana', and 'cherry'.

Working with Nested Arrays:

You can also use array_to_json with nested arrays, creating complex JSON structures.

SELECT array_to_json(ARRAY[ARRAY[1, 2], ARRAY[3, 4, 5]]) AS nested_json_array; 

In this example, a nested array of arrays is converted into a JSON array containing arrays as its elements.

While the array_to_json function is efficient for converting arrays to JSON, keep in mind that it primarily focuses on one-dimensional arrays. If you’re dealing with complex nested arrays or need to perform more advanced JSON operations, you should consider exploring other JSON functions and operators available in PostgreSQL.

The array_to_json function in PostgreSQL provides a convenient way to convert arrays into their JSON representation. Whether you’re working with simple arrays or more complex nested arrays, this function streamlines the process of preparing your data for JSON consumption.