Help with bulk update

App Building
, ,
1

Hi.

Trying to figure what am I doing wrong.
I have a JSON field in a pgsql in a table that's suppose to get populated with an editable 'tags' field that gets it's option list from a field from another table. when I'm trying to bulkUpdate the table with {{sensor_table.changesetArray}} the preview of the result of the query looks like this:

recordsToUpdateWith
{ "metrics": [ "Messages", "Ambient Light", "ORP", "Dissolved Oxygen" ], "id": "4" }

(upload://rFXbB3yBhHxQPLb7gzZYJW7KhtC.jpeg)

But when I run the query it throws an error:
update "sensor_db" set "metrics" = $1 where "id" = $2 - invalid input syntax for type json
Query ran successfully

Close

    • statusCode:422
    • error:"Unprocessable Entity"
    • message:"update "sensor_db" set "metrics" = $1 where "id" = $2 - invalid input syntax for type json"
    • data:null
    • :arrow_forward:

queryExecutionMetadata:{} 5 keys
* estimatedResponseSizeBytes:170
* resourceTimeTakenMs:59
* isPreview:false
* resourceType:"postgresql"
* lastReceivedFromResourceAt:1685048529109

Screen Shot 2023-05-25 at 5.05.52 PM
Screen Shot 2023-05-25 at 5.05.52 PM1920×1114 114 KB

2

Hey @Scottsky!

At the moment, changesetArray doesn't seem to be passing multi-tag columns in a way that works with bulk updates. Can you try using the following?

{{ sensor_table.changesetArray.map(row => ({...row, metrics: JSON.stringify(row.metrics)})) }}

That should take your metrics column and convert it to a JSON string which can be passed to your DB - let me know if that works!

1 Like
3

Hi,
Thank you for your reply, Kabirdas.
Unfortunately the query returned a 422 error:

  • statusCode:422
  • error:"Unprocessable Entity"
  • message:"Data must be an array."
  • data:null
  • :arrow_forward:

queryExecutionMetadata:{} 5 keys

  • estimatedResponseSizeBytes:96
  • resourceTimeTakenMs:2
  • isPreview:false
  • resourceType:"postgresql"
  • lastReceivedFromResourceAt:1686063445383

Although Preview returns this:

recordsToUpdateWith

{ "metrics": "["Wind Speed","Wind Direction","Air Temperature"]", "id": "7" }

Feels like I messed up with designing the architecture the first place.
Thought It would be a good idea to have metrics that can be recorded by a specific sensor to come from a separate table of metrics where I can have some descriptions and scales stored as well. But it seems it wade it way too complicated for me.
Would you suggest me trying some other approach altogether or this is salvageable?

4

:thinking: if you're using a bulk update query I'd expect an array like

[{ "metrics": "["Wind Speed","Wind Direction","Air Temperature"]", "id": "7" }]

instead of

{ "metrics": "["Wind Speed","Wind Direction","Air Temperature"]", "id": "7" }

Doing some testing on my end I see a similar error when that's not the case:

vs

Would you mind sharing a screenshot of the query you're using?

5

Had the same issue, your fix worked like a charm. Thanks Kabirdas!

6