How to use any with subquery returning bigint[] value?

Question

I want to fetch all rows from the original_content where id is in the pipeline_status.oc_version column where pipeline_status.uuid=4f3164b9-6fde-45d6-bd58-86308473b0dc- I have 2 PostgreSQL queries that give me the expected result and I'd like to translate either of them to jOOQ:

SELECT oc.* FROM original_content oc
WHERE oc.id = ANY(SELECT unnest(pipeline_status.oc_version) FROM pipeline_status WHERE pipeline_status.uuid='4f3164b9-6fde-45d6-bd58-86308473b0dc' AND pipeline_status.oc_version IS NOT NULL)
-- or 
SELECT oc.* FROM  original_content oc
WHERE oc.id = ANY(array(SELECT ps.oc_version FROM pipeline_status ps WHERE ps.uuid='4f3164b9-6fde-45d6-bd58-86308473b0dc' AND pipeline_status.oc_version IS NOT NULL))

I have the DSL generated, and I did something like

dslContext.select(
            ORIGINAL_CONTENT.asterisk()
        )
        .from(ORIGINAL_CONTENT)
        .where(ORIGINAL_CONTENT.ID.eq(
                DSL.any(DSL.select(PIPELINE_STATUS.OC_VERSION).from(PIPELINE_STATUS).where(PIPELINE_STATUS.UUID.eq(pipelineUuid)))
            )
        )

However, I get an error:

Cannot resolve method 'eq(QuantifiedSelect<Record1<T>>)'

How do I fix that?

My SQL:

create table public.pipeline_status
(
    uuid                     varchar(50) not null primary key,
    oc_version bigint[]
);


create table original_content
(
    id            bigserial primary key,
    name          varchar(50)                                        not null,
    created_at    timestamp default (now() AT TIME ZONE 'UTC'::text) not null
);

insert into pipeline_status (uuid, oc_version)
values  ('4f3164b9-6fde-45d6-bd58-86308473b0dc', '{1020,1021}');

insert into original_content (id, name,  created_at)
OVERRIDING SYSTEM VALUE
values (1001, 'Name2', '2024-03-22 06:33:12.574244'),
       (1021, 'Name2', '2024-03-22 07:33:32.574244'),
       (1020, 'Name1',  '2024-03-22 09:33:31.574244'),
       (1040, 'Name1',  '2024-03-22 07:33:51.574244'),
       (1002, 'Name3',  '2024-03-22 07:33:13.574244');

SELECT oc.* FROM original_content oc
WHERE oc.id = ANY(SELECT unnest(pipeline_status.oc_version) FROM pipeline_status WHERE pipeline_status.uuid='4f3164b9-6fde-45d6-bd58-86308473b0dc' AND pipeline_status.oc_version IS NOT NULL)

Answer 1

Existing attempts

Using UNNEST() in the SELECT clause is a very weird, historic PostgreSQL specific feature that isn't supported by jOOQ out of the box, although, you can obviously use plain SQL templating to get it to work, e.g.

DSL.field("unnest({0})", 
  ORIGINAL_CONTENT.ID.getDataType(), 
  PIPELINE_STATUS.OC_VERSION
); 

The oc.id = ANY(array(SELECT ps.oc_version ..)) approach seems wrong to me, but I assume it works because PostgreSQL doesn't properly support multi dimensional arrays. The ARRAY(SELECT array_column) expression should produce a BIGINT[][] type, but that doesn't exist in PostgreSQL, which just flattens it. This isn't supported in jOOQ, which assumes multi dimensional arrays are supported properly, so you'll get a wrong Field<Long[][]> type, when you wanted a Field<Long[]> type.

In other words, both of these PostgreSQL specific quirks aren't supported out of the box by jOOQ.

Alternative approach

But I'd rewrite your SQL version of the query to this:

SELECT * 
FROM original_content oc
EXISTS (
  SELECT 1
  FROM pipeline_status pc
  WHERE pc.uuid = '4f3164b9-6fde-45d6-bd58-86308473b0dc'
  AND pc.oc_version IS NOT NULL
  AND oc.id = ANY(pc.oc_version)
)

With jOOQ:

ctx.selectFrom(ORIGINAL_CONTENT)
   .where(exists(
        selectOne()
        .from(PIPELINE_STATUS)
        .where(PIPELINE_STATUS.UUID.eq("4f3164b9-6fde-45d6-bd58-86308473b0dc"))
        .and(PIPELINE_STATUS.OC_VERSION.isNotNull())
        .and(ORIGINAL_CONTENT.ID.eq(any(PIPELINE_STATUS.OC_VERSION)))
   ))
   .fetch();

