MySQL Backslashes
Yesterday, I wrote a blog post that showed you how to write a query returning a JSON structure for a 1:many relationship. The relationship was between the member and contact table. It returns one account_number from the member table and a list of first_name and last_name columns from the contact table in a JSON structure.
One of my students asked why I choose to strip the backslashes with Python, and my reply was the SQL was already complex for most blog readers. The student asked but how would you do it in SQL. OK, that’s a fair question for two reasons. First, you don’t need to do in your local programs because it’ll run faster on the server. Second, if you strip the backslashes you can insert it into a standard JSON column. This blog post will show you how to do both.
You would use three REGEXP_REPLACE function calls, like:
SELECT REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( JSON_OBJECT( 'account_number', account_number ,'contact', CONCAT('[' , GROUP_CONCAT( JSON_OBJECT('first_name',first_name ,'last_name',last_name ) SEPARATOR ',') ,']') ) ,'\\\\','') ,'"\\\[','\\\[') ,'\\\]"','\\\]') AS json_result FROM member m INNER JOIN contact c ON m.member_id = c.member_id GROUP BY m.account_number; |
It returns the following:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"contact": [{"last_name": "Winn", "first_name": "Randi"},{"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"} |
| {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"},{"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"} |
| {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"},{"last_name": "Sweeney", "first_name": "Matthew"},{"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
| {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"} |
| {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"} |
| {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"} |
| {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"} |
| {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"} |
| {"contact": [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec) |
Let’s create a table with a JSON structure with the following script:
/* Drop table if it exists. */ DROP TABLE IF EXISTS example; /* Create a example table. */ CREATE TABLE example ( id int unsigned auto_increment , struct json , PRIMARY KEY (id)); |
Now, we can embed the query inside an INSERT statement:
INSERT INTO example ( struct ) (SELECT REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( JSON_OBJECT( 'account_number', account_number ,'contact', CONCAT('[' , GROUP_CONCAT( JSON_OBJECT('first_name',first_name ,'last_name',last_name ) SEPARATOR ',') ,']') ) ,'\\\\','') ,'"\\\[','\\\[') ,'\\\]"','\\\]') AS json_result FROM member m INNER JOIN contact c ON m.member_id = c.member_id GROUP BY m.account_number); |
A query of the example table, like:
SELECT * FROM example; |
Returns:
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | struct |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"contact": [{"last_name": "Winn", "first_name": "Randi"}, {"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"} |
| 2 | {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"}, {"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"} |
| 3 | {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"}, {"last_name": "Sweeney", "first_name": "Matthew"}, {"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
| 4 | {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"} |
| 5 | {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"} |
| 6 | {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"} |
| 7 | {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"} |
| 8 | {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"} |
| 9 | {"contact": [{"last_name": "Potter", "first_name": "Harry"}, {"last_name": "Potter", "first_name": "Ginny"}, {"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"} |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec) |
I hope this answers the question about whether you can use SQL remove the backslashes from the original result set and how you can insert the JSON result set into a JSON data type.
Subscribe via RSS