将所有json列转换为新表

y4ekin9u  于 2021-06-20  发布在  Mysql
关注(0)|答案(3)|浏览(521)

我目前有一个表的结构如下:

customer_id  name   phoneNumbers  
1            Adam   [{'type':'home','number':'687-5309'} , {'type':'cell','number':'123-4567'}] 
2            Bill   [{'type':'home','number':'987-6543'}]

将phonenumbers列设置为json列类型。为了简单起见,我想把所有的json电话号码转换成一个新的单独的表。比如:

phone_id  customer_id type    number
1         1           home    687-5309  
2         1           cell    123-4567
3         2           home    987-6543

似乎它应该可以用openjson实现,但到目前为止,我还没有找到正确声明它的方法。感谢您的帮助。

ugmeyewa

ugmeyewa1#

你可以这样做:

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;
lsmd5eda

lsmd5eda2#

使用带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))))
a11xaf1n

a11xaf1n3#

请根据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;

相关问题