You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Peewee ORM中CTE嵌套时JSON_TABLE解析逻辑丢失问题求助

avatar
开发者阿泽
2026-6-15
问题

使用Peewee ORM创建CTE，通过原生SQL调用JSON_TABLE和JSON_KEYS解析字段中的JSON对象，将每个元素转为独立记录并关联其他表。单独执行包含该CTE的查询时，能生成包含完整JSON_TABLE解析逻辑的正确SQL；但将该CTE嵌套后关联其他表构建主查询时，JSON_TABLE的解析逻辑及WHERE条件完全丢失。

需解决：

  • 确认问题是否与原生SQL的使用有关
  • 如何让Peewee保留CTE的完整语法（包括原生SQL部分）
  • 是否有其他实现JSON解析后关联多表的方案

目标SQL（可正常执行） 

WITH solution AS (
    SELECT DISTINCT
        ost_form_entry.object_id as ost_form_entry_object_id,
        jt.my_key,
        JSON_UNQUOTE(JSON_EXTRACT(ost_form_entry_values.value, CONCAT('$.', jt.my_key))) AS my_value,
        ost_ticket.number,
        ost_ticket.ticket_id,
        ost_ticket.created AS created_date,
        ost_ticket.closed AS closed_date
    FROM
        ost_form_entry_values
        JOIN ost_form_entry ON ost_form_entry_values.entry_id = ost_form_entry.id
        JOIN ost_ticket ON ost_form_entry.object_id = ost_ticket.ticket_id,
        JSON_TABLE(
            JSON_KEYS(ost_form_entry_values.value),
            '$[*]' COLUMNS (
                my_key VARCHAR(100) PATH '$'
            )
        ) AS jt
    WHERE
        JSON_VALID(ost_form_entry_values.value)
        AND JSON_TYPE(ost_form_entry_values.value) = 'OBJECT'
        AND ost_form_entry_values.field_id = 67
),
user_type AS (
    SELECT DISTINCT
        jt.my_key,
        JSON_UNQUOTE(JSON_EXTRACT(ost_form_entry_values.value, CONCAT('$.', jt.my_key))) AS my_value,
        ost_form_entry.object_id as ost_form_entry_object_id
    FROM
        ost_form_entry_values
        JOIN ost_form_entry ON ost_form_entry_values.entry_id = ost_form_entry.id,
        JSON_TABLE(
            JSON_KEYS(ost_form_entry_values.value),
            '$[*]' COLUMNS (
                my_key VARCHAR(100) PATH '$'
            )
        ) AS jt
    WHERE
        JSON_VALID(ost_form_entry_values.value)
        AND JSON_TYPE(ost_form_entry_values.value) = 'OBJECT'
        AND ost_form_entry_values.field_id = 76
),
response AS (
    SELECT DISTINCT
        CONVERT(ost_form_entry_values.value, DATETIME) AS my_value,
        ost_form_entry.object_id as ost_form_entry_object_id
    FROM
        ost_form_entry_values
        JOIN ost_form_entry ON ost_form_entry_values.entry_id = ost_form_entry.id
    WHERE
        ost_form_entry_values.value IS NOT NULL
        AND ost_form_entry_values.field_id = 75
)
SELECT DISTINCT
    solution.number AS ticket_no,
    solution.created_date,
    solution.closed_date,
    response.my_value AS first_response_date,
    solution.my_value AS solution,
    user_type.my_value AS user_type,
    solution.ticket_id
FROM
    solution
    JOIN user_type ON solution.ost_form_entry_object_id = user_type.ost_form_entry_object_id
    JOIN response ON solution.ost_form_entry_object_id = response.ost_form_entry_object_id
WHERE
    solution.closed_date BETWEEN '2024-05-22' AND '2024-08-26'
;

Peewee中构建的JSON解析CTE 

