# SQL/JSON

Published articles for SQL/JSON.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## Consider using JSON arrays instead of JSON objects for serialisation

DevFeed: [Consider using JSON arrays instead of JSON objects for serialisation](<https://devfeed.tech/articles/consider-using-json-arrays-instead-of-json-objects-for-serialisation-28935.md>)

Original publisher: [Read original article](<https://blog.jooq.org/consider-using-json-arrays-instead-of-json-objects-for-serialisation/>)

Author: lukaseder

Published: 2025-08-11T12:43:10Z

Content type: opinion

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [JSON](<https://devfeed.tech/topics/json.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Structured-data](<https://devfeed.tech/topics/structured-data.md>)

Tags: [api](<https://devfeed.tech/tags/api.md>), [compression](<https://devfeed.tech/tags/compression.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [json](<https://devfeed.tech/tags/json.md>), [json-array](<https://devfeed.tech/tags/json-array.md>), [json-object](<https://devfeed.tech/tags/json-object.md>), [overhead](<https://devfeed.tech/tags/overhead.md>), [performance](<https://devfeed.tech/tags/performance.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>)

### AI overview

The article explains why jOOQ serialises some internal result data as JSON arrays of arrays instead of JSON objects. Positional access can be faster and avoids problems caused by duplicate column names, but the choice is a tradeoff and depends on whether people need to inspect the JSON or consume the API.

### Source excerpt

When implementing the awesome MULTISET operator in jOOQ, its implementation mostly relied on SQL/JSON support of various RDBMS. In short, while standard SQL supports nested collections via ARRAY or MULTISET operators like this: This is poorly supported in most RDBMS, so jOOQ emulates it using SQL/JSON as follows (or similar): Wait a second. A JSON ... Continue reading Consider using JSON arrays instead of JSON objects for serialisation ->

## Heroku Postgres 17 with the New Upgrade Process: Faster Performance, Easier Upgrade

DevFeed: [Heroku Postgres 17 with the New Upgrade Process: Faster Performance, Easier Upgrade](<https://devfeed.tech/articles/heroku-postgres-17-with-the-new-upgrade-process-faster-performance-easier-upgrade-26439.md>)

Original publisher: [Read original article](<https://www.heroku.com/blog/heroku-postgres-17-new-upgrade-process-faster-performance-easier-upgrade/>)

Author: Jonathan Brown

Published: 2025-05-29T15:32:49Z

Content type: release

Language: en

Sources: [Heroku](<https://devfeed.tech/sources/heroku.md>)

Topics: [Heroku](<https://devfeed.tech/topics/heroku.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [upgrade](<https://devfeed.tech/topics/upgrade.md>), [Database](<https://devfeed.tech/topics/database.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Monitoring](<https://devfeed.tech/topics/monitoring.md>), [Latency](<https://devfeed.tech/topics/latency.md>)

Tags: [availability](<https://devfeed.tech/tags/availability.md>), [database](<https://devfeed.tech/tags/database.md>), [database-performance](<https://devfeed.tech/tags/database-performance.md>), [heroku](<https://devfeed.tech/tags/heroku.md>), [heroku-postgres](<https://devfeed.tech/tags/heroku-postgres.md>), [monitoring](<https://devfeed.tech/tags/monitoring.md>), [news](<https://devfeed.tech/tags/news.md>), [performance](<https://devfeed.tech/tags/performance.md>), [performance-optimization](<https://devfeed.tech/tags/performance-optimization.md>), [platform-updates](<https://devfeed.tech/tags/platform-updates.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>), [upgrade](<https://devfeed.tech/tags/upgrade.md>)

### AI overview

Heroku announces the general availability of Heroku Postgres 17 and makes a new version-upgrade method the default. The release includes query and write performance improvements, enhanced JSON support, lower vacuum memory usage, and expanded observability features.

### Source excerpt

We're excited to announce the general availability of Heroku Postgres version 17, packed with new features and enhancements to your database performance. And that's not all - we're also introducing a game-changing feature that streamlines your upgrade experience. This new method of version upgrade is now the default, so you can try it to upgrade [...] The post Heroku Postgres 17 with the New Upgrade Process: Faster Performance, Easier Upgrade appeared first on Heroku.

## Emulating SQL FILTER with Oracle JSON Aggregate Functions

DevFeed: [Emulating SQL FILTER with Oracle JSON Aggregate Functions](<https://devfeed.tech/articles/emulating-sql-filter-with-oracle-json-aggregate-functions-28937.md>)

Original publisher: [Read original article](<https://blog.jooq.org/emulating-sql-filter-with-oracle-json-aggregate-functions/>)

Author: lukaseder

Published: 2024-06-03T12:17:41Z

Content type: article

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [JSON](<https://devfeed.tech/topics/json.md>)

Tags: [aggregate-functions](<https://devfeed.tech/tags/aggregate-functions.md>), [filter](<https://devfeed.tech/tags/filter.md>), [filter-clause](<https://devfeed.tech/tags/filter-clause.md>), [jooq-3-20](<https://devfeed.tech/tags/jooq-3-20.md>), [json](<https://devfeed.tech/tags/json.md>), [json-aggregate-functions](<https://devfeed.tech/tags/json-aggregate-functions.md>), [json-arrayagg](<https://devfeed.tech/tags/json-arrayagg.md>), [json-objectagg](<https://devfeed.tech/tags/json-objectagg.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>)

### AI overview

This article explains how to emulate the SQL FILTER clause for JSON aggregate functions, particularly in Oracle. It presents a workaround that wraps legitimate data in arrays, filters out emulation-generated rows, and then unwraps the arrays. jOOQ 3.20 will implement these emulations.

### Source excerpt

A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types ... Continue reading Emulating SQL FILTER with Oracle JSON Aggregate Functions ->

## 3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0

DevFeed: [3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0](<https://devfeed.tech/articles/3-18-0-release-with-support-for-more-diagnostics-sql-json-oracle-associative-arrays-multi-dimensional-arrays-r2dbc-1-0-28927.md>)

Original publisher: [Read original article](<https://blog.jooq.org/3-18-0-release-with-support-for-more-diagnostics-sql-json-oracle-associative-arrays-multi-dimensional-arrays-r2dbc-1-0/>)

Author: lukaseder

Published: 2023-03-08T16:34:56Z

Content type: release

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [releases](<https://devfeed.tech/topics/releases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Kotlin](<https://devfeed.tech/topics/kotlin.md>)

Tags: [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [kotlin](<https://devfeed.tech/tags/kotlin.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [r2dbc](<https://devfeed.tech/tags/r2dbc.md>), [release](<https://devfeed.tech/tags/release.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>), [uncategorized](<https://devfeed.tech/tags/uncategorized.md>)

### AI overview

jOOQ 3.18.0 is a release focused on expanded SQL diagnostics and SQL/JSON support, including vendor-specific MySQL and PostgreSQL features. It also adds improvements for the Query Object Model, Oracle associative arrays, PostgreSQL multidimensional arrays, Kotlin, and R2DBC 1.0.

### Source excerpt

DiagnosticsListener improvements A lot of additional diagnostics have been added, including the automated detection of pattern replacements, helping you lint your SQL queries irrespective of whether you're using jOOQ to write your SQL, or if you're using it as a JDBC / R2DBC proxy for an existing application. A lot of these diagnostics are available ... Continue reading 3.18.0 Release with Support for more Diagnostics, SQL/JSON, Oracle Associative Arrays, Multi dimensional Arrays, R2DBC 1.0 ->

## When to Use jOOQ and When to Use Native SQL

DevFeed: [When to Use jOOQ and When to Use Native SQL](<https://devfeed.tech/articles/when-to-use-jooq-and-when-to-use-native-sql-28972.md>)

Original publisher: [Read original article](<https://blog.jooq.org/when-to-use-jooq-and-when-to-use-native-sql/>)

Author: lukaseder

Published: 2022-12-08T13:53:02Z

Content type: comparison

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>), [Test-driven development](<https://devfeed.tech/topics/tdd.md>), [Testcontainers](<https://devfeed.tech/topics/testcontainers.md>)

Tags: [cons](<https://devfeed.tech/tags/cons.md>), [examples](<https://devfeed.tech/tags/examples.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jdbctemplate](<https://devfeed.tech/tags/jdbctemplate.md>), [jdbi](<https://devfeed.tech/tags/jdbi.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [native-sql](<https://devfeed.tech/tags/native-sql.md>), [plain-jdbc](<https://devfeed.tech/tags/plain-jdbc.md>), [plain-sql-templating](<https://devfeed.tech/tags/plain-sql-templating.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [tdd](<https://devfeed.tech/tags/tdd.md>), [testcontainers](<https://devfeed.tech/tags/testcontainers.md>)

### AI overview

This comparison explains when to use the jOOQ API and when to use native SQL for complex queries. It describes jOOQ's advantages for dynamic SQL, multiple database dialects, type safety, data mapping, stored procedures, security, exports, and diagnostics, while noting usability drawbacks for large static queries with many CTEs or derived tables.

### Source excerpt

A frequently encountered doubt people have when using jOOQ is to decide when a "complex" query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ manual is full of side by side examples of the same query, e.g. Using jOOQ: Using native SQL: In the native SQL ... Continue reading When to Use jOOQ and When to Use Native SQL ->

## The Performance of Various To-Many Nesting Algorithms

DevFeed: [The Performance of Various To-Many Nesting Algorithms](<https://devfeed.tech/articles/the-performance-of-various-to-many-nesting-algorithms-28965.md>)

Original publisher: [Read original article](<https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/>)

Author: lukaseder

Published: 2022-06-09T10:15:50Z

Content type: article

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Benchmark](<https://devfeed.tech/topics/benchmark.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Object-relational mapping](<https://devfeed.tech/topics/orm.md>)

Tags: [benchmark](<https://devfeed.tech/tags/benchmark.md>), [benchmarks](<https://devfeed.tech/tags/benchmarks.md>), [database](<https://devfeed.tech/tags/database.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [json-arrayagg](<https://devfeed.tech/tags/json-arrayagg.md>), [many-to-many](<https://devfeed.tech/tags/many-to-many.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [one-to-many](<https://devfeed.tech/tags/one-to-many.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [ordbms](<https://devfeed.tech/tags/ordbms.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [sql-xml](<https://devfeed.tech/tags/sql-xml.md>), [test-data](<https://devfeed.tech/tags/test-data.md>)

### AI overview

This article benchmarks several approaches to nesting to-many relationships with jOOQ, including MULTISET emulation, single joins with client-side deduplication, and multiple queries per nesting level. It reports that these approaches outperform the N+1 approach, with relative performance depending on data size and use case.

### Source excerpt

It's been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will ... Continue reading The Performance of Various To-Many Nesting Algorithms ->