best way to store bunch of json files in to database

Question

I have 26 json files from an open source (which have meanings for words)

They are named , a.json,b.json,c.json........z.json

I am looking for a suggestion on how can i store this data in DataBase (MySQL) using flask sql alchemy /pythonic way such that the the data is as:

{
    "a cappella": {
        "word": "a cappella",
        "wordset_id": "5feb6f679a",
        "meanings": [
            {
                "id": "492099d426",
                "def": "without musical accompaniment",
                "example": "they performed a cappella",
                "speech_part": "adverb"
            },
            {
                "id": "0bf8d49e2e",
                "def": "sung without instrumental accompaniment",
                "example": "they sang an a cappella Mass",
                "speech_part": "adjective"
            }
        ]
    },
    "A.D.": {
        "word": "A.D.",
        "wordset_id": "b7e9d406a0",
        "meanings": [
            {
                "id": "a7482f3e30",
                "def": "in the Christian era",
                "speech_part": "adverb",
                "synonyms": [
                    "AD"
                ]
            }
        ]
    },.........
}

Is the idea of having a single table for whole data holds good or a table for each json file ?

Any guiding links would be much helpful, TIA

https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-manipulation/how-to-store-json-in-mysql/ — GiovaniSalazar, Dec 26 '19 at 17:37
MySQL supports JSON as a data type: https://dev.mysql.com/doc/refman/5.7/en/json.html — foglerit, Dec 26 '19 at 23:18

score 1 · Answer 1 · answered Dec 26 '19 at 22:20

1

If the structure of each of the files is different, save yourself any effort and put them in a Document DB (e.g. MongoDB).

If the structure of the JSON data is the same for each "word" (which it looks like it is) extract the data and store it in tables: Word, Meaning, and Synonym

The tables would be keyed on word, or you could add a surrogate key:

Word: [word, wordset_id]
Meaning: [word, meaning_id, def, example, speech_part
Synonym: [word, synonym_word]

answered Dec 26 '19 at 22:20

Tony

9,672
3
47
75

hey thanks for the info(made me understand to draft something ), any example of this kind on doing with mongo? , I have tried with the approach of mysql but it is a much more process – KcH Dec 27 '19 at 10:57
1

@Codenewbie - There are plenty of examples of loading JSON in to mongo, here's one: https://stackoverflow.com/questions/19441228/insert-json-file-into-mongodb – Tony Dec 27 '19 at 11:53
Thanks @Tony I just got one and working on that , I would refer this too – KcH Dec 27 '19 at 11:55

best way to store bunch of json files in to database

1 Answers1