Parsing real-world JSON file and saving it to MySQL in Rust

This is the first application that we truly used Rust. The real application includes HTTP request, but for simplicity we will just parse the output file, a JSON file, and save it to a database, MySQL database.

We are using Cargo to organize the project dependencies as well as compiling and execution. So will start the project by invoking Cargo, it is already assumed that you installed your Rust and point to the right tooling environments before runing Cargo.

Create a Rust project using Cargo.

$ cargo new json-to-mysql
$ cd json-to-mysql

Edit the Cargo.toml file. We will be using only serde and mysql libraries from Rust.

[package]
name = "json-to-mysql"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"
mysql = "*"

Run the command to download and compile the dependecies. This command will download and pre-compiles the dependencies.

$ cargo build

Our goal is to parse the sample real world JSON file and save it to MySQL database (see the JSON format in the image below and sample text). The JSON contains the title of a show in Netflix as well as other metadata like poster and streaming URL.

Actual JSON file.

{"id":21772,"title":"Full Metal Jacket","language":"en","posterPath":"https://image.tmdb.org/t/p/w500/kMKyx1k8hWWscYFnPbnxxN4Eqo4.jpg","region":"US","releaseDate":"06/26/1987","runtimeMins":117,"externalIds":[{"id":600,"dbName":"TMDB","url":"https://www.themoviedb.org/movie/600-full-metal-jacket","posterPath":"https://image.tmdb.org/t/p/w500/kMKyx1k8hWWscYFnPbnxxN4Eqo4.jpg"},{"id":93058,"dbName":"IMDB","url":"https://www.imdb.com/title/tt0093058/"}],"watchAvailability":[{"contentId":"528677","id":10008,"directUrls":["https://www.netflix.com/watch/528677"],"monetizationType":"STREAM","price":null,"currency":null}]}

Modify the src/main.rs. Already provided comment per line as explanation.

use std::fs::File;
use std::io::{prelude::*, BufReader};
use serde_json::{Value};

use mysql::*;
use mysql::prelude::*;

struct Show {
    title: String,
    show_poster: String,
    show_url: String,
}

fn main() {
    // Open and read the file lines
    let path = "./sample.json";
    let file = File::open(path).expect("Unable to read file");
    let reader = BufReader::new(file);
    let lines: Vec<String> = reader.lines().map(|l| l.expect("Could not parse line")).collect();

    // Connect to your my-sql database
    let url = Opts::from_url("mysql://root:password@localhost:3306/db").unwrap();
    let pool = Pool::new(url).unwrap();
    let mut conn = pool.get_conn().unwrap();

    // Loop through the lines
    for line in lines {

        // Parse the line into a JSON object serde_json::Value
        let v: Value = serde_json::from_str(&line).expect("Unable to parse");

        // Since we want to save the streaming services of the said show, we need to 
        // loop an array inside the JSON file.
        let l = v["watchAvailability"][0]["directUrls"].as_array().unwrap().len();
        for n in 0..l {
            let streaming_url = v["watchAvailability"][0]["directUrls"][n].as_str().clone();
            match streaming_url {
                Some(url) => {
                    // Display the streaming url.  I have to do this to remove the Some(url) warning. 
                    // Unused variables in Rust emits warnings
                    println!("{:?}", url);

                    // Create a vector (array of object).  
                    // This provides you the ability to process multiple objects upon saving
                    let shows = vec![
                        Show { 
                            title: v["title"].as_str().as_deref().unwrap_or("Error").to_string(),
                            show_poster: v["posterPath"].as_str().as_deref().unwrap_or("Error").to_string(),
                            show_url: v["watchAvailability"][0]["directUrls"][n].as_str().as_deref().unwrap_or("Error").to_string(),
                        },
                    ];  
                    //Execute an insert query
                    conn.exec_batch(
                        r"INSERT INTO `shows` (`title`, `show_poster`, `show_url`)
                        VALUES (:title, :show_poster, :show_url)",
                    shows.iter().map(|s| params! {
                        "title" => s.title.clone(),
                        "show_poster" => s.show_poster.clone(),
                        "show_url" => s.show_url.clone(),
                        })
                    ).unwrap_err();
                },
                _ => println!("Error"),
            }
        }

    }
}

The complete code can be found in my github repository: https://github.com/hgminerva/rust-json-to-mysql

That’s it. Have fun coding. Please don’t forget to donate if your find this blog helpful.