Question 20528 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I Store JSON List in SQL Column – Mysql

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.

Tags:

2

Answers


  1. 0

    Yes you can
    Serialize to JSON string

    Newtonsoft.Json.JsonConvert.SerializeObject(list);

    and deserialize

    list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<string>>(jsonArray);
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search