cte = (OstFormEntryValues
       .select(
           OstFormEntryValues.entry_id,
           OstFormEntryValues.field_id,
           SQL('jt.my_key').alias(f'{field_name}_key'),
           fn.JSON_UNQUOTE(fn.JSON_EXTRACT(OstFormEntryValues.value, fn.CONCAT('$.', SQL('jt.my_key')))).alias(field_name)
       )
       .from_(OstFormEntryValues, SQL('JSON_TABLE(JSON_KEYS(value), \'$[*]\' COLUMNS (my_key VARCHAR(100) PATH \'$\'))').alias('jt'))
       .where(
           fn.JSON_VALID(OstFormEntryValues.value) &
           (fn.JSON_TYPE(OstFormEntryValues.value) == 'OBJECT') &
           (OstFormEntryValues.field_id == field_id)
       )
       .cte(field_name))

单独查询时生成的正确SQL（省略参数） 

WITH `solution_found` AS (
SELECT
    `t1`.`entry_id`,
    `t1`.`field_id`,
    jt.my_key AS `solution_found_key`,
    JSON_UNQUOTE(JSON_EXTRACT(`t1`.`value`,
    CONCAT(%s,
    jt.my_key))) AS `solution_found`
FROM
    `ost_form_entry_values` AS `t1`,
    JSON_TABLE(JSON_KEYS(value),
    '$[*]' COLUMNS (my_key VARCHAR(100) PATH '$')) AS `jt`
WHERE
    ((JSON_VALID(`t1`.`value`)
        AND (JSON_TYPE(`t1`.`value`) = %s))
        AND (`t1`.`field_id` = %s)))
SELECT
    `t2`.`object_id`,
    `solution_found`.`entry_id`,
    `solution_found`.`field_id`,
    `solution_found`.`solution_found`
FROM
    `ost_form_entry` AS `t2`
INNER JOIN `solution_found` ON
    (`t2`.`id` = `solution_found`.`entry_id`)

嵌套CTE后主查询生成的错误SQL（丢失解析逻辑） 

WITH `solution_found` AS (
SELECT
    `t1`.`object_id`,
    `solution_found`.`entry_id`,
    `solution_found`.`field_id`,
    `solution_found`.`solution_found`
FROM
    `ost_form_entry` AS `t1`
INNER JOIN `solution_found` ON
    (`t1`.`id` = `solution_found`.`entry_id`))
SELECT
    `t2`.`number`,
    `t2`.`ticket_id`,
    `solution_found`.`entry_id`,
    `solution_found`.`field_id`,
    `solution_found`.`solution_found`
FROM
    `ost_ticket` AS `t2`
INNER JOIN `solution_found` ON
    (`t2`.`ticket_id` = `solution_found`.`object_id`)
解决方案

问题原因

Peewee在处理CTE嵌套时，对原生SQL片段的跟踪存在缺陷。当将CTE作为子查询关联其他表时，Peewee没有正确保留CTE定义中的原生FROM子句（即JSON_TABLE部分）和WHERE条件，仅保留了CTE与其他表的关联逻辑，导致核心JSON解析逻辑丢失。

解决方法：显式定义完整CTE关联逻辑

不要分步构建CTE再关联，而是在CTE内部完成所有必要的表关联，确保Peewee能完整捕获整个CTE的SQL结构：

# 构建包含完整关联的solution CTE
solution_cte = (OstFormEntryValues
    .select(
        OstFormEntry.object_id.alias('ost_form_entry_object_id'),
        SQL('jt.my_key'),
        fn.JSON_UNQUOTE(fn.JSON_EXTRACT(OstFormEntryValues.value, fn.CONCAT('$.', SQL('jt.my_key')))).alias('my_value'),
        OstTicket.number,
        OstTicket.ticket_id,
        OstTicket.created.alias('created_date'),
        OstTicket.closed.alias('closed_date')
    )
    .join(OstFormEntry, on=(OstFormEntryValues.entry_id == OstFormEntry.id))
    .join(OstTicket, on=(OstFormEntry.object_id == OstTicket.ticket_id))
    .from_(OstFormEntryValues, SQL('JSON_TABLE(JSON_KEYS(value), \'$[*]\' COLUMNS (my_key VARCHAR(100) PATH \'$\'))').alias('jt'))
    .where(
        fn.JSON_VALID(OstFormEntryValues.value) &
        (fn.JSON_TYPE(OstFormEntryValues.value) == 'OBJECT') &
        (OstFormEntryValues.field_id == 67)
    )
    .distinct()
    .cte('solution'))

