Is there a way to store objects in a mySQL database?

Question

I have a database using MySQL called "users". In it are two tables. "first" and "second".

I take the users information from a form using JavaScript as an array of objects.

e.g. let usersInformation = [{"name":"james", "age":"30"}]

How can I easily store each array of objects?

In the past I have created columns e.g. "name" and then stored the value of "name" in that column.

Is there a way to store objects in a MySQL database. I looked up the term ORM and thought that may be of help.

Answer 1

You can use the JSON data type of MySQL.

mysql> create database user; 

mysql> use user
# Create a table with a json data type
mysql> create table user (json JSON); 

# Insert an array into the field
mysql> insert into user(json) values ('["first", "second", "third", "4th"]'); 

# Insert an object
mysql> insert into user(json) values('{"name": "Levi", "last": "Jr"}');

mysql> select * from user;
+-------------------------------------+
| json                                |
+-------------------------------------+
| ["first", "second", "third", "4th"] |
| {"last": "Jr", "name": "Levi"}      |
+-------------------------------------+
2 rows in set (0.00 sec)

You can use the JSON_EXTRACT to get some info from the field and filter it in the WHERE clause.

Here is how to use it: JSON_EXTRACT([field], [expression])), [expression] being how you going to extract the info from the field.

Ex.:

mysql> select * from user where JSON_EXTRACT(user.json, '$.name') = 'Levi';
+--------------------------------+
| json                           |
+--------------------------------+
| {"last": "Jr", "name": "Levi"} |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from user where JSON_EXTRACT(user.json, '$[0]') = 'first';
+-------------------------------------+
| json                                |
+-------------------------------------+
| ["first", "second", "third", "4th"] |
+-------------------------------------+
1 row in set (0.00 sec)