how to use JSON type in sqlmodel , Problem with openapi.json, File "pydantic\json.py", line 90, in pydantic.json.pydantic_encoder TypeError: Object of type 'Column' is not JSON serializable

[muscodev]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional
from sqlmodel import Field, SQLModel, Column, JSON


class EmployeeMasterBase(SQLModel):
    additional_info: Optional[dict] = Field(sa_column=Column(JSON))

Description

i was creating a Sqlmodel to conncect with postgres.
i want a field which of postgres JSON type.
The above code is able to create the table.
But while query "/openapi.json" for swagger, It gives following error .

 \site-packages\fastapi\_compat.py", line 370, in get_model_definitions
    m_schema, m_definitions, m_nested_models = model_process_schema(
  File "pydantic\schema.py", line 582, in pydantic.schema.model_process_schema
  File "pydantic\schema.py", line 623, in pydantic.schema.model_type_schema
  File "pydantic\schema.py", line 249, in pydantic.schema.field_schema
  File "pydantic\schema.py", line 217, in pydantic.schema.get_field_info_schema
  File "pydantic\schema.py", line 996, in pydantic.schema.encode_default
  File "pydantic\json.py", line 90, in pydantic.json.pydantic_encoder
TypeError: Object of type 'Column' is not JSON serializable




### Operating System

Windows

### Operating System Details

Windows 10, 64 bit

### SQLModel Version

 sqlmodel = "^0.0.8"

### Python Version

Python 3.10.7

### Additional Context

_No response_

[muscodev]

How we approach when using json type in sqlmodel

[muscodev]

iam upgraded to sqlmodel version 0.0.11, still the issue exist.
i don't know , why pydantic taking Column for JSON serialization

additional_info: Optional[dict] = Field(default={}, sa_column=Column(JSON))

additional_info: Optional[dict] = Field(default_factory=dict, sa_column=Column(JSON))

i have used both above ways, no change

[muscodev]

@tiangolo @dhiraj any suggestions

[muscodev]

iam closing it , i have to check on it again.
Thankyou

[muscodev]

Iam posting this to avoid the same mistake by other
The issue was in another line of code, where i missed the Field

    created_on: datetime =Column(
        DateTime(timezone=True),
        server_default=func.now()
        )
    updated_on: datetime = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        server_onupdate=func.now()
        )

corrected code is as follows

    created_on: datetime = Field(sa_column=Column(
        DateTime(timezone=True),
        server_default=func.now()
        ))
    updated_on: datetime = Field(sa_column=Column(
        DateTime(timezone=True),
        server_default=func.now(),
        server_onupdate=func.now()
        ))

[ad-code1993]

use this instead "Here's how to define a JSON field in SQLModel:
Import JSON from SQLAlchemy: You need to import the JSON type from sqlalchemy.
Python

from sqlalchemy import JSON

Define the field with sa_column: In your SQLModel class, define the field that will store JSON data and specify its SQLAlchemy column type using sa_column=Column(JSON).
Python

from typing import Optional, Dict, Any
from sqlmodel import Field, SQLModel, Column

class MyModel(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    data: Dict[str, Any] = Field(sa_column=Column(JSON))"

[fbuccioni]

Hi everyone

I suggest to have a global variable like TYPE_ANNOTATION_MAPS in the package to have arbitrary types support in SQLModel, this could save a lot of headaches and make SQLModel adaptable to different needs.

I have done this by monkey patching get_sqlalchemy_type() function:

import sqlmodel.main
from typing import Union, Annotated
from pydantic import JsonValue as PydanticJsonValue
from pydantic.types import Json as PydanticJson
from sqlalchemy.types import JSON as SQLAlchemyJSON


# I use pascal case for constants
TypeAnnotationMap = {
    PydanticJson: SQLAlchemyJSON,
    PydanticJsonValue: SQLAlchemyJSON
}

_gst = sqlmodel.main.get_sqlalchemy_type
def get_sqlalchemy_type(field: Any) -> Any:
    try:
        return _gst(field)
    except Exception as exc:
        type_ = None

        if hasattr(field, 'type_'):
            type_ = field.type_
        elif field.annotation:
            type_ = field.annotation

            if (
                get_origin(type_) is Union
                and len(type_.__args__) == 2
                and type(None) in type_.__args__
            ):
                type_ = next(arg for arg in type_.__args__ if arg is not None)

            if get_origin(type_) is Annotated:
                type_ = type(type_.__metadata__[0])

        if type_ in TypeAnnotationMap.keys():
            return TypeAnnotationMap[type_]

        raise exc

sqlmodel.main.get_sqlalchemy_type = get_sqlalchemy_type