MySQL increment value in a text field

Question

Say I have a text field with JSON data like this:

{
    "id": {
        "name": "value",
        "votes": 0
    }
}

Is there a way to write a query which would find id and then would increment votes value?

I know i could just retrieve the JSON data update what I need and reinsert updated version, but i wonder is there a way to do this without running two queries?

Answer 1

UPDATE `sometable`
    SET `somefield` = JSON_REPLACE(`somefield`, '$.id.votes', JSON_EXTRACT(`somefield` , '$.id.votes')+1)
    WHERE ...

Edit

As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON documents.

JSON_EXTRACT will allow you to access a particular JSON element in a JSON field, while JSON_REPLACE will allow you to update it.

To specify the JSON element you wish to access, use a string with the format

'$.[top element].[sub element].[...]'

So in your case, to access id.votes, use the string '$.id.votes'.

The SQL code above demonstrates putting all this together to increment the value of a JSON field by 1.