0

I am using mysql 5.6 and as a result dont have access to existing JSON functions in mysql - so looking for some clever workaround ideas...

I have a Story table in my mysql database that has a field called "tags". Tags is just a string field stores a JSON array of tagIds eg [2,5,6] which relate back to another table called tag. This allows me to easily reconstruct tag information in my UI by combing the json array and the tag data.

However now I need to update those tags in the database and its proving challenging. For example if I want to delete a tag with id = 6 I then need to remove it from all the places its referenced in stories ie 

update story set tags = {new tag list excluding 6} where tags used to contain 6

I am unsure how to correctly work with these JSON blobs in mysql.

Likewise in some cases I want to replace a tag with another tag id, ie

update story set tags = {new tag list excluding 6 and including new tag id} where tags used to contain 6

I could retrieve them all and do individual processing from within the server ie Nodejs and update each record individually but seems very inefficient.

What is the correct or desired approach here?

Gotts
  • 2,274
  • 3
  • 23
  • 32

1 Answers1

1

The "correct" approach would be to create another table story_tags (id, tag_id, story_id) with one-to-many relationship with story table. Then you could simply do:

select tag_id from story_tags were story_id = xx

and

delete from story_tags were tag_id = xx

However, this requires redesigning the data model of your app.

A less optimal approach is to create a class handling the tag logic in your app in the way you described it. For this solution, you need to assess how often updates/deletes happen and how significant drop on performance is acceptable. A scan/update of a few thousand rows at the time should not have a too severe impact on overall performance.

Eriks Klotins
  • 4,042
  • 1
  • 12
  • 26
  • Thanks. my decision to use the json blob in the first place was to avoid a story_tag relationship table. So still pushing to see if I can find a clever solution to handle this. For eg to query for a specific tag i created a view which extracts the json array into a searchable string but now updates are proving challenging. – Gotts May 11 '20 at 14:38
  • Personally, I would use JSON blobs only for static/caching purposes and never in joins/filters. See answers here: https://stackoverflow.com/questions/33660866/native-json-support-in-mysql-5-7-what-are-the-pros-and-cons-of-json-data-type – Eriks Klotins May 12 '20 at 04:06