How to BulkUpdate a Field in a JSON column

Data Service
1

I’m trying to add a field to all rows of a JSON column that don’t currently have the field. I have tried the javascript SDK, curl, the console, every thing I can think of and can’t get the column to change. The following syntax looks to me exactly like the documented way to do this:

The table is called “compliance”, the JSON column is called “values” and the json value is called “expires”. “be” is an instance of backendless sdk.

      const whereClause = "step = '31bc1e7063634d17931d29d08a3c43af' and userjoin.status = 0 and values->'$.expires' IS NULL";
      const rec = {
        values: be.JSONUpdateBuilder.SET().addArgument('$.expires', 1767225600000)
      };
      be.Data.of("compliance").bulkUpdate(whereClause, rec).then(result => {
        console.log("Updated", result, "records");
      }).catch(err => console.error(err));

The error is consistently: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', ‘$.expires’, 1767225600000) where (udt.compliance.step = ‘31bc1e7063634d17’ at line 1

What am I doing wrong? Thank you

2

Hello @Kelly_Oglesby,

It looks like you are doing this correctly.

The most likely issue is that your JSON column is named values, but VALUES is a reserved keyword in MySQL.

I would suggest trying the following:
1. Rename the values column to something like jsonValues or something similar
2. Run the same bulkUpdate again

Regards,
Alexander