如何在MySQL查询中获取JSON数组匹配项对应位置的关联值

要实现匹配inquiry.productdescription中指定元素后，获取providerinqlog中对应位置的price_json和time_json值，可以通过MySQL的JSON函数组合实现，以下提供两种可行方案：

方案1：用JSON_SEARCH提取索引后取值 #

直接通过JSON搜索函数定位目标元素的位置，再拼接路径提取对应数组值：

SELECT 
  inquiry.id,
  -- 提取匹配元素的对应价格和时间
  JSON_UNQUOTE(JSON_EXTRACT(providerinqlog.price_json, CONCAT('$[', 
    SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(inquiry.productdescription, 'one', 'c', NULL, '$[*].productkey')), '[', -1), ']', 1)
  , ']'))) AS matched_price,
  JSON_UNQUOTE(JSON_EXTRACT(providerinqlog.time_json, CONCAT('$[', 
    SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(inquiry.productdescription, 'one', 'c', NULL, '$[*].productkey')), '[', -1), ']', 1)
  , ']'))) AS matched_time
FROM inquiry 
JOIN providerinqlog ON providerinqlog.inquiry_id = inquiry.id 
WHERE JSON_CONTAINS(inquiry.productdescription, '{"productkey":"c", "productvalue":"600*4000"}') 
  AND inquiry.userid = 128 
  AND inquiry.id != 320 
  AND providerinqlog.provider_id = 159 
  AND providerinqlog.status_inq IN (2, 3);

关键逻辑说明： #

1. JSON_SEARCH(..., '$[*].productkey')：在productdescription数组中搜索productkey为c的元素，返回其路径（如"$[1]"）
2. SUBSTRING_INDEX组合：从路径字符串中提取出索引数字（如从$[1]中得到1）
3. CONCAT('$[', 索引, ']')：拼接成price_json和time_json的索引路径，用JSON_EXTRACT提取对应值，JSON_UNQUOTE去掉结果的引号

方案2：用JSON_TABLE展开数组匹配（可读性更强） #

将productdescription数组拆分为行记录，通过索引关联获取对应值：

SELECT 
  inquiry.id,
  JSON_UNQUOTE(JSON_EXTRACT(providerinqlog.price_json, CONCAT('$[', jt.idx - 1, ']'))) AS matched_price,
  JSON_UNQUOTE(JSON_EXTRACT(providerinqlog.time_json, CONCAT('$[', jt.idx - 1, ']'))) AS matched_time
FROM inquiry
JOIN providerinqlog ON providerinqlog.inquiry_id = inquiry.id
-- 把productdescription数组拆成带索引的行
JOIN JSON_TABLE(
  inquiry.productdescription,
  '$[*]' COLUMNS(
    idx FOR ORDINALITY, -- 生成从1开始的元素索引
    productkey VARCHAR(255) PATH '$.productkey',
    productvalue VARCHAR(255) PATH '$.productvalue'
  )
) jt ON jt.productkey = 'c' AND jt.productvalue = '600*4000'
WHERE inquiry.userid = 128 
  AND inquiry.id != 320 
  AND providerinqlog.provider_id = 159 
  AND providerinqlog.status_inq IN (2, 3);

关键逻辑说明： #

1. JSON_TABLE：将JSON数组展开为关系型表结构，每行对应数组中的一个元素，同时生成从1开始的索引idx
2. 关联条件匹配目标productkey和productvalue，定位到对应元素的索引
3. 由于JSON数组的原生索引从0开始，所以用idx - 1作为price_json和time_json的索引，提取对应位置的值

两种方案都能精准提取目标位置的price和time值，方案2的可读性更强，适合处理复杂的JSON数组匹配场景。

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