Create an SQL table with arrays

Question

I have a question! I don't really know how to explain it, so I'll illustrate it!

I know how to make tables on SQL that will have this shape :

var data = {
  "news" :[
    {
      "title" : "I am the title 1",
      "number of views" : 28,
      "text" : "Here is the text",
      "date of publication" : "13 March"
    },
    {
      "title" : "I am the title 2",
      "number of views" : 57,
      "text" : "Here is the text",
      "date of publication" : "24 May"
      ]
    },
    {
      "title" : "I am the title 3",
      "number of views" : 74,
      "text" : "Here is the text",
      "date of publication" : "27 May"
    }
  ]
};

Now I would like to be able to create a table that will have this shape :

var data = {
  "staff member" :[
     {
      "First name" : "John",
      "Last name" : "Doe",
      "schedules" : [
          "Friday : 14h - 22h",
          "Saturday : 10h - 19h",
          "Sunday : 10h - 19h"
      ]
    },
    {
      "First name" : "Amelia",
      "Last name" : "Doe",
      "schedules" : [
          "Friday : 18h - 23h",
          "Saturday : 9h - 17h",
          "Sunday : 8h - 20h"
      ]
    }
  ]
};

See the difference? Sorry if it's a little confusing, but I don't really know what to call it!

I think it can be done by creating another table and finding myself with a common id for the first name, the last name on one side and the schedules on the other. But there must be a way to do it more simply, right?

So my question is simple, is it possible? And if so, how?

I did it in JSON, but now I'd like to switch to MySQL, and I don't know how to do that!

Thank you for your answers.

EDIT :

The issue is about schedules and staff members. So I have to create 2 different tables and link the IDs between them to have a staff members with his schedules?

I think you've got the right idea. First try to do it on paper, and then work it out in code. You will have to get the id of the staff member you just inserted into the database, and use that id in the schedules table to link a schedule to a staff member. — KIKO Software
– KIKO Software, Commented Aug 3, 2019 at 20:32
Tables in MySQL represent relations, not JSON objects, so your whole question doesn't make much sense. You should create a table for the staff members and a table for the schedules and link them via a linking table. — sticky bit
– sticky bit, Commented Aug 3, 2019 at 20:33
There is no relationship between the two codes (the code with news was just an example to show my problem), the question was rather, in relation to the schedules. But as said above by @kiko-software, I have to make 2 different tables and link them? — Sami Lafrance
– Sami Lafrance, Commented Aug 3, 2019 at 20:37

daShier · Accepted Answer · 2019-08-03 21:06:23Z

1

SQL does not support the concept of "sub tables" the way you are showing in your code. Instead you should create two separate tables similar to:

    CREATE TABLE staff_member (
        staff_member_id int,
        First_name varchar,
        Last_name varchar
    )

    CREATE TABLE schedules (
       staff_member_id int,
       day_of_week varchar,
       start_hour int,
       end_hour int
    )

Then you can select the data using:

   SELECT m.First_name, m.Last_name, s.day_of_week, s.start_hour, s.end_hour
      FROM staff_member m 
      LEFT JOIN schedules s ON m.staff_member_id = s.staff_member_id
      ORDER BY m.Last_name, m.First_name, s.day_of_week, s.start_hour, s.end_hour

Of course, you can add a WHERE clause to select specific information.

answered Aug 3, 2019 at 21:06

daShier

2,1062 gold badges10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create an SQL table with arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related