PyDAL issues with json

[Maurice Waka]

When I used PostgreSQL (including NeonDB), and inserting into a Field('images', 'json') column, I noted tht it fails even when passing a properly serialized JSON string via json.dumps().

Instead of inserting the correct JSON value (e.g. ["images/pets.jpg"]), pyDAL inserts a corrupted string containing its internal list separator ('||'), resulting in:

SQL

invalid input syntax for type json
DETAIL: Token "|" is invalid.
CONTEXT: JSON data, line 1: |...

what I did

1. Define table with PostgreSQL:

   
   Field('images', 'json')
2. In an @action, do:
   Python

   images_value = json.dumps(["images/test.jpg"])
   db.mytable.insert(..., images=images_value)

[Massimo DiPierro]

If a file is JSON you should not store a string in it but a proper object:

images_value = json.dumps(["images/test.jpg"])

db.mytable.insert(..., images=images_value)

If you want to store a string-serialized json than make the field of type "text" not "json".