How to Extract Specific Columns as a JSON Array of Objects in PostgreSQL?

Extract specific columns from a PostgreSQL table as an array of JSON objects

Question:

You have a table with multiple columns and want to return an array of objects for each row, with two columns grouped according to another column. However, you'll run into the problem that the result contains an extra key in each object. The desired output is an array of objects with only two columns, grouped by a third column.

PostgreSQL 10 solution:

For PostgreSQL 10 and above, you can use the - operator to remove a single key (or array of keys in PostgreSQL 10) from a jsonb object before aggregating.

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

PostgreSQL 9.4 solution:

In PostgreSQL 9.4 and above, you can use jsonb_build_object() or json_build_object() to create a JSON object from alternating keys and values.

<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 solution:

For PostgreSQL 9.3 and above, you can use to_jsonb() and ROW expressions to create JSON objects. You can also use subqueries instead of ROW expressions.

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

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

The above solution will return the desired result: an array of objects with only two columns, grouped by the third column.

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