# 同理构建user_type CTE
user_type_cte = (OstFormEntryValues
    .select(
        SQL('jt.my_key'),
        fn.JSON_UNQUOTE(fn.JSON_EXTRACT(OstFormEntryValues.value, fn.CONCAT('$.', SQL('jt.my_key')))).alias('my_value'),
        OstFormEntry.object_id.alias('ost_form_entry_object_id')
    )
    .join(OstFormEntry, on=(OstFormEntryValues.entry_id == OstFormEntry.id))
    .from_(OstFormEntryValues, SQL('JSON_TABLE(JSON_KEYS(value), \'$[*]\' COLUMNS (my_key VARCHAR(100) PATH \'$\'))').alias('jt'))
    .where(
        fn.JSON_VALID(OstFormEntryValues.value) &
        (fn.JSON_TYPE(OstFormEntryValues.value) == 'OBJECT') &
        (OstFormEntryValues.field_id == 76)
    )
    .distinct()
    .cte('user_type'))

# 同理构建response CTE
response_cte = (OstFormEntryValues
    .select(
        fn.CONVERT(OstFormEntryValues.value, 'DATETIME').alias('my_value'),
        OstFormEntry.object_id.alias('ost_form_entry_object_id')
    )
    .join(OstFormEntry, on=(OstFormEntryValues.entry_id == OstFormEntry.id))
    .where(
        OstFormEntryValues.value.is_null(False) &
        (OstFormEntryValues.field_id == 75)
    )
    .distinct()
    .cte('response'))

# 主查询关联所有CTE
main_query = (solution_cte
    .select(
        solution_cte.c.number.alias('ticket_no'),
        solution_cte.c.created_date,
        solution_cte.c.closed_date,
        response_cte.c.my_value.alias('first_response_date'),
        solution_cte.c.my_value.alias('solution'),
        user_type_cte.c.my_value.alias('user_type'),
        solution_cte.c.ticket_id
    )
    .join(user_type_cte, on=(solution_cte.c.ost_form_entry_object_id == user_type_cte.c.ost_form_entry_object_id))
    .join(response_cte, on=(solution_cte.c.ost_form_entry_object_id == response_cte.c.ost_form_entry_object_id))
    .where(
        solution_cte.c.closed_date.between('2024-05-22', '2024-08-26')
    )
    .distinct())

# 执行查询
results = main_query.execute()

这种方式将所有关联逻辑包含在CTE内部，Peewee会完整生成包含JSON_TABLE的CTE定义，不会丢失解析逻辑。

替代方案：自定义JSON_TABLE函数扩展

如果频繁使用JSON_TABLE，可以通过Peewee的Function类自定义该函数，让ORM更好地识别和跟踪它：

from peewee import Function

class JSON_TABLE(Function):
    function = 'JSON_TABLE'

# 使用自定义函数重构CTE
cte = (OstFormEntryValues
    .select(
        OstFormEntryValues.entry_id,
        OstFormEntryValues.field_id,
        SQL('jt.my_key').alias(f'{field_name}_key'),
        fn.JSON_UNQUOTE(fn.JSON_EXTRACT(OstFormEntryValues.value, fn.CONCAT('$.', SQL('jt.my_key')))).alias(field_name)
    )
    .from_(
        OstFormEntryValues,
        JSON_TABLE(
            fn.JSON_KEYS(OstFormEntryValues.value),
            SQL("'$[*]' COLUMNS (my_key VARCHAR(100) PATH '$')")
        ).alias('jt')
    )
    .where(
        fn.JSON_VALID(OstFormEntryValues.value) &
        (fn.JSON_TYPE(OstFormEntryValues.value) == 'OBJECT') &
        (OstFormEntryValues.field_id == field_id)
    )
    .cte(field_name))

