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

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)

Solution

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();