How to Return Multiple Columns as a JSON Array of Objects in PostgreSQL?

Generating JSON Arrays of Objects from Multiple PostgreSQL Columns

This guide demonstrates how to retrieve data from a PostgreSQL table as a JSON array of objects, grouping by a specific column. The examples use the MyTable table as a reference:

id	value_two	value_three	value_four
1	a	A	AA
2	a	A2	AA2
3	b	A3	AA3
4	a	A4	AA4
5	b	A5	AA5

The objective is to create a JSON array where each object contains value_three and value_four, grouped by value_two. The desired output format is:

value_two	value_four
a	[{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
b	[{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

PostgreSQL Solutions (Version Specific)

The optimal solution depends on your PostgreSQL version:

PostgreSQL 10 and above:

Leveraging the - operator for key exclusion simplifies the query:

<code class="language-sql">SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM tbl t
GROUP BY val2;</code>

This efficiently removes unnecessary columns (id and val2) before aggregation.

PostgreSQL 9.4 and above:

jsonb_build_object() provides a more controlled approach:

<code class="language-sql">SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl 
GROUP BY val2;</code>

This explicitly specifies the key-value pairs for each JSON object.

PostgreSQL 9.3 and above:

Several options exist, each with trade-offs:

1. to_jsonb((val3, val4)): This loses column names in the resulting JSON.

2. (val3, val4)::foo: This preserves column names by casting to a custom row type (foo). Requires creating the type CREATE TYPE foo AS (val3 text, val4 text); beforehand.

3. Subquery approach: A more verbose method using a subquery but avoids type casting.

<code class="language-sql">SELECT val2, jsonb_agg(to_jsonb((val3, val4))) AS js_34  -- Loses column names
FROM tbl
GROUP BY val2;

-- Requires: CREATE TYPE foo AS (val3 text, val4 text);
SELECT val2, jsonb_agg((val3, val4)::foo) AS js_34  -- Preserves column names
FROM tbl
GROUP BY val2;

SELECT val2, jsonb_agg(to_jsonb((SELECT t FROM (SELECT val3, val4) t))) AS js_34 -- Subquery approach
FROM tbl
GROUP BY val2;</code>

Choose the method most suitable for your PostgreSQL version and desired level of conciseness. For versions 10 and above, the - operator offers the cleanest and most efficient solution.

The above is the detailed content of How to Return Multiple Columns as a JSON Array of Objects in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!