JSON aggregation with JSON_AGG in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PostgreSQL function JSON_AGG do?

JSON_AGG collects multiple rows into a single JSON array. It helps group data as JSON objects inside one array.

beginner

How do you use JSON_AGG to aggregate rows from a table?

You select the columns you want as JSON objects and wrap them inside JSON_AGG. For example: SELECT JSON_AGG(row_to_json(t)) FROM (SELECT * FROM table_name) t;

intermediate

Can JSON_AGG be combined with GROUP BY?

Yes. You can group rows by a column and aggregate related rows as JSON arrays for each group.

intermediate

What is the difference between JSON_AGG and ARRAY_AGG?

JSON_AGG returns a JSON array, which can contain JSON objects. ARRAY_AGG returns a SQL array of values, not JSON formatted.

beginner

Why use row_to_json inside JSON_AGG?

row_to_json converts a table row into a JSON object. Using it inside JSON_AGG creates an array of JSON objects instead of simple values.

What type of data does JSON_AGG return?

A A JSON array

B A SQL array

C A JSON object

D A text string

JSON_AGG returns a JSON array containing aggregated JSON values.

Which function is commonly used inside JSON_AGG to convert rows to JSON objects?

A JSON_BUILD_OBJECT

B TO_JSON

C ROW_TO_JSON

D JSON_OBJECT_AGG

ROW_TO_JSON converts a whole row into a JSON object, perfect for use inside JSON_AGG.

Can JSON_AGG be used with GROUP BY to create grouped JSON arrays?

A Only with <code>ORDER BY</code>

B Yes

C No

D Only with <code>HAVING</code>

JSON_AGG works well with GROUP BY to aggregate rows per group into JSON arrays.

What does this query return?<br>SELECT JSON_AGG(name) FROM users;

A A JSON array of user names

B A JSON object of user names

C A SQL array of user names

D A single user name

This query returns a JSON array containing all user names from the users table.

Which is true about JSON_AGG compared to ARRAY_AGG?

A <code>JSON_AGG</code> returns SQL arrays, <code>ARRAY_AGG</code> returns JSON arrays

B Both return SQL arrays

C Both return JSON arrays

D <code>JSON_AGG</code> returns JSON arrays, <code>ARRAY_AGG</code> returns SQL arrays

JSON_AGG returns JSON formatted arrays, while ARRAY_AGG returns SQL native arrays.

Explain how to use JSON_AGG to create a JSON array of objects from a table.

Think about how to turn each row into JSON and then collect them.

Describe a scenario where combining GROUP BY with JSON_AGG is useful.

Imagine grouping orders by customer and returning JSON arrays of orders.