python - JSON对象列上的PostgreSQL JOIN

Question

我应该在 postgres 上加入 3 个不同的表：

• lote_item（我有一些书籍 ID）
• lote_item_log（我有一个列“属性”，带有一个 JSON 对象，例如{“aluno_id”：“2823”，“aluno_email”：“[e-mail]”，“aluno_unidade”：174，“livro_codigo”：“ XOZK-0NOYP0Z1EMJ"} ) - Obs.: aluno_unidade上的一些值是空的

最后

• 公司（我有每个 aluno_unidade 的每个学校名称。例如：aluno_unidade = 174 ==> nome_fantasia = mySchoolName）。

加入前两个表很容易，因为lote_item_log有一个外键，我可以这样匹配：

SELECT * FROM lote_item 加入 lote_item_log on lote_item.id = lote_item_log.lote_item_id

现在，我需要获取包含在表company中的School Name以及表lote_item_log中的aluno_unidade ID 。

我目前的查询是：

        SELECT 
            *
        FROM 
            lote_item
        JOIN
            lote_item_log
        ON
            lote_item.id = lote_item_log.lote_item_id
        JOIN
            company
        ON
            (
                SELECT
                    JSON_EXTRACT_PATH_TEXT(attributes, 'aluno_unidade')::int
                FROM
                    lote_item_log
                WHERE
                    operation_id = 6
            ) = company.senior_id
        WHERE 
            item_id = {book_id};

operation_id确定哪个学校是活跃的。

我得到的错误：

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.CardinalityViolation) 多于一行由用作表达式的子查询返回

我尝试了 LIMIT 1，但后来我得到了一个空数组。

我需要的是：

lote_item.created_at | lote_item.updated_at | lote_item.item_id | uuid | aluno_email | c014_id | nome_fantasia | cnpj | is_franchise | is_active
         somedate    |     somedate         |   some_item_id    | XJW4 | someemail@a | some_id | SCHOOL NAME   | cnpj |    t       |      t       

标签： pythonsqljsonpostgresqlapi

Answer 1

我知道了。

不确定这是最好的方法，但工作......

        SELECT 
            *
        FROM 
            lote_item
        JOIN
            lote_item_log
        ON
            lote_item.id = lote_item_log.lote_item_id
        JOIN
            company
        ON
            JSON_EXTRACT_PATH_TEXT(attributes, 'aluno_unidade')::int = company.senior_id
        WHERE 
            item_id = {book_id};