# Nested collections

Published articles for Nested collections.

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

## How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ

DevFeed: [How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ](<https://devfeed.tech/articles/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq-28945.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/>)

Author: lukaseder

Published: 2023-03-24T15:45:28Z

Content type: tutorial

Language: en

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

Topics: [Java](<https://devfeed.tech/topics/java.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Jackson](<https://devfeed.tech/topics/jackson.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Library](<https://devfeed.tech/topics/library.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [collections](<https://devfeed.tech/tags/collections.md>), [collector](<https://devfeed.tech/tags/collector.md>), [collectors](<https://devfeed.tech/tags/collectors.md>), [hierarchical-sql](<https://devfeed.tech/tags/hierarchical-sql.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jackson](<https://devfeed.tech/tags/jackson.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [json](<https://devfeed.tech/tags/json.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [recursive-sql](<https://devfeed.tech/tags/recursive-sql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

A tutorial on converting flat parent-child data into hierarchical structures using SQL, Java, or jOOQ. It compares a recursive PostgreSQL query with jOOQ 3.19 and a reusable pure-Java Collector, and discusses JSON serialization with Jackson and type inference considerations.

### Source excerpt

Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 ... Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ ->

## Why You Should Execute jOOQ Queries With jOOQ

DevFeed: [Why You Should Execute jOOQ Queries With jOOQ](<https://devfeed.tech/articles/why-you-should-execute-jooq-queries-with-jooq-28974.md>)

Original publisher: [Read original article](<https://blog.jooq.org/why-you-should-execute-jooq-queries-with-jooq/>)

Author: lukaseder

Published: 2023-01-18T11:46:25Z

Content type: opinion

Language: en

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

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Java](<https://devfeed.tech/topics/java.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Compiler](<https://devfeed.tech/topics/compiler.md>)

Tags: [code-generation](<https://devfeed.tech/tags/code-generation.md>), [crud](<https://devfeed.tech/tags/crud.md>), [csv](<https://devfeed.tech/tags/csv.md>), [execution](<https://devfeed.tech/tags/execution.md>), [export](<https://devfeed.tech/tags/export.md>), [identity](<https://devfeed.tech/tags/identity.md>), [import](<https://devfeed.tech/tags/import.md>), [java](<https://devfeed.tech/tags/java.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [jpa](<https://devfeed.tech/tags/jpa.md>), [json](<https://devfeed.tech/tags/json.md>), [mapping](<https://devfeed.tech/tags/mapping.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [nested-records](<https://devfeed.tech/tags/nested-records.md>), [orm](<https://devfeed.tech/tags/orm.md>), [r2dbc](<https://devfeed.tech/tags/r2dbc.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [type-safety](<https://devfeed.tech/tags/type-safety.md>), [udt](<https://devfeed.tech/tags/udt.md>), [updatablerecord](<https://devfeed.tech/tags/updatablerecord.md>), [user-defined-types](<https://devfeed.tech/tags/user-defined-types.md>), [xml](<https://devfeed.tech/tags/xml.md>)

### AI overview

The article argues that queries built with jOOQ should generally also be executed with jOOQ. It explains that jOOQ preserves type safety and structured result mapping during execution, whereas extracting SQL and bind values for JDBC-level execution loses compiler knowledge of the result set. It acknowledges limited use cases for executing jOOQ-generated SQL elsewhere, such as a small number of dynamic queries in an otherwise JPA-based application.

### Source excerpt

Previously on this blog, I've written a post explaining why you should use jOOQ's code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I've answered numerous jOOQ questions on Stack Overflow, where someone used jOOQ to build a query, but then executed it elsewhere, including on: jOOQ itself isn't ... Continue reading Why You Should Execute jOOQ Queries With jOOQ ->

## The Second Best Way to Fetch a Spring Data JPA DTO Projection

DevFeed: [The Second Best Way to Fetch a Spring Data JPA DTO Projection](<https://devfeed.tech/articles/the-second-best-way-to-fetch-a-spring-data-jpa-dto-projection-28966.md>)

Original publisher: [Read original article](<https://blog.jooq.org/the-second-best-way-to-fetch-a-spring-data-jpa-dto-projection/>)

Author: lukaseder

Published: 2022-08-30T14:48:44Z

Content type: tutorial

Language: en

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

Topics: [Code](<https://devfeed.tech/topics/code.md>), [Spring Boot](<https://devfeed.tech/topics/spring-boot.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [dto-projection](<https://devfeed.tech/tags/dto-projection.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [repositories](<https://devfeed.tech/tags/repositories.md>), [spring-boot](<https://devfeed.tech/tags/spring-boot.md>), [spring-data-jpa](<https://devfeed.tech/tags/spring-data-jpa.md>)

### AI overview

The article presents jOOQ as a second way to implement a Spring Data JPA DTO projection. It shows value-constructor and aggregate-function approaches, emphasizing type-safe record conversion and reduced infrastructure code.

### Source excerpt

I've just stumbled upon this great post by Vlad Mihalcea, titled The Best Way to Fetch a Spring Data JPA DTO Projection. It got some nice traction on reddit, too. This is such a nice use-case and apt solution, I wanted to quickly show the second best way of doing the same, with jOOQ this ... Continue reading The Second Best Way to Fetch a Spring Data JPA DTO Projection ->

## How to Filter a SQL Nested Collection by a Value

DevFeed: [How to Filter a SQL Nested Collection by a Value](<https://devfeed.tech/articles/how-to-filter-a-sql-nested-collection-by-a-value-28940.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-filter-a-sql-nested-collection-by-a-value/>)

Author: lukaseder

Published: 2022-06-10T14:32:38Z

Content type: tutorial

Language: en

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

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Stack Overflow](<https://devfeed.tech/topics/stackoverflow.md>)

Tags: [array](<https://devfeed.tech/tags/array.md>), [array-agg](<https://devfeed.tech/tags/array-agg.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [filter](<https://devfeed.tech/tags/filter.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [multiset-agg](<https://devfeed.tech/tags/multiset-agg.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [order-of-operations](<https://devfeed.tech/tags/order-of-operations.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [predicate](<https://devfeed.tech/tags/predicate.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

This tutorial explains how to filter SQL nested collections by a value. It compares filtering arrays through a derived table with using PostgreSQL's ARRAY_AGG and group filtering, then shows a jOOQ version intended to work across supported relational database systems.

### Source excerpt

I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ's MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, ... Continue reading How to Filter a SQL Nested Collection by a Value ->

## 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 ->

## How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ

DevFeed: [How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ](<https://devfeed.tech/articles/how-to-typesafely-map-a-nested-sql-collection-into-a-nested-java-map-with-jooq-28946.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-typesafely-map-a-nested-sql-collection-into-a-nested-java-map-with-jooq/>)

Author: lukaseder

Published: 2022-05-09T10:06:51Z

Content type: tutorial

Language: en

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

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

Tags: [collections](<https://devfeed.tech/tags/collections.md>), [data](<https://devfeed.tech/tags/data.md>), [data-structures](<https://devfeed.tech/tags/data-structures.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [map](<https://devfeed.tech/tags/map.md>), [mapping](<https://devfeed.tech/tags/mapping.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [nested-maps](<https://devfeed.tech/tags/nested-maps.md>), [nested-records](<https://devfeed.tech/tags/nested-records.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stack-overflow](<https://devfeed.tech/tags/stack-overflow.md>), [typesafety](<https://devfeed.tech/tags/typesafety.md>)

### AI overview

This tutorial shows how to use jOOQ 3.15's ad-hoc conversion API and nested collection features to map a nested SQL collection into a type-safe nested Java Map. It uses the Sakila database example and explains how collectors preserve insertion order.

### Source excerpt

A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I've blogged about the powerful MULTISET operator many times, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in ... Continue reading How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ ->