Error: Xano/PostgreSQL JSONB Array Issue - File Upload Causes java.util.ArrayList Error

[kathytw]

Hello Softr Teams,

I am encountering an issue where file uploads to a JSONB field in my Xano database result in a java.util.ArrayList error. Other string-based fields work perfectly, but the JSON object from a file upload fails to store correctly.

Error Message

Unable to update record for application with id='xxxxxx' PreparedStatementCallback; bad SQL grammar [UPDATE "xano-****-****-****-**"."public"."*****_*" SET xdo = jsonb_set(xdo, '{"Image_object"}', to_jsonb(?)) WHERE "id" = ?]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

Only the file cannot work properly, the rest of them are all single string which can be stored by xano.

{
  "role": "HR",
  "email": "aaa@gmail.com",
  "user_image": "www.google.com",
  "Image_object": [
    {
      "id": "50acca86-ee4b-4cf8-a12f-8af6cf17ab24",
      "type": "URL",
      "filename": "Red_Dot_Design_Award_1.png",
      "url": "https://app-files-v1.softr-files.com/applications/d641735a-fd2e-4d03-a9f6-b4335702b100/uploads/8d7ab385-5648-448e-b1c1-c551d9630720/Red_Dot_Design_Award_1.png"
    }
  ]
}

What Works:
Single string fields update without any problems. For example:

json

{
  "role": "HR",
  "email": "aaa@gmail.com",
  "user_image": "www.google.com"
}

What Fails:
When I attempt to update the image_object key with a file upload JSON (provided below), it fails. The key image_object is structured as an array of JSON objects:

json

{
  "Image_object": [
    {
      "id": "50acca86-ee4b-4cf8-a12f-8af6cf17ab24",
      "type": "URL",
      "filename": "Red_Dot_Design_Award_1.png",
      "url": "https://app-files-v1.softr-files.com/applications/d641735a-fd2e-4d03-a9f6-b4335702b100/uploads/8d7ab385-5648-448e-b1c1-c551d9630720/Red_Dot_Design_Award_1.png"
    }
  ]
}

Error Analysis:

Xano seems unable to infer the type of the ArrayList coming from the JSONB field.
This issue occurs only when a file upload JSON (array) is sent from Softr. Strings work fine.

Given the limited available resources, I kindly ask for your collaboration and insights to resolve this issue. Please help. Thank you very much!

[artur]

Can you perhaps use txt/url fields for files we at Softr will host the files and put URL there

[kathytw]

Hi Artur,

Thank you very much for helping out.

I did some more experiments, and it turns out that if we set the Xano field as a text field, we can successfully accept the URL from Softr when using a form submission block. When I upload a file via the Softr form submission, it passes the URL directly to my Xano text field. It looks like this:

{"fields":{"user_image":"https://app-files-v1.softr-files.com/applications/d641735a-fd2e-4d03-a9f6-b4335702b100/uploads/06543dfb-6763-43e6-aa05-9242b2dbe2e8/IDEA (1).jpg"}}

However, when using the Action button of a detail page block—in my case: Edit Profile → Update Record → Upload File—even if I map it to Xano’s text field in both the POST and PATCH APIs, it still passes the array to Xano instead of the URL.

The payload looks like this:

json

{
  "fields": {
    "role": "HR",
    "email": "kathy@gmail.com",
    "user_image": [
      {
        "id": "988be72e-93be-4ee6-bb24-af36ad5294d4",
        "type": "URL",
        "filename": "user.png",
        "url": "https://app-files-v1.softr-files.com/applications/d641735a-fd2e-4d03-a9f6-b4335702b100/uploads/098df489-a231-4ee1-aeba-76ead715165a/user.png"
      }
    ]
  },
}

[artur]

@kathytw can you pls ping us in the live support chat I guess we would need access to the app as test user to try it out and solve