CODEWITHSUNDEEP
mysqlsqljsonjson-query
Robbie
Robbie

Reputation: 716

convert all JSON columns into new table

I currently have a table structured like:

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

With the phoneNumbers column set as a JSON column type. For simplicity sake though I am wanting to covert all the JSON phone numbers into a new separate table. Something like:

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

It seems like it should be do-able with OPENJSON but so far I haven't had any luck in figuring out how to declare it correctly. Any help is appreciated.

Upvotes: 2

Views: 796

Answers (3)

JERRY
JERRY

Reputation: 1173

Please change CTE defination syntax according to MySQL\Maria versions.

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;

Upvotes: 0

kkkkkkk
kkkkkkk

Reputation: 7738

You can do something like this:

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)

The trick is (SELECT 0 as i union all SELECT 1), depends on your JSON array's length you may need to add more index. You can find out the max length by:

SELECT MAX(JSON_LENGTH(phone)) FROM customer;

Upvotes: 1

JERRY
JERRY

Reputation: 1173

USE recursive CTE with 1 and recurse upto json_length. 

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

then use concat to pass that element_id in Extract Query: 

(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))))

Upvotes: 1

Related Questions