sqlalchemy.dialects.postgresql.JSON columns have incorrect types?

[davidgu]

mypy seems to think that the type of data in this column is "Union[Dict[str, Any], List[Any]]" which seems reasonable. However, the returned data actually seems to be of type str

[killthekitten]

@davidgu did I get it right that you refer to runtime value of the column? If so, could you provide a minimal reproducible example?

Is it before or after the changes to the column get committed/flushed? It could be that you accidentally pass it the data in a form of string, i.e. json.dumps. In that case, SQLAlchemy won't attempt to convert your JSON to a dict before the flush happens.

[davidgu]

@killthekitten This is after the changes to the column are committed. The reason for this bug is that I am storing a plain string in the column, which is valid JSON and accepted by postgres. You can verify this on https://jsonlint.com/, or with json.loads('"hello world"')

The typing stubs do not account for this possibility. A more accurate type would be something like Union[Dict[str, Any], List[Any], str, int, float] to account for the possibility of bare strings or numbers.

[killthekitten]

@davidgu thanks for clarifying, makes sense now 👍 This change might also need to take in account a JSON null value, i.e. https://github.com/sqlalchemy/sqlalchemy/issues/3159, and/or JSON.none_as_null

[davidgu]

@killthekitten can we add a bug label to this issue? I don't seem to have the permissions to do that

[killthekitten]

@davidgu me neither, for this we would need some help from maintainers.