Storing JSON in MySQL database?

Question

Is it acceptable to store JSON data in a MySQL table row? I need to store arrays in a mysql database. The problem is, i don't know how many columns i will need for each user. So I thought to store JSON in one row named array for exemple. Is this the best way?

Edit:

Also, I am using text as table column type.

If you know for sure that you never, ever need to do anything with the JSON data (like query for specific properties inside it), why not... but it's not really good practice in any case. — Pekka, Mar 13 '13 at 20:02
Why would you need a different amount of columns for different users? — beny23, Mar 13 '13 at 20:02
@user1947561: you wouldn't store a new item in a new column, but have an `user_items` table with a `user_id` foreign key... — beny23, Mar 13 '13 at 20:13

score 12 · Accepted Answer · answered Mar 13 '13 at 20:04

12

Yes, it's a very good idea to use mysql as a key-value store, in fact facebook does for some uses.

CREATE TABLE `json` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `data` blob NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The above table-structure will get you far. It's very easy to shard or cluster.

Edit: The point here is to use PHP, Ruby etc to handle the json/data. You do SELECT ..., do your edits, then INSERT ... ON DUPLICATE KEY UPDATE ....

answered Mar 13 '13 at 20:04

Gustav

2,902
1
25
31

The OP may need to query some of the data in the JSON, so this may *not* be the best idea. – gen_Eric Mar 13 '13 at 20:08
6

Well, it's not in the question... – Gustav Mar 13 '13 at 20:17
1

Yeah, I know. He added that as a comment to the question. – gen_Eric Mar 13 '13 at 20:17
This works fine if the JSON can be stored as a key-value pairs without any children nodes. What if the JSON heirarchy is deeper? ex: fields have children which in-turn have children objects? which is not feasible to store in a RDBMS. – Eswar Rajesh Pinapala Mar 13 '13 at 20:48
@EswarRajeshPinapala That does not matter in this approach, it's all just generic data. – Gustav Mar 13 '13 at 20:52
Makes sense If the data field holds the entire JSON. However, querying the JSON nodes may not be possible. – Eswar Rajesh Pinapala Mar 13 '13 at 21:02
@EswarRajeshPinapala I hope it is possible to query json data, using LIKE operator, provided the field contains only the json string. – shasi kanth Sep 07 '15 at 10:46

score 2 · Answer 2 · answered Mar 13 '13 at 20:06

Storing more than one piece of data in a relational database field is generally wrong. It is possible to think of cases where it would be acceptable, but not for storing an entire user.

If a using a relational database structure is not sufficient you may want to look at a NoSQL database inestead of MySQL.

score 0 · Answer 3 · answered Mar 13 '13 at 20:03

0

It's not a problem. mySQL can already handle most of the brackets and funky characters in their text field.

answered Mar 13 '13 at 20:03

apollosoftware.org

12,161
4
48
69

score 0 · Answer 4 · answered Mar 13 '13 at 20:05

I think it is not. What about four months later you decide add a new attribute to your entity or remove some attributes from your entity? How will you parse your old json contents? If you dont know how many columns you will need in your table, you should think in a different way and maybe create a dynamic structure like using user_column table

Storing JSON in MySQL database?

4 Answers4