ShenanigansS
Windmill•8mo ago
Shenanigans

Cannot convert between String and JSONB in PostgreSQL

Given the following json object below, 
["ABC","DEF","GHI"]

I would like to update a record in my postgresql database of type jsonb using a postgresql script such as below:
UPDATE my_table SET 
  my_jsonb_column = 
    CASE
      WHEN $33::JSONB IS NULL OR $33::JSONB = '[]'::JSONB THEN COALESCE(my_jsonb_column, '[]'::JSONB)
      ELSE COALESCE(my_jsonb_column, '[]'::JSONB) || $33::JSONB
    END,
--- ... more code here
WHERE my_id = $1::INT RETURNING *


I am unfortunately unable to get this to work as Windmill interprets my inputs as either an actual JSON object or a string if wrapped in quotes.

i.e. If my input is "['ABC','DEF','GHI']", I get the following error message below

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: error serializing parameter 32: cannot convert between the Rust type `alloc::string::String` and the Postgres type `jsonb`: cannot convert between the Rust type `alloc::string::String` and the Postgres type `jsonb` @pg_executor.rs:129:24"
    }
}


Otherwise, I get the following error:
{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nUnsupported type in query: Array [String(\"ABC\"), String(\"DEF\"), String(\"GHI\")] and signature \"jsonb\""
    }
}
Was this page helpful?