Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it’s so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem).
In PostgreSQL, you can write:
CREATE TABLE blogs ( id SERIAL NOT NULL PRIMARY KEY, title text NOT NULL, tags text[] )
Or in Oracle:
-- Oracle only knows nominal array types, so we have to declare -- them in advance CREATE TYPE tag_t AS VARRAY(100) OF VARCHAR2(100 CHAR); CREATE TABLE blogs ( id NUMBER(18) GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, title VARCHAR2(100 CHAR) NOT NULL, tags tag_t )
So, roughly the same thing. Now, let’s insert some data. How about the 3 most recent posts on the jOOQ blog, prior to this one:
In PostgreSQL:
INSERT INTO blogs (title, tags) VALUES ( 'How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ', ARRAY[ 'implicit cursor', 'batch', 'oracle', 'jooq', 'jdbc', 'resultset' ] ), ( 'How to Execute SQL Batches With JDBC and jOOQ', ARRAY[ 'batch', 'batch statement', 'mysql', 'jooq', 'jdbc', 'sql server', 'sql' ] ), ( 'How to Emulate Partial Indexes in Oracle', ARRAY[ 'optimisation', 'index', 'partial index', 'oracle', 'sql', 'postgresql', 't-sql', 'sql server' ] )
Or in Oracle:
INSERT INTO blogs (title, tags) VALUES ( 'How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ', tag_t( 'implicit cursor', 'batch', 'oracle', 'jooq', 'jdbc', 'resultset' )); INSERT INTO blogs (title, tags) VALUES ( 'How to Execute SQL Batches With JDBC and jOOQ', tag_t( 'batch', 'batch statement', 'mysql', 'jooq', 'jdbc', 'sql server', 'sql' )); INSERT INTO blogs (title, tags) VALUES ( 'How to Emulate Partial Indexes in Oracle', tag_t( 'optimisation', 'index', 'partial index', 'oracle', 'sql', 'postgresql', 't-sql', 'sql server' ));
Now, the array type by itself is not very useful. When it gets really interesting is when we unnest it again into a table. For instance in PostgreSQL:
SELECT title, tag FROM blogs, LATERAL unnest(tags) AS tags(tag);
Or in Oracle:
-- Classic style SELECT title, tags.* FROM blogs, TABLE(tags) tags; -- Since Oracle 12c SELECT title, tags.* FROM blogs, LATERAL (SELECT * FROM TABLE(tags)) tags;
Note that we’re using the keyword LATERAL
in some of the above queries. For those of you who are used to T-SQL syntax, it’s almost the same thing as APPLY
. Both LATERAL
and APPLY
are also very useful with table valued functions (stay tuned for a blog post on those).
The idea behind LATERAL
is that the table (derived table, subquery, function call, array unnesting) on the right side of LATERAL
can “laterally” access stuff from the left side of LATERAL
in order to produce new tables. In the above query, we’re producing a new table of tags for each blog post, and then we cross join the two tables.
Here’s what the above queries result in:
title tag ----------------------------------------------------------------------------- How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ implicit cursor How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ batch How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ oracle How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ jooq How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ jdbc How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ resultset How to Execute SQL Batches With JDBC and jOOQ batch How to Execute SQL Batches With JDBC and jOOQ batch statement How to Execute SQL Batches With JDBC and jOOQ mysql How to Execute SQL Batches With JDBC and jOOQ jooq How to Execute SQL Batches With JDBC and jOOQ jdbc How to Execute SQL Batches With JDBC and jOOQ sql server How to Execute SQL Batches With JDBC and jOOQ sql How to Emulate Partial Indexes in Oracle optimisation How to Emulate Partial Indexes in Oracle index How to Emulate Partial Indexes in Oracle partial index How to Emulate Partial Indexes in Oracle oracle How to Emulate Partial Indexes in Oracle sql How to Emulate Partial Indexes in Oracle postgresql How to Emulate Partial Indexes in Oracle t-sql How to Emulate Partial Indexes in Oracle sql server
You can immediately see the cross join semantics here, as we’re combining each tag (per post) with its post.
Looking for ordinals (i.e. the tag number inside of the array) along with the array? Easy!
Just add the powerful WITH ORDINALITY
clause after the UNNEST()
call in PostgreSQL:
SELECT title, tag FROM blogs, LATERAL unnest(tags) WITH ORDINALITY AS tags(tag);
A bit more complicated to emulate in Oracle:
-- Fancy, with a window function SELECT title, tags.* FROM blogs, LATERAL ( SELECT tags.*, ROW_NUMBER() OVER (ORDER BY NULL) FROM TABLE(tags) tags ) tags; -- Classic, with ROWNUM SELECT title, tags.* FROM blogs, LATERAL ( SELECT tags.*, ROWNUM FROM TABLE(tags) tags ) tags;
The result now contains the tag “ID”, i.e the ordinal of the tag inside of the array:
title tag ordinal ------------------------------------------------------------------------- How to Fetch ... Cursors with JDBC and jOOQ implicit cursor 1 How to Fetch ... Cursors with JDBC and jOOQ batch 2 How to Fetch ... Cursors with JDBC and jOOQ oracle 3 How to Fetch ... Cursors with JDBC and jOOQ jooq 4 How to Fetch ... Cursors with JDBC and jOOQ jdbc 5 How to Fetch ... Cursors with JDBC and jOOQ resultset 6 How to Execute SQL Batches With JDBC and jOOQ batch 1 How to Execute SQL Batches With JDBC and jOOQ batch statement 2 How to Execute SQL Batches With JDBC and jOOQ mysql 3 How to Execute SQL Batches With JDBC and jOOQ jooq 4 How to Execute SQL Batches With JDBC and jOOQ jdbc 5 How to Execute SQL Batches With JDBC and jOOQ sql server 6 How to Execute SQL Batches With JDBC and jOOQ sql 7 How to Emulate Partial Indexes in Oracle optimisation 1 How to Emulate Partial Indexes in Oracle index 2 How to Emulate Partial Indexes in Oracle partial index 3 How to Emulate Partial Indexes in Oracle oracle 4 How to Emulate Partial Indexes in Oracle sql 5 How to Emulate Partial Indexes in Oracle postgresql 6 How to Emulate Partial Indexes in Oracle t-sql 7 How to Emulate Partial Indexes in Oracle sql server 8
Now, imagine looking for those blog posts that are tagged “jooq”. Easy!
PostgreSQL:
SELECT title FROM blogs WHERE 'jooq' = ANY(tags);
Oracle:
SELECT title FROM blogs WHERE 'jooq' IN (SELECT * FROM TABLE(tags));
Yielding:
title ----------------------------------------------------------- How to Fetch Oracle 12c Implicit Cursors with JDBC and jOOQ How to Execute SQL Batches With JDBC and jOOQ
Conclusion
These are just a few nice things we can do when we denormalise our data into nested collections / arrays, and then use features like UNNEST
to bring them back to the table level. Both Oracle and PostgreSQL support a variety of really nice features building on top of arrays, so do check them out!