寻求类SAS ARRAY函数的JSON映射字段查询优化方案

针对你需要匹配JSON中field["1"]到field["15"]（可扩展）的字段，返回对应序号attribute["n"]值的需求，不用写重复的CASE语句，可通过生成序号序列+行级匹配的方式实现，类似SAS数组的循环逻辑。以下是主流数据库的具体实现：

PostgreSQL #

利用generate_series生成序号序列，结合JSON提取函数匹配字段：

SELECT COALESCE(
  (SELECT jsonb_extract_path_text(attribute, num::text)
   FROM generate_series(1, 15) AS nums(num)
   WHERE jsonb_extract_path_text(field, num::text) = 'XXXXX'
   LIMIT 1), -- 取第一个匹配的结果，和CASE逻辑一致
  NULL -- 无匹配时的默认值，可替换为你需要的内容
) AS p_attribute
FROM your_table;

• 若你的JSON字段是json类型，把jsonb_extract_path_text换成json_extract_path_text即可。
• 调整generate_series(1,15)的第二个参数，可扩展到任意数量的字段（比如20、30）。

MySQL #

通过递归CTE生成序号，拼接JSON路径进行匹配：

WITH RECURSIVE nums AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM nums WHERE num < 15
)
SELECT COALESCE(
  (SELECT JSON_UNQUOTE(JSON_EXTRACT(attribute, CONCAT('$."', num, '"')))
   FROM nums
   WHERE JSON_UNQUOTE(JSON_EXTRACT(field, CONCAT('$."', num, '"'))) = 'XXXXX'
   LIMIT 1),
  NULL
) AS p_attribute
FROM your_table;

• JSON_UNQUOTE用于去除JSON提取结果的引号，若不需要可省略。

SQL Server #

使用递归CTE生成序号，结合JSON_VALUE函数实现匹配：

WITH nums AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM nums WHERE num < 15
)
SELECT COALESCE(
  (SELECT JSON_VALUE(attribute, CONCAT('$."', num, '"'))
   FROM nums
   WHERE JSON_VALUE(field, CONCAT('$."', num, '"')) = 'XXXXX'
   ORDER BY num ASC
   OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY), -- 保证取第一个匹配的结果
  NULL
) AS p_attribute
FROM your_table;

补充说明 #

• 上述方案均和原CASE语句逻辑一致：按序号从小到大匹配，返回第一个符合条件的attribute值。
• 若需要返回所有匹配的attribute值，可去掉LIMIT/FETCH NEXT，改用字符串聚合函数（如PostgreSQL的STRING_AGG、MySQL的GROUP_CONCAT）合并结果。

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