Answer 2

If you want to keep your initial SQL query (and not rewrite it/simplify it as in Lukas Eder's answer):

SELECT oc.* FROM original_content oc
WHERE oc.id = ANY(SELECT unnest(pipeline_status.oc_version) FROM pipeline_status
WHERE pipeline_status.uuid='4f3164b9-6fde-45d6-bd58-86308473b0dc' AND
      pipeline_status.oc_version IS NOT NULL)

Looking at "jOOQ / 4.12.9. Quantified comparison predicate", using quantified comparison predicates (ANY, ALL) should help: since your case involves comparing a single column (oc.id) against a set of values produced by a subquery, you can use the DSL.any() method in combination with a subselect.

However, because you are dealing with an array column (oc_version), and you want to compare each id against any element within these arrays, you should first unnest (unnest(Field<?> cursor)) the array with a subquery, and then use DSL.any() for the comparison.

The relevant unnest method method would then be:

DSL.unnest(Field<T[]> field)

It takes a Field<T[]>, where T[] is an array type, and it expands the array into a set of rows. In your case, T would be Long or a similar numeric type matching your oc_version array elements.

import org.jooq.impl.DSL;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;

// Assuming these are your generated table and field references
var ORIGINAL_CONTENT = table("original_content");
var PIPELINE_STATUS = table("pipeline_status");
var ID = field("id", Long.class);
var OC_VERSION = field("oc_version", Long[].class);
var UUID = field("uuid", String.class);

String pipelineUuid = "4f3164b9-6fde-45d6-bd58-86308473b0dc";

var correctedQuery = dslContext
        .select(ORIGINAL_CONTENT.asterisk())
        .from(ORIGINAL_CONTENT)
        .where(
            ORIGINAL_CONTENT.field(ID).eq(
                DSL.any(
                    DSL.select(DSL.unnest(PIPELINE_STATUS.field(OC_VERSION).cast(Long[].class)))
                    .from(PIPELINE_STATUS)
                    .where(PIPELINE_STATUS.field(UUID).eq(pipelineUuid))
                    .and(PIPELINE_STATUS.field(OC_VERSION).isNotNull())
                )
            )
        )
        .fetch();

// Execute and print the query
System.out.println(correctedQuery);

DSL.unnest(PIPELINE_STATUS.field(OC_VERSION).cast(Long[].class)) is used to convert the oc_version array column into a set of rows so that each id from the original_content table can be compared against each element within those arrays.

---

I don't use the fields you created like var OC_VERSION = field("oc_version", Long[].class);, but instead I use the fields of the generated DSL, so for example: .and(PIPELINE_STATUS.field(PIPELINE_STATUS.OC_VERSION).isNotNull()).
I still get: Cannot resolve method 'eq(QuantifiedSelect<R>)'.

That error message should mean a type mismatch between what the eq() method expects and what QuantifiedSelect<R> provides: the return type of the subquery might not directly match the expected type for a straightforward equality comparison, hence the error.

The correct appraoach should not wrap a generated field with PIPELINE_STATUS.field() again. Instead, use the generated fields directly as they are already typed correctly. For instance, if PIPELINE_STATUS.OC_VERSION is a generated field representing the oc_version column, it should be used directly without additional wrapping.
And when dealing with arrays and the ANY function, the comparison might require explicit handling to make sure type compatibility.
That means adjusting the query to properly use the ANY function with the expected types. Pseudo-code:

import static org.jooq.impl.DSL.*;

// Example using generated DSL fields directly
var pipelineUuid = "4f3164b9-6fde-45d6-bd58-86308473b0dc";

var query = dslContext
        .selectFrom(ORIGINAL_CONTENT)
        .where(
            ORIGINAL_CONTENT.ID.eq(
                // Use DSL.any() with a subquery that properly matches the expected type
                DSL.any(
                    DSL.select(PIPELINE_STATUS.OC_VERSION)
                       .from(PIPELINE_STATUS)
                       .where(PIPELINE_STATUS.UUID.eq(pipelineUuid))
                       .and(PIPELINE_STATUS.OC_VERSION.isNotNull())
                       // That might need adjustment depending on the actual type of OC_VERSION
                )
            )
        )
        .fetch();

That would end up like Lukas Eder's query.