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
13 posts
11 Aug 2025
3 Jun 2024
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…
27 Jul 2021
It’s been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we’ve released jOOQ 3.15 with MULTISET support, which builds on top of these features to offer type-safe nested collections, the way every ORDBMS should implement them. Building (dogfooding) on top of … Continue reading Standard SQL/JSON…
6 Jul 2021
This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn’t adopt it. And those who did, didn’t agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s…
9 Oct 2020
One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set. The following has always been possible in jOOQ, assuming PostgreSQL’s INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module): The … Continue reading Nesting Collections…
16 May 2020
A few days ago Fatih posted this question on twitter. I’m going to attempt to give my answer, however to do that I need to apply some simplifications as my previous attempts to answer it involved a lot of phrases like a pointer to a pointer, and other unhelpful waffling. Hopefully my simplified answer can […]
5 May 2020
SQL Server supports transforming flat tabular SQL result sets into hierarchical structures by convention using the convenient FOR XML or FOR JSON syntaxes. This is really convenient and less verbose than the standard SQL/XML or SQL/JSON APIs – although the standard ones are more powerful. In this blog post, I’d like to show a few … Continue reading Using SQL…
13 Nov 2019
It’s been a while since I’ve ranted on this blog, but I was recently challenged by a reddit thread to write about this topic, so here goes… So, you’re writing a service that produces some JSON from your database model. What do you need? Let’s see: STOP IT No, seriously. Just stop it right there! … Continue reading Stop Mapping…
16 Nov 2018
A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log’s deltas in order to obtain a snapshot of some record at a given point in time. In this article, I will reproduce their problem statement in a simplified version and … Continue reading How to…
28 Sept 2016
Some people make architecture decisions purely based on the loudest consultant: no, we base all our important architectural decisions on # of tweets. It's a Twitter Oriented Architecture ;) — Gareth Western (@gareth) September 21, 2016 For most others, however, decisions are not as simple as this. For instance: When should we start evaluating NoSQL … Continue reading When to…
11 Oct 2013
Some of y’all may have caught our previous blog post announcing the release of our Java JSON transformation library, Jolt. Jolt is a powerful tool that can accomplish a variety of useful transformations on JSON data, and even chain multiple transformations together. Jolt has additional functionality that is useful for working with JSON including the […]
28 Jun 2013
We are pleased to announce a new open source contribution, a Java based JSON to JSON transformation tool named Jolt. Jolt grew out of a BV Platform API project to migrate the backend from Solr/MySql to Cassandra/ElasticSearch. As such, we were going to be doing a lot of data transformations from the new ElasticSearch JSON […]
31 Mar 2011
As I mentioned yesterday, Christoph is currently developing the Active Directory Integration WordPress plugin. Today we were talking about the write back of WordPress profile settings to Active Directory. I mentioned it would be helpful to have an abstract definition of available Active Directory attributes which can be dynamically integrated […] The post Convert Active Directory schema to XML or…