Dave's MySQL Quiz #3 -- JSON data conversion
source link: https://blogs.oracle.com/mysql/daves-mysql-quiz-3-json-data-conversion
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Dave's MySQL Quiz #3 -- JSON data conversion
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;
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;
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK