MySQL Workbench中查询JSON数组内PhoneType为LANDLINE的手机号的方法

实现方案（适用MySQL 8.0及以上版本，无需存储过程） #

不需要手动解析json_search返回的路径数组，直接用JSON_TABLE函数把JSON里嵌套的申请人、手机号数组展开成常规的二维表结构，直接按条件筛选即可，语法简单还能支持各种复杂查询场景。

1. 提取所有PhoneType为LANDLINE的手机号

SELECT jt.PhoneNumber
FROM application,
JSON_TABLE(
  applicationData,
  '$.PersonApplicant[*]' COLUMNS (
    PersonalId VARCHAR(20) PATH '$.PersonalId',
    NESTED PATH '$.ApplicantPhone[*]' COLUMNS (
      PhoneType VARCHAR(20) PATH '$.PhoneType',
      PhoneNumber VARCHAR(20) PATH '$.PhoneNumber',
      IsPrimaryPhone BOOLEAN PATH '$.IsPrimaryPhone'
    )
  )
) AS jt
WHERE jt.PhoneType = 'LANDLINE';

执行后会直接返回两条匹配的手机号：8085063644 和 9643645364。

2. 扩展场景：提取所有拥有LANDLINE号码的用户PersonalId

只需要修改SELECT字段并增加去重逻辑即可：

SELECT DISTINCT jt.PersonalId
FROM application,
JSON_TABLE(
  applicationData,
  '$.PersonApplicant[*]' COLUMNS (
    PersonalId VARCHAR(20) PATH '$.PersonalId',
    NESTED PATH '$.ApplicantPhone[*]' COLUMNS (
      PhoneType VARCHAR(20) PATH '$.PhoneType'
    )
  )
) AS jt
WHERE jt.PhoneType = 'LANDLINE';

返回的结果就是符合条件的用户ID：1005、1006。

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