The last lesson built a JSON array from a group's values. JSON_OBJECTAGG builds a JSON object instead, one column supplying each key, another supplying each key's value, the key-value equivalent of the same idea.
Learning Objectives
- Build a JSON object from a group, one column as the key and another as the value, under each database's real function.
- Know that SQL Server has the identical gap here as it did for JSON_ARRAYAGG, for the identical reason.
- Write Oracle's distinctly different KEY ... VALUE ... argument syntax correctly.
Group Rows
One group per department
JSON Object Aggregate
Key column, value column, per row
One JSON Object
{ key: value, key: value, ... }
The Real Function, by Database
| Database | Building a JSON Object From a Group |
|---|---|
| MySQL (5.7.22+) / MariaDB (10.5+) | JSON_OBJECTAGG(key_col, value_col) |
| PostgreSQL | json_object_agg(key_col, value_col) / jsonb_object_agg(key_col, value_col), PostgreSQL's own original functions, or the SQL-standard JSON_OBJECTAGG(KEY key_col VALUE value_col) added in PostgreSQL 16+ |
| SQLite | json_group_object(name_col, value_col), same purpose, a different real name |
| Oracle | JSON_OBJECTAGG(KEY key_col VALUE value_col), the KEY ... VALUE ... keywords are mandatory, not just key_col, value_col |
| SQL Server | No built-in aggregate yet, same as JSON_ARRAYAGG, in preview for SQL Server 2025 and on Azure SQL Database. The on-premises workaround today is the same FOR JSON PATH subquery pattern. |
Try It Yourself
Build a single JSON object mapping each department's id to its name, on PostgreSQL.
SELECT jsonb_object_agg(id, name) AS departments_by_id FROM departments;
Frequently Asked Questions
What happens if the key column has duplicate values?
Behavior differs by database, some keep the last value seen for a repeated key, Oracle's JSON_OBJECTAGG specifically offers a WITH UNIQUE KEYS clause to raise an error instead if that matters. Check the specific database's documented behavior before relying on either outcome.
Does Oracle's KEY ... VALUE ... syntax apply to JSON_ARRAYAGG too?
No, that syntax is specific to JSON_OBJECTAGG, since an object aggregate genuinely needs both a key and a value per row. JSON_ARRAYAGG only needs one expression per row, no KEY/VALUE split required.
Common Beginner Mistakes
Writing JSON_OBJECTAGG(key_col, value_col) on Oracle, matching MySQL's argument style
That's a syntax error on Oracle, which requires JSON_OBJECTAGG(KEY key_col VALUE value_col), the KEY and VALUE keywords are mandatory there, unlike MySQL's and MariaDB's plain comma-separated arguments.
Expecting JSON_OBJECTAGG on a current, generally-available on-premises SQL Server
Same gap as JSON_ARRAYAGG, it's only in a 2025 preview and on Azure SQL Database as of this writing. FOR JSON PATH is the real workaround on a generally-available on-premises install.
Best Practices
- Use Oracle's WITH UNIQUE KEYS clause when a duplicate key in the source data should be an error, not a silently overwritten value.
- Don't carry MySQL's plain comma-separated argument style over to Oracle, its KEY ... VALUE ... keywords are mandatory syntax.
- On PostgreSQL, prefer jsonb_object_agg for new code over the plain-text json_object_agg for the same reason as the array version.
Interview Questions
How does Oracle's JSON_OBJECTAGG syntax genuinely differ from MySQL's?
MySQL and MariaDB take two plain, comma-separated arguments: JSON_OBJECTAGG(key_col, value_col). Oracle requires explicit KEY and VALUE keywords: JSON_OBJECTAGG(KEY key_col VALUE value_col), a real syntax difference, not just a stylistic one, writing the MySQL form on Oracle is a syntax error.
Why does SQL Server have the exact same JSON_OBJECTAGG gap it has for JSON_ARRAYAGG?
Both were added to SQL Server as part of the same JSON-aggregates feature, currently in preview for SQL Server 2025 and available on Azure SQL Database, but not yet generally available on-premises, the same FOR JSON PATH subquery pattern is the real workaround for both until that ships.
Summary
JSON_OBJECTAGG builds a JSON object from a group's key and value columns, under the same real per-database split as the previous lesson's JSON array version, down to SQL Server's identical 2025-preview gap and Oracle's distinctly mandatory KEY ... VALUE ... syntax.
That completes this module's look at collecting a group into one structured value: text (STRING_AGG/GROUP_CONCAT/LISTAGG), a native array (ARRAY_AGG, PostgreSQL only), and JSON (JSON_ARRAYAGG/JSON_OBJECTAGG and their real per-database names).
What's Next?
The next lessons move from collecting values to describing their spread statistically, variance and standard deviation, starting with VAR_POP and VAR_SAMP.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →