CODEWITHSUNDEEP
javascriptmysqlobjectorm
Ryan Hinchliffe
Ryan Hinchliffe

Reputation: 87

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

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.

Upvotes: 4

Views: 11853

Answers (3)

Ajith S Nair
Ajith S Nair

Reputation: 11

use JSON.stringify() & JSON.parse()

object to text

const obj = {name: "John", age: 30, city: "New York"}; const myJSON = JSON.stringify(obj);

*JSON can be store in the db with text datatype.*

text to object

const txt = '{"name":"John", "age":30, "city":"New York"}' const obj = JSON.parse(txt);

*text can be taken from the db and pass as object with use of JSON.parse()*

Upvotes: 0

Darshana Pathum
Darshana Pathum

Reputation: 669

Before send your object to the query convert it to json. If you need you can make your table field as MEDIUM TEXT

let usersInformation = [{"name":"james", "age":"30"}];
usersInformation = JSON.stringify(usersInformation);

Then send this to your query.

Upvotes: 1

Levi Leal
Levi Leal

Reputation: 61

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)

Upvotes: 6

Related Questions