Cannot cast using JSON.JSONPathType

[CaselIT]

In some dialects that use server side binding, like psycopg and asyncpg, queries that use jsonb_path_query_array and similar function that take jsonpath arguments fail without cast, and casting using JSON.JSONPathType errors with
AttributeError: 'JSONPathType' object has no attribute '_compiler_dispatch'

Example:

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

m = sa.MetaData()
t = sa.Table("t", m, sa.Column("addresses", JSONB))

e = sa.create_engine('postgresql+psycopg://scott:[email protected]:5432/test',echo=True)
m.create_all(e)

q = sa.select(
    sa.func.jsonb_path_query_array(
        t.c.addresses.op("->")("addresses"), "$.address.id"
    )
)

with e.connect() as c:
    c.execute(q)

casting instead to jsonpath with a literal columns works:

q = sa.select(
    sa.func.jsonb_path_query_array(
        t.c.addresses.op("->")("addresses"), sa.literal_column("'$.address.id'::jsonpath")
    )
)

[zzzeek]

OK if jsonpath is really a type we can cast to that using sqltypes.JSON.JSONPathType, just needs a visit set up in postgresql/base.py -> PGTypeCompiler

[zzzeek]

i dont this can be automatic however, we just document it

[CaselIT]

i dont this can be automatic however, we just document it

not unless we implement a function for jsonb_path_query_array with the proper types I guess. probably not worth it since it's safe to say that's not very common

[zzzeek]

right we could implement it but then it's like there's this one JSON function that's randomly implemented and the rest aren't, which is just not easy to explain

[CaselIT]

Do you want to keep in inside the JSON class or should it have its own type outside of it (I'm guessing called JSONPATH)

[zzzeek]

I think we can make an alias for it JsonPath and it should be a genric type, that is, wont fail if used on other backends

[zzzeek]

within PG dialect, sure we can add JSONPATH directly as well, that will be the type map for JsonPath

[CaselIT]

ah ok, JsonPath is generic, I though it was in pg only.

I'll take a look

[sqla-tester]

Federico Caselli has proposed a fix for this issue in the main branch:

JSONPathType can be used in casts in PostgreSQL https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4019

[CaselIT]

I think we can make an alias for it JsonPath and it should be a genric type, that is, wont fail if used on other backends

I'm not sure what to render in the other backends. At the moment in the patch it fails with unsupported compilation

[Lawouach]

Hello gentlemen,

Any chance this would have been backported to 1.4.x? Or do you advise I upgarde to 2.0?

Thanks,

[Lawouach]

Well, I went and used literal_column with 1.4 and that works so that should be fine.