Mariadb如何正确查询JSON数组内满足id=14且结束日期更早的记录

• 原语句使用的JSON路径$.[*].id、$.[*].end_date写法不符合JSON路径规范，数组通配符[*]前多余的.会导致路径解析失败，直接取不到对应字段值，是返回空结果的原因之一。
• 即使修正路径写法，使用JSON_VALUE()搭配$[*].id、$[*].end_date取数时，返回的是JSON数组所有元素的id值集合、所有元素的end_date值集合，两个值集是独立取数的，没有建立同一数组元素下id和end_date绑定匹配的关联，无法识别“同一个元素同时满足id=14、end_date早于当日”的逻辑，也会导致匹配结果完全错误。
• 存储的end_date字段为DD-MM-YYYY格式的字符串，直接和YYYY-MM-DD格式的日期值做字符串大小比较，本身逻辑存在偏差。

MariaDB 10.6及以上版本支持JSON_TABLE函数，可将JSON数组拆解为独立行后做条件匹配，语句如下：

SELECT DISTINCT t.*
FROM test_udc t,
JSON_TABLE(
  t.test_udc,
  '$[*]' COLUMNS (
    elem_id INT PATH '$.id',
    elem_end_date VARCHAR(10) PATH '$.end_date'
  )
) AS jt
WHERE jt.elem_id = 14
  AND STR_TO_DATE(jt.elem_end_date, '%d-%m-%Y') < '2021-02-15';

• 上述语句通过JSON_TABLE逐行拆解JSON数组的每个元素，将单个元素的id、end_date字段映射为独立列值，保证两个判断条件是绑定在同一个数组元素上生效的，不会出现跨元素错配。
• 针对非标准格式的日期字符串，必须通过STR_TO_DATE显式转换为合法日期类型后再做大小比较，才能得到正确的日期判断结果。
• 针对给定的测试数据，上述语句会正确返回表中id=1的记录：该记录内id=14的数组元素end_date为14-02-2021，早于2021-02-15；不会匹配id=2的记录（其对应id=14的元素end_date为16-02-2021，晚于目标日期）。

如果使用的是不支持JSON_TABLE的MariaDB 10.5及以下版本，可通过JSON_SEARCH定位目标元素路径，再提取对应字段值匹配：

SELECT *
FROM test_udc
WHERE 
  JSON_SEARCH(test_udc, 'one', '14', NULL, '$[*].id') IS NOT NULL
  AND STR_TO_DATE(
    JSON_UNQUOTE(
      JSON_EXTRACT(
        test_udc,
        CONCAT(
          REPLACE(
            JSON_UNQUOTE(JSON_SEARCH(test_udc, 'one', '14', NULL, '$[*].id')),
            '.id',
            ''
          ),
          '.end_date'
        )
      )
    ),
    '%d-%m-%Y'
  ) < '2021-02-15';

该方案的逻辑是先定位到数组中值为14的id字段所在的路径，再替换路径后缀为end_date提取同元素下的日期值做匹配，保证两个条件的关联关系正确。

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