How to correctly escape asterisk inside of JSON_SET() in a MySQL query?

Question

I have a simple database that stores fields and values in a JSON column to create custom forms using PHP { "Field1": "Value1", "Field2": "Value2" }.

Compulsory fields are sored with an asterisk *: { "Field3*": "Value3" }.

The problem comes when I try to update the compulsory field, I think that's because of escaping issues.

When I try to update the script I should use isn't clear to me.

1. UPDATE Forms SET Form=JSON_SET('Form','$.Field3*','Value3') WHERE Some=attributes;
2. UPDATE Forms SET Form=JSON_SET('Form','$.Field3\*','Value3') WHERE Some=attributes; (with one backslah)
3. UPDATE Forms SET Form= JSON_SET ('Form','$.Field3\\*', 'Value3') WHERE Some=attributes; (with two backslahes)
4. UPDATE Forms SET Form=JSON_SET('Form','$.Field3\\\*','Value3') WHERE Some=attributes; (with three backslahes)

I tried all of these but:

• when a new Form is created the first works,
• when a form is updated and the field Field3* already exists the fourth works,
• when a Form is updated but the field Field3* doesn't exists the fourth creates a field called Field\* that messes everything up.

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax

JSON Path Syntax

...

Names of keys must be double-quoted strings or valid ECMAScript identifiers

This means if your keys contain special characters (such as spaces, digits, punctuation, etc.) that make them not valid ECMAScript identifiers, then you must put the keys in double-quotes in a JSON Path.

Demo:

mysql> set @j = '{"Field3*":"Value3"}';

mysql> select json_set(@j, '$."Field3*"', 'NewValue3') as result;
+--------------------------+
| result                   |
+--------------------------+
| {"Field3*": "NewValue3"} |
+--------------------------+