Is it possible to store JSON array data in a MySQL database?

Question

I have a unique situation. My site users can post articles to other users for viewing, but they can restrict who can view articles by age and by country. My problem is that instead of 250 entries (1 for each country that links to the article) I found it necessary to store all 250 countries (or depending on which countries they want to be visible to other users) in JSON format in text field in the database. Thus, I will need only one entry per article. I'm not sure if the performance will suffer terribly? The site will employ 1-2 million users, and the number of articles submitted for review will also be quite large. The only “processing” that will be done is that each user country is stored in the database and it will be checked against the country array for the article to see if that user is allowed to view this article.

What do you guys think? I thought a lot of 250 entries for each article?

Answer 1

I think storing data in a lookup table is perfectly acceptable. This gives you much more freedom in the future if something changes, and as long as you index tables well, performance will not be overly tolerated.

Mysql handles billions of records with ease. Yes, you will need to make sure that you monitor the integrity of the data, but adding a column to the lookup table compared to changing the object stored in each individual record suddenly seems a lot easier.

Just make sure that you store the data correctly - as you are not repeating information that does not need to be repeated. Store the countries in one table and a simple identifier in the lookup table that refers to it.

Answer 2

In short, I would say that storing Json Data in a column in a relational database is fine if you are not going to query data based on that column.

If you need to search for data based on this column, there will be tremendous performance associated with the need to parse json before excluding data, so this will be no no.

We encountered this problem when I worked on a smaller scale, and storing json properties in the database worked, so as not to increase the complexity of the database for unexplored properties.

Answer 3

Instead, I would use another table for this data and create a unique column to match it.

Answer 4

You have a country table and an article table. I would make a third “country article” consisting only of indices that must match. After all, Mysql is relational. If you are worried about performance, a guideline.

Answer 5

One option would be to use a bit field to represent your countries using MySQL bit or binary . This will allow you to store information for each tournament in one bit, which ultimately will require 32 additional bytes per record (8 bits / byte * 32 bytes = 256 bits).

I'm not sure, but it may even be possible to request the use of bitwise operations, which can be very fast.

Answer 6

You can have a separate table for countries and have your identifiers stored in the article table.

You can have options for all countries, Asia, Europe, North America, South America, etc. that are stored in your countries.

Answer 7

JSON will not allow the DBMS to validate the countries that you want to keep. This is basically opaque text, so the DBMS cannot provide referential integrity (foreign keys).

And even if you don’t need to request countries (this is quite large if), you will at least have to parse JSON before checking for a specific country.

JSON may be a good match for hierarchical data, but it is a simple set (a country is either a set element or not), which can be well represented by a separate ARTICLE_COUNTRY join table, which can then be supported and efficiently searched:

This connection table will only refer to countries in which this article is available. If most of the articles are available from most countries, you can even change the value of the join table and store only “forbidden” countries, thereby reducing the total number of rows.