Append value to JSON decode array parameter stored in MySQL

Question

Im trying to work out how to append a zero to a specific JSON decoded array value for multiple records stored in a MySQL table according to some conditions.

for example, for table 'menu', column 'params'(text) have records containing JSON decoded arrays of this format:

{"categories":["190"],"singleCatOrdering":"","menu-anchor_title":""}

and column 'id' has a numeric value of 90.

my goal is to add a zero to 'categories' value in menu.params whenever (for example) menu.id is under 100.

for this records the result being

{"categories":["1900"],"singleCatOrdering":"","menu-anchor_title":""}

so im looking for a SQL Query that will search and find the occurrences of "categories": ["999"] in the Database and update the record by adding a zero to the end of the value.

this answer is partially helpful by offering to use mysql-udf-regexp but its referring to REPLACE a value and not UPDATE it. perhaps the REGEXP_REPLACE? function will do the trick. i have never used this library and am not familiar with it, perhaps there is an easier way to achieve what i need ?

Thanks

Answer 1

If I understand your question correctly, you want code that does something like this:

var data = {
    "menu": {
        "id": 90,
            "params": {
            "categories": ["190"],
                "singleCatOrdering": "",
                "menu-anchor_title": ""
        }
    }
};

var keys = Object.keys(data);
var columns;
for (var ii = 0, key; key = keys[ii]; ii++) {
    value = data[key];
    if (value.id < 100) {
        value.params.categories[0] += "0";
        alert(value.params.categories[0]);
    }
}

jsFiddle

However, I am not using a regular expression at all. Perhaps if you reword the question, the necessity of a regex will become clearer.