mysql - Inserting JSON data line by line into an SQL table

Question

I have a JSON file that stores the information about a bunch of recipes, like cuisine, time, the ingredients, instructions, etc. I am supposed to transfer all the data to a MySQL table with the relevant headings.

The "ingredients" and the "instructions" are stored like this:

The instructions and ingredients have several "lines" , stored as a list.

How can we store the ingredients and instructions in a MySQL table, in a line by line format?

something like:

instructions

inst1

inst2

..

The JSON file was created using a python program using the beautiful soup module.

PS: I am very new to both SQL and JSON, so I unfortunately dont have anything to show under "what I tried"...Any help will be appreciated.

question from:https://stackoverflow.com/questions/65642918/inserting-json-data-line-by-line-into-an-sql-table

Answer 1

Rather than give you the exact answer, I'll give you the process I use to determine a database structure. You're using a relational database, so that's what I'll talk about. Its also good to have a naming convention, I've used CamelCase here but you can do whatever you want.

You mentioned you were using python but this answer is language agnostic.

You've chosen quite a complex example, but I'll assume you understand how to create a table, and use primary keys and foreign keys. If not, maybe you should do something simpler.

Step 1 - Figure out what the entities are

These are the real-life entities which need to represented as database tables. In this case, I'm seeing 4 entities;

• Recipe
• Keyword
• Ingredient
• Instruction

Each of these can have a table in MySql. Give them a Primary key which follows a naming convention.

Step 2 - figure out the relationships

It looks like keywords are shared between multiple recipes, so you'll a many to many relationship - this means there's going to be an extra table,

• RecipeKeyword

This is just a link between Recipe and keyword to avoid redundancy. It has two foreign keys, RecipeId and KeywordId. At the moment its just a dumb object. In other situations like this, its common for an application to need information about a join - for example, who linked the two things together (consider users, permissions, and a join table with information on who granted the permission)

The other entities are one to many - each will need a foreign key, RecipeId

Step 3 - design each table

As well as having several lists, your Recipe object has some properties. These can be in its table. Most of them are strings in your data, although there are better ways to store things we can keep this simple.

The other entities just have a text field, from your screenshot, only the Recipe has properties.

For this system, you'll need to first insert all Recipe and Keyword objects. There is a common pattern in relational databases where in insert a record, and get its ID so you can insert more stuff which references it.

Step 4 - find a python mysql library

I don't know of one but google will help you find it. The documentation should include the basics of querying.

Step 5 - Insert your data

Here is some psudocode

    FOR EACH recipe
        INSERT the recipe, and get its ID
        FOR EACH keyword
            IF the keyword does not exist already
                INSERT the new keyword and get its ID

            INSERT a record into RecipeKeyword with RecipeId and KeywordId

        FOR EACH ingredient
            INSERT the ingredient, give it RecipeId as a foreign key
      
        FOR EACH instruction
            INSERT the instruction, give it RecipeId as a foreign key
       

            

That's it. From here you can select with joins - To form what we're seeing above, you might need to do 3 seperate queries and merge them together into a record object on the python side to reproduce the original structure.