自定义函数能让Peewee更准确地解析SQL结构，减少原生SQL片段被忽略的概率。

终极兜底：使用原生SQL字符串

如果上述方法都无效，可以直接使用Peewee的RawQuery执行完整的原生SQL语句，完全绕过ORM的SQL生成逻辑：

from peewee import RawQuery

sql = """
WITH solution AS (
    SELECT DISTINCT
        ost_form_entry.object_id as ost_form_entry_object_id,
        jt.my_key,
        JSON_UNQUOTE(JSON_EXTRACT(ost_form_entry_values.value, CONCAT('$.', jt.my_key))) AS my_value,
        ost_ticket.number,
        ost_ticket.ticket_id,
        ost_ticket.created AS created_date,
        ost_ticket.closed AS closed_date
    FROM
        ost_form_entry_values
        JOIN ost_form_entry ON ost_form_entry_values.entry_id = ost_form_entry.id
        JOIN ost_ticket ON ost_form_entry.object_id = ost_ticket.ticket_id,
        JSON_TABLE(
            JSON_KEYS(ost_form_entry_values.value),
            '$[*]' COLUMNS (
                my_key VARCHAR(100) PATH '$'
            )
        ) AS jt
    WHERE
        JSON_VALID(ost_form_entry_values.value)
        AND JSON_TYPE(ost_form_entry_values.value) = 'OBJECT'
        AND ost_form_entry_values.field_id = 67
),
user_type AS (
    SELECT DISTINCT
        jt.my_key,
        JSON_UNQUOTE(JSON_EXTRACT(ost_form_entry_values.value, CONCAT('$.', jt.my_key))) AS my_value,
        ost_form_entry.object_id as ost_form_entry_object_id
    FROM
        ost_form_entry_values
        JOIN ost_form_entry ON ost_form_entry_values.entry_id = ost_form_entry.id,
        JSON_TABLE(
            JSON_KEYS(ost_form_entry_values.value),
            '$[*]' COLUMNS (
                my_key VARCHAR(100) PATH '$'
            )
        ) AS jt
    WHERE
        JSON_VALID(ost_form_entry_values.value)
        AND JSON_TYPE(ost_form_entry_values.value) = 'OBJECT'
        AND ost_form_entry_values.field_id = 76
),
response AS (
    SELECT DISTINCT
        CONVERT(ost_form_entry_values.value, DATETIME) AS my_value,
        ost_form_entry.object_id as ost_form_entry_object_id
    FROM
        ost_form_entry_values
        JOIN ost_form_entry ON ost_form_entry_values.entry_id = ost_form_entry.id
    WHERE
        ost_form_entry_values.value IS NOT NULL
        AND ost_form_entry_values.field_id = 75
)
SELECT DISTINCT
    solution.number AS ticket_no,
    solution.created_date,
    solution.closed_date,
    response.my_value AS first_response_date,
    solution.my_value AS solution,
    user_type.my_value AS user_type,
    solution.ticket_id
FROM
    solution
    JOIN user_type ON solution.ost_form_entry_object_id = user_type.ost_form_entry_object_id
    JOIN response ON solution.ost_form_entry_object_id = response.ost_form_entry_object_id
WHERE
    solution.closed_date BETWEEN %s AND %s
;
"""

# 执行原生查询，传入参数
results = RawQuery(OstTicket, sql, ('2024-05-22', '2024-08-26')).execute()

这种方式完全避免ORM的SQL生成问题，适合复杂的JSON解析场景。

内容的提问来源于stack exchange，提问作者SpacemanSpiff

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.15 18:37:02