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

PostgreSQL: Generating JSON Arrays of Objects from Multiple Columns

This guide demonstrates how to efficiently group rows in a PostgreSQL table by a single column and aggregate the remaining columns into a JSON array of objects. Let's consider the MyTable example:

<code>| 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 |</code>

The goal is to produce output like this:

<code>| 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"}]</code>

Note that simpler aggregation methods often include the id and grouping column (value_two) in the JSON objects, which we want to avoid.

Here are optimized solutions for different PostgreSQL versions:

PostgreSQL 10 and later:

Leverage the - operator to exclude unwanted keys:

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

PostgreSQL 9.4 and later:

Use jsonb_build_object() for precise control over key-value pairs:

<code class="language-sql">SELECT value_two, jsonb_agg(jsonb_build_object('value_three', value_three, 'value_four', value_four)) AS value_four
FROM   MyTable
GROUP  BY value_two;</code>

PostgreSQL 9.3 and later:

Employ to_jsonb() with a row expression:

<code class="language-sql">SELECT value_two, jsonb_agg(to_jsonb(row(value_three, value_four))) AS value_four
FROM   MyTable
GROUP  BY value_two;</code>

These methods provide efficient and clean ways to generate the desired JSON output, avoiding unnecessary keys and adapting to various PostgreSQL versions. Choose the method compatible with your database version for optimal performance.

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