I have begun exploring JSON, the MySQL X Dev API, and the Document Store in earnest due to a requirement I am facing in my day job. The data model I am working with presents several challenges (don’t they all). Inspired in my own right by 2 fantastic books I am currently reading and working through: SQL Antipatterns: Avoiding the Pitfalls of Database Programming by Bill Karwin and Introducing MySQL Shell by Charles Bell. I am starting to see that JSON, the MySQL X Dev API, and the Document Store just might be my salvation. Based on my understanding gained from both books – for differing reasons – I have come to this conclusion. Both books have influenced my thinking in terms of different options, applicable to my particular data needs. In this post, I will go from SQL to JSON all within MySQL using built-in functionality….
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.18
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
To gain more knowledge and proficiency with JSON and the X Dev API should I implement them for my requirements at work, I thought to explore JSON using a data set that I constantly monitor: those stats I collect from my many walks.
I have these 2 tables in which I store walking data such as calories burned, time spent walking, distance walked, etc…:
1 2 3 4 5 6 7 8 9 10 11 12 | MySQL localhost:33060+ ssl walking SQL > DESC walking_stats; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | day_walked | date | YES | | NULL | | | cal_burned | decimal(4,1) | YES | | NULL | | | miles_walked | decimal(4,2) | YES | | NULL | | | duration | time | YES | | NULL | | | mph | decimal(2,1) | YES | | NULL | | | shoe_id | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 6 rows in set (0.0013 sec) |
1 2 3 4 5 6 7 8 | MySQL localhost:33060+ ssl walking SQL > DESC shoes_worn; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | shoe_id | int(11) | YES | | NULL | | | brand_name | text | YES | | NULL | | +------------+---------+------+-----+---------+-------+ 2 rows in set (0.0028 sec) |
The ‘shoe_id’ column present in both tables provides the necessary REFERENTIAL INTEGRITY for a row in the ‘walking_stats’ table to a row in the ‘shoes_worn’ table. My goal is to store this information in a JSON column, already joined up, so to speak.
There are currently 200+ rows in the ‘walking_stats’ table:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl walking SQL > SELECT COUNT(*) FROM walking_stats; +----------+ | COUNT(*) | +----------+ | 234 | +----------+ 1 row in set (0.1006 sec) |
This simple JOIN returns the information for what shoe brand was worn each day I walked:
(I’ll limit the result set for on-screen brevity)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MySQL localhost:33060+ ssl walking SQL > SELECT w.day_walked, w.cal_burned, w.miles_walked, -> w.duration, w.mph, s.brand_name -> FROM walking_stats AS w -> INNER JOIN shoes_worn AS s -> USING (shoe_id) -> LIMIT 5; ------------+------------+--------------+----------+-----+-------------------+ | day_walked | cal_burned | miles_walked | duration | mph | brand_name | +------------+------------+--------------+----------+-----+-------------------+ | 2019-01-02 | 181.1 | 1.76 | 00:33:18 | 3.2 | Keen Koven WP | | 2019-01-07 | 207.3 | 2.03 | 00:38:07 | 3.2 | Oboz Sawtooth Low | | 2019-01-08 | 218.2 | 2.13 | 00:40:07 | 3.2 | Oboz Sawtooth Low | | 2019-01-09 | 193.0 | 1.94 | 00:35:29 | 3.3 | Oboz Sawtooth Low | | 2019-01-10 | 160.2 | 1.58 | 00:29:27 | 3.2 | Oboz Sawtooth Low | +------------+------------+--------------+----------+-----+-------------------+ 5 rows in set (0.1100 sec) |
How can I map this to a JSON column? To not put the cart before the horse, I’ll first create a table named ‘j_walking’ containing a single JSON column.
1 | MySQL localhost:33060+ ssl walking SQL > CREATE TABLE j_walking(stats json); |
(Note: In keeping with good database design practices, I likely should have some form of PRIMARY KEY column for this table. However, since I only walk once a day, the ‘day_walked’ DATE column value is unique and can be used in its stead for my purposes.)
Back to the question of how to map this particular query’s results to JSON? MySQL provides a JSON_OBJECT() function (see the JSON_OBJECT() documentation here for more information) that just so happens to be exactly what I need. JSON_OBJECT() creates a JSON document from a supplied list of key-value pairs.
I can take the joined query results and simply provide a desired key name for each value, basically mapping the keys to the columns named in the SELECT clause:
1 2 3 4 5 | SELECT JSON_OBJECT('day_walked', w.day_walked, 'cal_burned', w.cal_burned, 'miles_walked', w.miles_walked, 'duration', w.duration, 'mph', w.mph, 'shoes_worn', s.brand_name) FROM walking_stats AS w INNER JOIN shoes_worn AS s USING(shoe_id); |
Then, use INSERT with SELECT and populate the ‘j_walking’ table using the JSON values returned from the JSON_OBJECT function call:
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl walking SQL > START TRANSACTION; Query OK, 0 rows affected (0.0004 sec) MySQL localhost:33060+ ssl walking SQL > INSERT INTO j_walking(stats) -> SELECT JSON_OBJECT('day_walked', w.day_walked, 'cal_burned', w.cal_burned, 'miles_walked', -> w.miles_walked, 'duration', w.duration, 'mph', w.mph, 'shoes_worn', s.brand_name) -> FROM walking_stats AS w -> INNER JOIN shoes_worn AS s -> USING(shoe_id); Query OK, 234 rows affected (1.7953 sec) Records: 234 Duplicates: 0 Warnings: 0 MySQL localhost:33060+ ssl walking SQL > COMMIT; Query OK, 0 rows affected (0.3857 sec) |
All the rows are present in the ‘j_walking’ table:
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl walking SQL > SELECT COUNT(*) FROM j_walking; +----------+ | COUNT(*) | +----------+ | 234 | +----------+ 1 row in set (0.0045 sec) |
Some housekeeping…
Notice the ‘duration’ column values have several extra zeros tacked on to the seconds position:
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl walking SQL > SELECT stats->>'$.duration' -> FROM j_walking -> LIMIT 5; +----------------------+ | stats->>'$.duration' | +----------------------+ | 00:33:18.000000 | | 00:38:07.000000 | | 00:40:07.000000 | | 00:35:29.000000 | | 00:29:27.000000 | +----------------------+ 5 rows in set (0.0010 sec) |
I would prefer something along these lines instead:
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl walking SQL > SELECT CAST(stats->>'$.duration' AS TIME) -> FROM j_walking -> LIMIT 5; +------------------------------------+ | CAST(stats->>'$.duration' AS TIME) | +------------------------------------+ | 00:33:18 | | 00:38:07 | | 00:40:07 | | 00:35:29 | | 00:29:27 | +------------------------------------+ 5 rows in set (0.0008 sec) |
However, since the ‘duration’ column is a TIME data type, I can use the TIME_FORMAT() function with the desired format specifiers, yielding the precision I want:
1 2 3 4 5 6 7 8 9 | MySQL localhost:33060+ ssl walking SQL > START TRANSACTION; Query OK, 0 rows affected (0.0005 sec) MySQL localhost:33060+ ssl walking SQL > UPDATE j_walking -> SET stats = JSON_SET(stats, '$.duration', TIME_FORMAT(stats->>'$.duration', "%H:%i:%s")); Query OK, 234 rows affected (0.1309 sec) Rows matched: 234 Changed: 234 Warnings: 0 MySQL localhost:33060+ ssl walking SQL > COMMIT; Query OK, 0 rows affected (0.4265 sec) |
This quick query show the results are as I wanted, in the HH:MM:SS format
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL localhost:33060+ ssl walking SQL > SELECT stats->>'$.duration' AS duration -> FROM j_walking -> LIMIT 5; +----------+ | duration | +----------+ | 00:33:18 | | 00:38:07 | | 00:40:07 | | 00:35:29 | | 00:29:27 | +----------+ 5 rows in set (0.0009 sec) |
And here are 10 rows from the ‘j_walking’ table for an overall picture of the JSON data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | MySQL localhost:33060+ ssl walking SQL > SELECT * FROM j_walking LIMIT 10\G *************************** 1. row *************************** stats: {"mph": 3.2, "duration": "00:33:18", "cal_burned": 181.1, "day_walked": "2019-01-02", "shoes_worn": "Keen Koven WP", "miles_walked": 1.76} *************************** 2. row *************************** stats: {"mph": 3.2, "duration": "00:38:07", "cal_burned": 207.3, "day_walked": "2019-01-07", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 2.03} *************************** 3. row *************************** stats: {"mph": 3.2, "duration": "00:40:07", "cal_burned": 218.2, "day_walked": "2019-01-08", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 2.13} *************************** 4. row *************************** stats: {"mph": 3.3, "duration": "00:35:29", "cal_burned": 193.0, "day_walked": "2019-01-09", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 1.94} *************************** 5. row *************************** stats: {"mph": 3.2, "duration": "00:29:27", "cal_burned": 160.2, "day_walked": "2019-01-10", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 1.58} *************************** 6. row *************************** stats: {"mph": 3.2, "duration": "00:37:55", "cal_burned": 206.3, "day_walked": "2019-01-11", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 2.03} *************************** 7. row *************************** stats: {"mph": 3.2, "duration": "00:46:33", "cal_burned": 253.2, "day_walked": "2019-01-13", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 2.49} *************************** 8. row *************************** stats: {"mph": 3.3, "duration": "00:32:39", "cal_burned": 177.6, "day_walked": "2019-01-14", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 1.78} *************************** 9. row *************************** stats: {"mph": 3.2, "duration": "00:38:03", "cal_burned": 207.0, "day_walked": "2019-01-15", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 2.03} *************************** 10. row *************************** stats: {"mph": 3.2, "duration": "00:45:43", "cal_burned": 248.7, "day_walked": "2019-01-16", "shoes_worn": "Oboz Sawtooth Low", "miles_walked": 2.42} 10 rows in set (0.0013 sec) |
I am excited to learn and more importantly, share what I learn, about JSON, the X Dev API, and the Document Store so look for additional posts to come in the future on these technologies.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.