0

I'm trying to create a website using asp.net core, entity framework and MySQL. But I'm stuck in one point. Storing list variable in SQL column. I've a database model that must store List in it. I've read questions about storing List in SQL but most of answer was saying not to do that. I'm curious can i serialize my List to JSON and store it in one column ?

Like;

new List<string>(){"data1", "data2"}

to

"["data1", "data2"]"

Is there a drawback to do that ? Or are there any other solutions ? I'm open to all suggestions.

Ertugrul Sungur
  • 102
  • 7
  • Does this answer your question? [Storing JSON in database vs. having a new column for each key](https://stackoverflow.com/questions/15367696/storing-json-in-database-vs-having-a-new-column-for-each-key) – Leandro Bardelli Mar 08 '23 at 12:20
  • No, my model is not like this. In this link, he or she want to store all variables in one column. I've other columns already like id and other stuff. I'm just wondering doing that in one column is okay or not. – Ertugrul Sungur Mar 08 '23 at 12:32
  • https://stackoverflow.com/questions/4160732/nosql-vs-relational-database – Leandro Bardelli Mar 08 '23 at 12:39

2 Answers2

5

Yes, you can store JSON in MySQL. MySQL has a JSON type since version 5.7, but they have a lot if missing features until at least version 8.0.17, and new fixes to JSON are still being introduced.

You asked about drawbacks. Yes, there are many.

  • The JSON format of data typically uses 2-3 times as much space as storing the same data in normal rows and columns, so you will spend more on storage and it will fill up faster.

  • If you only store an array and fetch the whole array later, that's easy. But many people are tempted to use elements of JSON arrays or objects in SQL expressions. This makes queries much more complex, hard to code, and hard to optimize.

    You should read some of the past questions I've answered about MySQL in JSON, and see the types of trouble some people cause trying to use JSON — when it would be so simple if they used normal rows and columns. From what I've seen of the ways people abuse JSON, I'm leaning toward saying it's the most ill-advised feature to be added to any relational database.

  • You will need to study the set of JSON functions provided by MySQL if you try to treat JSON arrays as collections of discrete elements. You will have to learn how to build arrays, search arrays, and take slices of arrays using these functions.

  • Searching JSON arrays for a particular element cannot be optimized with a traditional index. You must use a multi-valued index.

You might also like my presentation How to Use JSON in MySQL Wrong.

In your example of storing a simple array, it would be better for storage efficiency, query complexity, and optimization if you stored the values one per row in a dependent table. This is the correct way to store multi-valued attributes in a relational database.

Using JSON is just like any other form of denormalization. It can be an advantage, but only for a specific query. It comes at the disadvantage for all other queries.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

Yes you can Serialize to JSON string

Newtonsoft.Json.JsonConvert.SerializeObject(list);

and deserialize

list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<string>>(jsonArray);
Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
Fatehi_Alqadasi
  • 548
  • 5
  • 14