CODEWITHSUNDEEP
mysqlsqljson
Bruno Andrade
Bruno Andrade

Reputation: 595

From JSON Multidimensional array to SQL

I have this JSON structure and need to pass it to a database. But I do not know if the structure I made is the best possible code has some repetitions that I do not think this is correct and I think it is unnecessary.

JSON:

{
    "info": [
        {
            "atriz": "Sandra Bullock",
            "atriz-id": "162",
            "atriz-slug": "sandra-bullock",
            "carreira": {
                "id": "264",
                "inicio": "08/10/91",
                "final": "16/08/18",
                "videos": [
                    {
                        "id": "2930500",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "Sb6y1oTwd",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "drama",
                        "upload": {
                            "send": true,
                            "id": "118840448",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    },
                    {
                        "id": "2930499",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "R2G0GhTwF",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "acao",
                        "upload": {
                            "send": true,
                            "id": "118840554",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    }
                ]
            }
        },
        {
            "atriz": "Jennifer Lawrence",
            "atriz-id": "207",
            "atriz-slug": "jennifer-lawrence",
            "carreira": {
                "id": "263",
                "inicio": "02/01/88",
                "final": "09/08/18",
                "videos": [
                    {
                        "id": "2930443",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "DNJNYHWh",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "drama",
                        "upload": {
                            "send": true,
                            "id": "118844113",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    },
                    {
                        "id": "2930442",
                        "titulo": "Lorem ipsum habitant commodo cubilia eget blandit",
                        "desc": "Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis",
                        "code": "OqieXJHwh",
                        "source": "http://URL",
                        "download": true,
                        "link": "http://URL",
                        "duration": null,
                        "post_status": "pending",
                        "category": "comedia",
                        "upload": {
                            "send": true,
                            "id": "118844112",
                            "url": "http://URL",
                            "status": "finished"
                        }
                    }
                ]
            }
        }
    ]
}

I do not understand much of SQL so I used an online convert and it returned me this:

SQL:

CREATE TABLE IF NOT EXISTS infos (
    `info_atriz` VARCHAR(17) CHARACTER SET utf8,
    `info_atriz_id` INT,
    `info_atriz_slug` VARCHAR(17) CHARACTER SET utf8,
    `info_carreira_id` INT,
    `info_carreira_inicio` DATETIME,
    `info_carreira_final` DATETIME,
    `info_carreira_videos_id` INT,
    `info_carreira_videos_titulo` VARCHAR(49) CHARACTER SET utf8,
    `info_carreira_videos_desc` VARCHAR(78) CHARACTER SET utf8,
    `info_carreira_videos_code` VARCHAR(9) CHARACTER SET utf8,
    `info_carreira_videos_source` VARCHAR(10) CHARACTER SET utf8,
    `info_carreira_videos_download` VARCHAR(4) CHARACTER SET utf8,
    `info_carreira_videos_link` VARCHAR(10) CHARACTER SET utf8,
    `info_carreira_videos_duration` INT,
    `info_carreira_videos_post_status` VARCHAR(7) CHARACTER SET utf8,
    `info_carreira_videos_category` VARCHAR(7) CHARACTER SET utf8,
    `info_carreira_videos_upload_send` VARCHAR(4) CHARACTER SET utf8,
    `info_carreira_videos_upload_id` INT,
    `info_carreira_videos_upload_url` VARCHAR(10) CHARACTER SET utf8,
    `info_carreira_videos_upload_status` VARCHAR(8) CHARACTER SET utf8
);
INSERT INTO infos VALUES
    ('Sandra Bullock',162,'sandra-bullock',264,'1991-10-08 00:00:00','2018-08-16 00:00:00',2930500,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','Sb6y1oTwd','http://URL','True','http://URL',NULL,'pending','drama','True',118840448,'http://URL','finished'),
    ('Sandra Bullock',162,'sandra-bullock',264,'1991-10-08 00:00:00','2018-08-16 00:00:00',2930499,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','R2G0GhTwF','http://URL','True','http://URL',NULL,'pending','acao','True',118840554,'http://URL','finished'),
    ('Jennifer Lawrence',207,'jennifer-lawrence',263,'1988-01-02 00:00:00','2018-08-09 00:00:00',2930443,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','DNJNYHWh','http://URL','True','http://URL',NULL,'pending','drama','True',118844113,'http://URL','finished'),
    ('Jennifer Lawrence',207,'jennifer-lawrence',263,'1988-01-02 00:00:00','2018-08-09 00:00:00',2930442,'Lorem ipsum habitant commodo cubilia eget blandit','Suscipit augue dictum ultrices ultricies aliquam mattis nostra taciti sagittis','OqieXJHwh','http://URL','True','http://URL',NULL,'pending','comedia','True',118844112,'http://URL','finished');

Is this really the best possible structure for the database? This repetition in these values for example ('Sandra Bullock', 162, 'sandra-bullock', 264, '1991-10-08 00:00:00', '2018-08-16 00:00:00') does not have how to optimize this?

Upvotes: 0

Views: 149

Answers (1)

Andrey_Murgin
Andrey_Murgin

Reputation: 58

The answer depends on your goals you try to achieve. And also it is important to know in what way you will use this data further.

  1. The structure is not very good. You will have several records (their count may be huge - depends on JSON) for the only JSON item. I'm not sure this will be convenient to retrieve data in such way. It is preferred to use relational model:

Generally, each table/relation represents one "entity type" (such as customer or product). The rows represent instances of that type of entity (such as "Lee" or "chair") and the columns representing values attributed to that instance (such as address or price).

So, in your case, I would use more than 1 table.
-- videos with columns {id, titulo, desc, etc.}
-- carreira with id of related "atriz" record as a separate column and other columns {id, inicio, final}
-- carreira_videos - separate table which stores carreira-video relations. The rable may contain only two columns: carreira_id and video_id. For example, "Sandra Bullock" item in your example JSON has carreira with two videos - so, in carreira_videos table you will have two records:

carreira_id | video_id
264 | 2930500
264 | 2930499

-- atrizs with columns {atriz, atriz-id, atriz-slug} and separate column related carreira_id.

  1. Actually, you're right about a lot of data repetition in your example. In case your JSON has more than just two items in "info" node, then there are more data duplicates. If you will use relational model (as it is preferred in SQL-based databases) and optimal schema such as described above you will avoid repetitions.

  2. You need to study basics of relational databases and SQL before you go further. If you do not understand them then it is not real to create optimize design.
    https://www.w3schools.com/sql/
    https://searchdatamanagement.techtarget.com/definition/relational-database

  3. Actually, if you want to store JSON itself then you may not need SQL-based database. You should look at NoSQL approaches and DBs https://en.wikipedia.org/wiki/NoSQL
    https://www.quackit.com/json/tutorial/list_of_json_databases.cfm

All depends on your goals.

Upvotes: 2

Related Questions