Is is possible to use .as_alias() for Json field from a joined table?

[Anton-Karpenko]

I struggle to apply as_alias() to a field from a joined table.

Here is an example:

class User(Table, table_name="user"):
    name = Varchar(length=120)
    config = JSONB(default={})

    
class Subscriber(Table, table_name="subscriber"):
    name = Varchar(length=120)
    user = ForeignKey(references=User)

Here is my query:

query = Subscriber.select(Subscriber.name.as_alias("subscriber_name"), Subscriber.user.config.as_alias("config"))
print(query)

The output is:

SELECT "subscriber"."name" AS subscriber_name,  "subscriber$user"."config" AS "user$config" AS config
FROM subscriber LEFT JOIN user subscriber$user ON (subscriber.user = subscriber$user.id)

Here is AS coupled, and I have an obvious error:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "AS"

[dantownsend]

@Anton-Karpenko You're right this is a bug. I was able to replicate it using this script:

import asyncio

from piccolo.table import Table, create_db_tables, drop_db_tables
from piccolo.columns import Varchar, JSONB, ForeignKey
from piccolo.engine.sqlite import SQLiteEngine

DB = SQLiteEngine()

class User(Table, tablename="my_user", db=DB):
    name = Varchar(length=120)
    config = JSONB(default={})


class Subscriber(Table, tablename="subscriber",  db=DB):
    name = Varchar(length=120)
    user = ForeignKey(references=User)


async def main():
    await drop_db_tables(User, Subscriber)
    await create_db_tables(User, Subscriber)
    user = await User.objects().create(name="Bob")
    await Subscriber.objects().create(user=user, name="Susan")

    results = await Subscriber.select(
        Subscriber.name.as_alias("subscriber_name"),
        Subscriber.user.config.as_alias("config")
    )
    print(results)

if __name__ == '__main__':
    asyncio.run(main())

The problem just seems to be with JSONB when using as_alias with a join. JSONB has a custom get_select_string method, which is the cause of the bug:

piccolo/piccolo/columns/column_types.py

Line 2230 in 2288596

def get_select_string(self, engine_type: str, just_alias=False) -> str:

The SQL query has multiple 'AS' when using as_alias:

SELECT
    "subscriber"."name" AS subscriber_name,
    "subscriber$user"."config" AS "user$config" AS config   -- This is the problem, there are two 'AS' statements
FROM subscriber
LEFT JOIN my_user subscriber$user
ON (subscriber.user = subscriber$user.id)

Hopefully it's quite a simple fix.

In the meantime, you can use the JSON column type instead, until I release a fix.

[Anton-Karpenko]

Great, thanks! I'll wait for a fix, but for now, I'll make a tweak in pydantic.

[dantownsend]

Version 0.80.0 is on PyPI now - hopefully fixes the issue.

[Anton-Karpenko]

Thanks for a quick fix!