Trying to update a JSON column. QUERY1 works fine, but QUERY2 which has a variable seems to update the amount field to 'null'. Tried using CAST as well but still the column updates to 'null'. I am sure it is something to do with the varibale usage.
QUERY1
SET @uuid = '0ab09975-9bdd-4af8-8e76-57a65bf5c53f';
update `json`
set `attributes` = JSON_SET(`attributes`,"$.analyzeResult.documents[0].fields[0].InvoiceTotal.valueCurrency.amount", 1504.55) where `uuid` = uuid;
QUERY2
SET @uuid = '0ab09975-9bdd-4af8-8e76-57a65bf5c53f';
SET @in_amount = 1504.55;
update `json`
set `attributes` = JSON_SET(`attributes`,"$.analyzeResult.documents[0].fields[0].InvoiceTotal.valueCurrency.amount", in_amount) where `uuid` = uuid;
Session variables should always include their @, both at setting and use.
So just correct your query to:
SET @uuid = '0ab09975-9bdd-4af8-8e76-57a65bf5c53f';
SET @in_amount = 1504.55;
update `json`
set `attributes` = JSON_SET(`attributes`,"$.analyzeResult.documents[0].fields[0].InvoiceTotal.valueCurrency.amount", @in_amount) where `uuid` = @uuid;
(with an @ to @in_amount and one to @uuid too)
The fact that your first query worked without an @ on uuid (where `uuid` = uuid;) is that your table has a column named uuid,
so while you meant where column(uuid) = variable(uuid);,
uuid without an @ referred to the column uuid and you were in fact writing where column(uuid) = column(uuid);,
which is true for all rows of your table (with uuid is not null),
which mean you were overwriting your whole table with the same value.
You can see some tests on this fiddle.