如何合并两个SQL查询，通过索引获取数组查询返回的指定元素

核心逻辑 #

将第一个查询生成的数组作为取值源，第二个查询返回的索引作为下标，通过数据库支持的数组取值语法即可直接获取目标元素，以下提供不同场景的实现方案： #

方案1：子查询嵌套写法（适配PostgreSQL等原生支持数组类型的数据库） #

SELECT 
  (SELECT ARRAY(
    SELECT journeys.id 
    FROM JOURNEYS 
    JOIN RESPONSES ON scenarios[1] = responses.id
  ))[
    (SELECT journeys_index.j_index 
     FROM journeys_index
     WHERE environment = 'env1' 
       AND for_channel = 'ch1' 
       AND first_name = 'name1'
     LIMIT 1)
  ] AS target_element;

方案2：CTE易读写法（逻辑分层更清晰，方便后续修改） #

WITH journey_arr AS (
  -- 预生成目标数组
  SELECT ARRAY(
    SELECT journeys.id 
    FROM JOURNEYS 
    JOIN RESPONSES ON scenarios[1] = responses.id
  ) AS arry
),
target_idx AS (
  -- 预获取目标索引
  SELECT j_index 
  FROM journeys_index
  WHERE environment = 'env1' 
    AND for_channel = 'ch1' 
    AND first_name = 'name1'
  LIMIT 1 -- 避免返回多个索引导致报错
)
SELECT arry[j_index] AS target_element
FROM journey_arr, target_idx;

方案3：MySQL适配写法（针对使用JSON数组存储的场景） #

如果使用MySQL，可将数组转为JSON格式后通过JSON_EXTRACT取值：

SELECT JSON_EXTRACT(
  (SELECT JSON_ARRAYAGG(journeys.id)
   FROM JOURNEYS 
   JOIN RESPONSES ON scenarios[1] = responses.id),
  CONCAT('$[', (SELECT j_index FROM journeys_index WHERE environment = 'env1' AND for_channel = 'ch1' AND first_name = 'name1' LIMIT 1), ']')
) AS target_element;

注意事项 #

• 确认数据库的数组下标起始规则：PostgreSQL等多数数据库默认数组下标从1开始，如果你的j_index是从0计数的，需要将索引值+1后再使用
• 若索引查询可能返回空值，可搭配COALESCE函数设置默认值避免返回异常

内容的提问来源于stack exchange，提问作者Hadas Hamerovv