0

I have a table with column named "Description". Value of each record has json array format. Suppose value of two records are:

[
    {"languageCode":"fr","description":"France","isDefault":true},
    {"languageCode":"cs","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false}
]
and

[
    {"languageCode":"cs","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false},
    {"languageCode":"fr-FR","description":"France","isDefault":true}
]

I want update all records of table and update value of languageCode if it is "fr" and "cs" to "fr-FR" and "cs-CZ"

Expected:

[
    {"languageCode":"fr-FR","description":"France","isDefault":true},
    {"languageCode":"cs-CZ","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false}
]

and

[
    {"languageCode":"cs-CZ","description":"Czech","isDefault":false},
    {"languageCode":"it-IT","description":"Italian","isDefault":false},
    {"languageCode":"fr-FR","description":"France","isDefault":true}
]

I tried Update table and set Description column but I don't know how to find languages code (fr, cs) and update it.

Updated: I change my approach by use REPLACE instead through json But I think Json still is a good approach.

Here is my script

UPDATE applications
SET Description = REPLACE ( REPLACE ( Description, '\"languageCode\":\"fr\"', '\"languageCode\":\"fr-FR\"'), '\"languageCode\":\"cs\"', '\"languageCode\":\"cs-CZ\"')
Babatos
  • 21
  • 1
  • 5
  • Did you take a quick look at the MySQL Documentation on how to do this? [Like JSON Function](https://dev.mysql.com/doc/refman/5.7/en/json-functions.html) – RiggsFolly Jan 09 '23 at 10:32
  • I just read it. But I got problem with an array of object in MySQL. I don't know how to query value of languagecode and update it. – Babatos Jan 10 '23 at 04:17
  • You may consider this is not the best way to store data in a relational database and decide it would be easier to refactor this to conform to the relational model. It would make maintaining this data so much easier if you want to do it in MySQL itself – RiggsFolly Jan 10 '23 at 11:16

0 Answers0