你可以这样做：

SELECT id,
    name,
    JSON_UNQUOTE(JSON_EXTRACT(phone, CONCAT("$[", seq.i, "]", ".", "number"))) AS NUMBER, 
    JSON_UNQUOTE(JSON_EXTRACT(phone, CONCAT("$[", seq.i, "]", ".", "type"))) AS TYPE
FROM customer, (SELECT 0 AS I UNION ALL SELECT 1) AS seq
WHERE seq.i < json_length(phone)

诀窍是 (SELECT 0 as i union all SELECT 1) ，取决于json数组的长度，您可能需要添加更多索引。您可以通过以下方法找到最大长度：

SELECT MAX(JSON_LENGTH(phone)) FROM customer;

使用带1的递归cte，并递归到json\u长度。

SELECT c.*, JSON_LENGTH(c.phoneNumbers) as json_length
from customers c;

然后使用concat在extract查询中传递该元素的\u id：

(json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$.type.',1))), json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$.number.',1))))
(json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$.type.',2))), json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$.number.',1))))
-
-
-
(json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$.type.',json_length))), json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$.number.',json_length))))

请根据mysql\maria版本更改cte定义语法。

WITH RECURSIVE cte_recurse_json AS
(
  SELECT customer_id, phone_numbers, 0 as recurse, JSON_LENGTH(c.phoneNumbers) as json_length, 
            json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$[',0,'].type'))) as type, 
            json_unquote(JSON_EXTRACT(phoneNumbers, CONCAT('$[',0,'].number'))) as number
  FROM table
  UNION ALL
  SELECT t.customer_id, t.phone_numbers, ct.recurse + 1 as recurse, t.json_length, 
            json_unquote(JSON_EXTRACT(ct.phoneNumbers, CONCAT('$[',ct.recurse,'].type'))) as type, 
            json_unquote(JSON_EXTRACT(ct.phoneNumbers, CONCAT('$[',ct.recurse,'].number'))) as number
  FROM TABLE t
  INNER JOIN cte_recurse_json ct ON t.customer_id = ct.customer_id
  WHERE ct.recurse < json_length
)
SELECT customer_id, type, number FROM cte_recurse_json;