X

MySQL and HeatWave

  • MySQL
    September 27, 2021

Dave's MySQL Quiz #3 -- JSON data conversion

David Stokes
MySQL Community Manager

 This weeks quiz is a little different.  Many of you grew up with MySQL's sample world database but have not used the world_x database (see https://elephantdolphin.blogspot.com/2021/09/sample-databases-for-learning-mysql-and.html for information on both sample data sets) and may be shocked to find that there is now a forth table to the database and some of the information is now stored in a JSON data type column. 

    The quiz is to take the following code that works with the world database and convert it to work with the world_x database.

SELECT city.Name as 'City'
    , city.Population as 'city pop'
    , country.name as 'country'
    , country.Population as 'country pop'
    , round(city.Population / country.Population * 100,2) as 'Percent'  
FROM city 
JOIN country on (city.CountryCode = country.Code) 
ORDER BY Country.Name,  city.name
LIMIT 10;

 
    The output should look like this:
 

Hint #1 -- The data has moved in some cases

Hint #2 -- You can use JSON_UNQUOTE(JSON_EXTRACT(...)) or the ->> operator (See https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract and https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-inline-path)

There is a solution below and your solution may be different than what is presented.

 

 

Solution:
SELECT city.name as 'City'
    ,city.Info->>"$.Population" as 'city pop'
    ,countryinfo.doc->>"$.Name" as 'country'
    ,countryinfo.doc->>"$.demographics.Population" as 'country pop'
    ,round(city.Info->>"$.Population" / countryinfo.doc->>"$.demographics.Population" * 100,2) as 'Percent'  
FROM city 
JOIN countryinfo on (city.CountryCode = countryinfo.doc->>"$._id") 
ORDER BY countryinfo.doc->>"$.Name",  city.name
LIMIT 10;

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.