如何在MySQL中从嵌套JSON字段提取指定子元素值

你当前的SQL无法正确提取目标值，核心问题是路径错误——user_data是数组类型（JSON中用[]包裹），而非单个对象，且内部存储答案的键是elements而非element，导致$.user_data.element.postValue无法定位到目标数据。

以下是两种可行的解决方案：

方法1：使用JSON_TABLE展开数组（推荐，可读性强） #

这种方式将JSON数组转换为关系型表结构，便于精准筛选和提取：

SELECT 
  elements.postValue AS `Incorporation Number`
FROM 
  your_table, -- 替换为你的实际表名
  JSON_TABLE(
    your_table.json_column,
    '$.user_data[*]' COLUMNS (
      element_title_english VARCHAR(255) PATH '$.elementTitle.english',
      answer_elements JSON PATH '$.elements'
    )
  ) AS user_data_items,
  JSON_TABLE(
    user_data_items.answer_elements,
    '$[*]' COLUMNS (
      postValue VARCHAR(255) PATH '$.postValue'
    )
  ) AS elements
WHERE 
  your_table.id = 123
  AND user_data_items.element_title_english = 'Incorporation Number';

方法2：使用JSON_EXTRACT结合条件匹配 #

如果无需展开数组，可直接通过路径匹配提取目标值：

SELECT 
  JSON_EXTRACT(
    json_column,
    '$.user_data[?(@.elementTitle.english == "Incorporation Number")].elements[0].postValue'
  ) AS `Incorporation Number`
FROM 
  your_table -- 替换为你的实际表名
WHERE 
  id = 123;

关键逻辑说明 #

• $.user_data[?(@.elementTitle.english == "Incorporation Number")]：定位到user_data数组中，对应问题标题为"Incorporation Number"的元素
• elements[0].postValue：提取该元素下elements数组第一个项的postValue值

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