如何在jOOQ中使用ANY处理返回bigint[]的子查询？

问题背景 #

需求是从original_content表中获取所有id存在于pipeline_status表指定uuid的oc_version（bigint[]类型）列中的行，已有可用的PostgreSQL查询，但编写jOOQ代码时触发上述错误。

错误原因 #

你原代码中DSL.select(PIPELINE_STATUS.OC_VERSION)返回的是查询数组类型的Select对象，直接传入DSL.any()后得到的是QuantifiedSelect类型，而ORIGINAL_CONTENT.ID.eq()方法只能接受单个值的Field参数，类型不匹配导致报错。

解决方案 #

以下两种方案分别对应你提供的两个PostgreSQL查询写法：

方案1：对应unnest拆分数组的SQL写法 #

通过DSL.unnest()将数组拆分为单个值的子查询，再用IN或eq(any(...))匹配：

用IN的写法

dslContext.select(ORIGINAL_CONTENT.asterisk())
          .from(ORIGINAL_CONTENT)
          .where(ORIGINAL_CONTENT.ID.in(
              DSL.select(DSL.unnest(PIPELINE_STATUS.OC_VERSION))
                 .from(PIPELINE_STATUS)
                 .where(PIPELINE_STATUS.UUID.eq(pipelineUuid))
                 .and(PIPELINE_STATUS.OC_VERSION.isNotNull())
          ))
          .fetch();

用eq(any(...))的写法

dslContext.select(ORIGINAL_CONTENT.asterisk())
          .from(ORIGINAL_CONTENT)
          .where(ORIGINAL_CONTENT.ID.eq(
              DSL.any(
                  DSL.select(DSL.unnest(PIPELINE_STATUS.OC_VERSION))
                     .from(PIPELINE_STATUS)
                     .where(PIPELINE_STATUS.UUID.eq(pipelineUuid))
                     .and(PIPELINE_STATUS.OC_VERSION.isNotNull())
              )
          ))
          .fetch();

方案2：对应array()子查询的SQL写法 #

将子查询返回的数组字段转为Field<Long[]>，再用DSL.any()匹配数组元素：

dslContext.select(ORIGINAL_CONTENT.asterisk())
          .from(ORIGINAL_CONTENT)
          .where(ORIGINAL_CONTENT.ID.eq(
              DSL.any(
                  DSL.field(
                      DSL.select(PIPELINE_STATUS.OC_VERSION)
                         .from(PIPELINE_STATUS)
                         .where(PIPELINE_STATUS.UUID.eq(pipelineUuid))
                         .and(PIPELINE_STATUS.OC_VERSION.isNotNull())
                  )
              )
          ))
          .fetch();

验证用表结构与数据 #

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

内容的提问来源于stack exchange，提问作者Clyde Barrow