Joomla 4自定义字段JSON数组提取name和value的MySQL查询求助

你的原查询错误在于使用JSON_OBJECT构造固定JSON对象，并未从目标列的JSON数据中提取实际内容，因此无法得到预期结果。以下是针对不同MySQL版本的解决方案：

MySQL 8.0+（推荐，Joomla 4官方兼容版本） #

使用JSON_TABLE函数将JSON对象转换为关系型数据，直接提取所有name和value：

SELECT 
  opt.name, 
  opt.value
FROM 
  josl2_fields f,
  JSON_TABLE(
    JSON_EXTRACT(f.fieldparams, '$.options'),
    '$.*' COLUMNS(
      name VARCHAR(255) PATH '$.name',
      value VARCHAR(255) PATH '$.value'
    )
  ) AS opt
WHERE 
  f.title = 'manufacturer';

语句说明： #

• JSON_EXTRACT(f.fieldparams, '$.options')：从fieldparams列中提取options节点的JSON对象
• JSON_TABLE(...)：将options下的所有子节点（options0、options1等）转换为行数据，同时提取每个子节点的name和value字段
• 关联原表并筛选标题为manufacturer的字段数据

MySQL 5.7（兼容方案） #

若使用MySQL 5.7（不支持JSON_TABLE），可通过JSON_KEYS和索引遍历提取数据：

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(f.fieldparams, CONCAT('$.options.', k.option_key, '.name'))) AS name,
  JSON_UNQUOTE(JSON_EXTRACT(f.fieldparams, CONCAT('$.options.', k.option_key, '.value'))) AS value
FROM
  josl2_fields f,
  (
    SELECT JSON_UNQUOTE(JSON_EXTRACT(options_keys, CONCAT('$[', idx, ']'))) AS option_key
    FROM
      (SELECT JSON_KEYS(fieldparams, '$.options') AS options_keys FROM josl2_fields WHERE title = 'manufacturer') AS keys,
      (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS indexes
    WHERE idx < JSON_LENGTH(options_keys)
  ) AS k
WHERE
  f.title = 'manufacturer';

语句说明： #

• 先通过JSON_KEYS获取options下的所有键名（options0等）
• 生成索引序列遍历所有键，逐个提取对应name和value
• 注意：索引序列需覆盖所有选项数量，若选项更多需补充UNION ALL SELECT n

运行上述查询后，将以列表形式返回所有name和value的对应结果。

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