问题

Here's the translated code portion:

从 SQLAlchemy 的 ORM 框架中，是否可以在 Trino 中使用 JSON 列？以下是我目前的尝试：

```python
pip install trino[sqlalchemy]

(详细安装说明请参考 https://github.com/trinodb/trino-python-client#sqlalchemy。)

from sqlalchemy import create_engine, select
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.schema import Table, Column, MetaData

engine = create_engine(...)

Base = declarative_base()

table = Table(
  'table',
  Base.metadata,
  Column('json_col', postgresql.JSON), # 我已尝试其他 JSON 类型：https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.JSON
  autoload_with=engine,
  extend_existing=True)

with Session(engine_prod_a_agentservice) as session:
    
  test_ids = ['abc', 'def']
    
  stmt = select(table).where(
        table.c.json['key1']['key2'].in_(test_ids))
    
  print(stmt)
    
  result = session.execute(stmt)

这段代码不起作用，因为生成的 WHERE 子句不正确（以下代码是从 print(stmt) 的输出中提取的）：

WHERE ((table.json_col[:json_col_1]) ->> :param_1) IN (__[POSTCOMPILE_param_2])

在 Trino 的 SQL 方言中，我将如下实现：

WHERE cast(json_extract(json_col, '$.key1.key2') as varchar) IN ('abc', 'def')

我想要实现的功能是否可能，还是功能目前还没有（尚未）实现？

请注意，这是翻译后的代码部分，不包括问题或其他信息。

<details>
<summary>英文:</summary>

Is there a way to use [SQLAlchemy&#39;s ORM framework][1] with a JSON column in Trino? Here&#39;s my attempt so far:

pip install trino[sqlalchemy]

(See https://github.com/trinodb/trino-python-client#sqlalchemy for installation details.)


```python
from sqlalchemy import create_engine, select
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.schema import Table, Column, MetaData

engine = create_engine(...)

Base = declarative_base()

table = Table(
  'table',
  Base.metadata,
  Column('json_col', postgresql.JSON), # I have tried other JSON types: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.JSON
  autoload_with=engine,
  extend_existing=True)

with Session(engine_prod_a_agentservice) as session:
    
  test_ids = ['abc','def']
    
  stmt = select(table).where(
        table.c.json['key1']['key2'].in_(test_ids))
    
  print(stmt)
    
  result = session.execute(stmt)

This does not work because the generated WHERE clause is not correct (the following code being taken from the output of print(stmt)):

WHERE ((table.json_col[:json_col_1]) ->> :param_1) IN (__[POSTCOMPILE_param_2])

In the Trino dialect of SQL I would achieve this as follows:

WHERE cast(json_extract(json_col,'$.key1.key2') as varchar) IN ('abc', 'def')

Is what I want to achieve possible or is the functionality just not there (yet)?

答案1

得分: 1

You can import the JSON datatype from the trino package and make the following code changes:

import sqlalchemy as sqla
from trino.sqlalchemy.datatype import JSON

metadata = sqla.MetaData()

table = Table(
  'table',
  metadata,
  sqla.Column('json_col', JSON), 
  schema="....")

You can find the package's unit test on GitHub to validate the work with the JSON column.

import sqlalchemy as sqla
from trino.sqlalchemy.datatype import JSON

metadata = sqla.MetaData()

table = Table(
  'table',
  metadata,
  sqla.Column('json_col', JSON), 
  schema="....")

答案2

得分: 1

以下是翻译好的内容：

如您所解释，您需要使用Trino的'json_extract'方法。尝试类似以下方式：

from sqlalchemy import func, String

stmt = select(table).where(func.cast(func.json_extract(table.json_col, f'$.key1.key2'), String).in_(test_ids))

from sqlalchemy import func, String

stmt = select(table).where(func.cast(func.json_extract(table.json_col, f'$.key1.key2'), String).in_(test_ids))