How to Return Multiple Columns from the Same Row as a JSON Array of Objects in PostgreSQL?

Returning Multiple Columns as a JSON Array of Objects in PostgreSQL

PostgreSQL offers several approaches to query and format data as a JSON array of objects, grouping results by a specific column. Here's a breakdown of methods for different PostgreSQL versions.

PostgreSQL 10 and later

For removing specific keys from the aggregated JSON object, employ the - operator before aggregation. Explicit casting to text[] is necessary to resolve ambiguity with the overloaded function:

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

PostgreSQL 9.4 and later

Utilize jsonb_build_object() to construct a JSON object from key-value pairs:

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

PostgreSQL 9.3 and later

Employ to_jsonb() with a ROW expression or subquery to generate the array of objects:

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

Alternatively:

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

These methods provide efficient ways to retrieve and structure data, grouping values and creating JSON arrays of objects representing multiple columns from a single row.

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