0

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

Community
  • 1
  • 1
buzibuzi
  • 724
  • 3
  • 15
  • 27

1 Answers1

0

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.

James Newton
  • 6,623
  • 8
  • 49
  • 113
  • Hi @James,thanks, but im looking for a SQL Query that will search and find the occurances of "categories": ["999"]in the JSON array and add a zero to the end of the number by updating the record...hope its more clear – buzibuzi Jun 04 '15 at 06:52
  • An SQL query will allow you to insert and extract data from your database. It will not allow you to manipulate the data. For that, you need to use a user-defined function, or server-side code like PHP. if you are storing JSON strings in your database, perhaps a NoSQL database might be a better choice. Can you explain the big picture, so that we can understand what problem you are working to solve? – James Newton Jun 04 '15 at 12:24
  • Hi @James Newton , the big picture is this: i have records that have this JSON encoded data, all i want to do is update them so that "categories" value has another zero after it. i would have thought you can isolate this value in the JSON string using REGEX and change it using MySQL UPDATE. too complicated ? – buzibuzi Jun 08 '15 at 08:28