JSON数组提取问题：如何按排序规则获取指定元素而非固定取第一个

我的passport列存储JSON数组，示例数据包含两个护照对象，country字段值分别为AX和JP。我希望按country降序排序后，提取值为JP的元素，但当前查询用$[0]固定取数组第一个元素的country，结果始终是AX，不符合预期，需要修改JSON路径实现按排序过滤获取目标元素。

示例passport列数据 #

[{"status": 1, "country": "AX", "issued_date": "2022-07-05", "passport_no": "ABS123433NEW3", "expiration_date": "2000-06-07"}, {"status": 1, "country": "JP", "issued_date": "2022-05-01", "passport_no": "TK84773812NEW3", "expiration_date": "-"}]

当前错误查询语句 #

LEFT JOIN (
      SELECT
      employees.id AS emp_id,
      JSON_UNQUOTE(JSON_EXTRACT(employees.passport, '$[0].country')) AS latest_passport_no
    FROM
      TAL.employees
    WHERE
      JSON_UNQUOTE(JSON_EXTRACT(employees.passport, '$[0].country')) IS NOT NULL
    ORDER BY
      JSON_UNQUOTE(JSON_EXTRACT(employees.passport, '$[0].country')) DESC
  ) AS latest_passports ON EMP.id = latest_passports.emp_id

解决方案 #

要实现按country降序排序后提取目标元素，不能直接固定取数组下标，需要先将JSON数组拆分为独立行，排序后再筛选对应元素。以下是几种可行的修改方式：

方式1：使用JSON_TABLE拆分数组+排序取第一条 #

LEFT JOIN (
    SELECT
        emp.id AS emp_id,
        pass.country AS latest_passport_country
    FROM
        TAL.employees emp
    JOIN JSON_TABLE(
        emp.passport,
        '$[*]' COLUMNS (
            country VARCHAR(2) PATH '$.country'
        )
    ) pass
    WHERE
        pass.country IS NOT NULL
    GROUP BY
        emp.id
    ORDER BY
        pass.country DESC
    LIMIT 1
) AS latest_passports ON EMP.id = latest_passports.emp_id

方式2：使用窗口函数（多员工场景更严谨） #

如果需要为每个员工单独按country降序取第一条记录，用窗口函数ROW_NUMBER()可以避免分组排序的潜在问题：

LEFT JOIN (
    SELECT
        emp_id,
        country AS latest_passport_country,
        passport_no
    FROM (
        SELECT
            emp.id AS emp_id,
            pass.country,
            pass.passport_no,
            ROW_NUMBER() OVER (PARTITION BY emp.id ORDER BY pass.country DESC) AS rn
        FROM
            TAL.employees emp
        JOIN JSON_TABLE(
            emp.passport,
            '$[*]' COLUMNS (
                country VARCHAR(2) PATH '$.country',
                passport_no VARCHAR(50) PATH '$.passport_no'
            )
        ) pass
        WHERE
            pass.country IS NOT NULL
    ) sub_query
    WHERE rn = 1
) AS latest_passports ON EMP.id = latest_passports.emp_id

说明 #

• JSON_TABLE（MySQL 8.0及以上版本支持）会将JSON数组拆分成多行，每行对应一个护照对象的字段
• 窗口函数ROW_NUMBER()会为每个员工的护照记录按country降序编号，取编号为1的记录就是country值最大的那条（即JP）
• 若需要提取护照的其他字段，只需在JSON_TABLE的COLUMNS中添加对应字段的路径定义即可

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