0

I have same table with json field:

CREATE TABLE IF NOT EXISTS `collections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items` json DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `collections` SET `id` = 1, `items`='{"0": {"id": 12967, "color": 17}, "1": {"id": 12920, "color": 69}, "2": {"id": 12310, "color": 24}, "3": {"id": 12261, "color": 17}, "4": {"id": 11874, "color": 17}}', `title`= 'First';

I want split items from collections and move item to new table:

CREATE TABLE IF NOT EXISTS `collection_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `collection` int(11) DEFAULT NULL,
  `item` int(11) DEFAULT NULL,
  `color` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `collections_items` SET `collection`=1, `item`=12967, `color`=17;
INSERT INTO `collections_items` SET `collection`=1, `item`=12920, `color`=69;

Note: I don't want to use any scripts beyond MySQL (php, etc). If I use:

SELECT items, JSON_EXTRACT(items, '$.*.id') ids,  JSON_EXTRACT(items, '$.*.color') colors
FROM site_catalog_collections 
WHERE id=1;

It return array as field:

ids
[12967, 12920, 12310, 12261, 11874]
colors
[17, 69, 24, 17, 17]

How it insert to collection_items?

Improve this question
4
  • Oh very nice, you have got yourself a bad table structure and now you want someone to write a stored procedure to fix it. Have you tried anything yourself? Commented Dec 16, 2016 at 8:09
  • All I came up with is just to write a script on php, create loop to iterate through all the data and get items array via json_deсode with the creation of the new nested loop to insert into collection_items Commented Dec 16, 2016 at 8:24
  • Have you tried to Google? Because, in that link I found this as the third result. If yes, can you show us what you've tried? Where did it not work/go wrong? (update: found some more JSON functions native to MySQL (requires MySQL >5.7) ) Commented Dec 16, 2016 at 8:49
  • All these solutions are only suitable for one-to-one, in my schema, need to select and implement one-to-many Commented Dec 16, 2016 at 9:35
Related questions
3
How to retrieve values stored in JSON array in MySQL query itself?
0
Use JSON Array in MySQL
0
fetch the value as array value
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.