I need to set/update JSON array in MYSQL table from Node and I have this query, that throws a 'Invalid JSON path expression' error. For example I want to find object with key '2022-01-03' and if it exist update its value to 'O 08:00'

UPDATE allemployees SET schedule = JSON_SET(schedule, '$.2022-01-03', 'O 08:00') WHERE name_cyr = 'John Doe'

My JSON in the table looks like this:

[{"2022-01-03": "H 08:00"}, [{"2022-01-04": "H 08:00"}] ]

CodePudding user response：

UPDATE allemployees
SET schedule = JSON_SET(schedule, '$[0]."2022-01-03"', 'O 08:00') 
WHERE name_cyr = 'John Doe';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c59600049b4dfc1675c444a6da578bb

1. The path "2022-01-03" contains dashes and must be enclosed with doublequote chars.

2. The value to be set is not upper-level value, it is a component of definite array element.