Issue accessing parts of the parsed SQL query tree using sqlglot.parse()

[JhonGarcia0]

I am using the sqlglot library to parse a complex SQL query, but I am encountering issues when trying to access specific parts of the parsed tree. When I execute sqlglot.parse(query), I get an object of type sqlglot.expressions.Select, but when iterating over this object, I can only access the columns (Column), and I cannot seem to access other elements such as JOINs, WHERE, and WITH, even though I know they are present in the query.

query = """WITH cte1 AS (
    SELECT t1.id, t1.nombre
    FROM tabla1 t1
    WHERE t1.activo = 1
)
SELECT t1.id, t1.nombre, t2.fecha, t3.monto
FROM cte1 t1
JOIN tabla2 t2 ON t1.id = t2.id_tabla1 AND t2.estado = 'activo'
JOIN (SELECT t4.monto FROM tabla4 t4 WHERE t4.estado = 'activo') t3 ON t2.id = t3.id_tabla2 AND t3.tipo = 'final'
WHERE t2.fecha > '2023-01-01';
"""

parsed = sqlglot.parse(query)
for a in parsed:
    print(type(a))
    # Returns: <class 'sqlglot.expressions.Select'>

for a in parsed:
    for n in a:
        print(type(i))
    # Returns: <class 'sqlglot.expressions.Column'>

Expected Outcome:
I would like to be able to access the following parts of the parsed tree:

The JOINs present in the query.
The WHERE clause.
The WITH clause (CTE).

I have tried several ways to access the joins, where, and with properties of the Select object, but have not been successful. For example:

for a in parsed:
    if isinstance(a, sqlglot.expressions.Select):
        # Attempting to access JOINs
        print(a.joins)  # This either causes an error --> AttributeError: 'Select' object has no attribute 'joins'

I have verified that the parsed tree contains the JOINs, WHERE, and WITH, but I am unable to access these parts.

Here’s the result when I print the parsed variable:

[Select(
   expressions=[
     Column(
       this=Identifier(this=id, quoted=False),
       table=Identifier(this=t1, quoted=False)),
     Column(
       this=Identifier(this=nombre, quoted=False),
       table=Identifier(this=t1, quoted=False)),
     Column(
       this=Identifier(this=fecha, quoted=False),
       table=Identifier(this=t2, quoted=False)),
     Column(
       this=Identifier(this=monto, quoted=False),
       table=Identifier(this=t3, quoted=False))],
   from=From(
     this=Table(
       this=Identifier(this=cte1, quoted=False),
       alias=TableAlias(
         this=Identifier(this=t1, quoted=False)))),
   joins=[
     Join(
       this=Table(
         this=Identifier(this=tabla2, quoted=False),
         alias=TableAlias(
           this=Identifier(this=t2, quoted=False))),
       on=And(
         this=EQ(
           this=Column(
             this=Identifier(this=id, quoted=False),
             table=Identifier(this=t1, quoted=False)),
           expression=Column(
             this=Identifier(this=id_tabla1, quoted=False),
             table=Identifier(this=t2, quoted=False))),
         expression=EQ(
           this=Column(
             this=Identifier(this=estado, quoted=False),
             table=Identifier(this=t2, quoted=False)),
           expression=Literal(this=activo, is_string=True)))),
     Join(
       this=Subquery(
         this=Select(
           expressions=[
             Column(
               this=Identifier(this=monto, quoted=False),
               table=Identifier(this=t4, quoted=False))],
           from=From(
             this=Table(
               this=Identifier(this=tabla4, quoted=False),
               alias=TableAlias(
                 this=Identifier(this=t4, quoted=False)))),
           where=Where(
             this=EQ(
               this=Column(
                 this=Identifier(this=estado, quoted=False),
                 table=Identifier(this=t4, quoted=False)),
               expression=Literal(this=activo, is_string=True)))),
         alias=TableAlias(
           this=Identifier(this=t3, quoted=False))),
       on=And(
         this=EQ(
           this=Column(
             this=Identifier(this=id, quoted=False),
             table=Identifier(this=t2, quoted=False)),
           expression=Column(
             this=Identifier(this=id_tabla2, quoted=False),
             table=Identifier(this=t3, quoted=False))),
         expression=EQ(
           this=Column(
             this=Identifier(this=tipo, quoted=False),
             table=Identifier(this=t3, quoted=False)),
           expression=Literal(this=final, is_string=True))))],
   where=Where(
     this=GT(
       this=Column(
         this=Identifier(this=fecha, quoted=False),
         table=Identifier(this=t2, quoted=False)),
       expression=Literal(this=2023-01-01, is_string=True))),
   with=With(
     expressions=[
       CTE(
         this=Select(
           expressions=[
             Column(
               this=Identifier(this=id, quoted=False),
               table=Identifier(this=t1, quoted=False)),
             Column(
               this=Identifier(this=nombre, quoted=False),
               table=Identifier(this=t1, quoted=False))],
           from=From(
             this=Table(
               this=Identifier(this=tabla1, quoted=False),
               alias=TableAlias(
                 this=Identifier(this=t1, quoted=False)))),
           where=Where(
             this=EQ(
               this=Column(
                 this=Identifier(this=activo, quoted=False),
                 table=Identifier(this=t1, quoted=False)),
               expression=Literal(this=1, is_string=False)))),
         alias=TableAlias(
           this=Identifier(this=cte1, quoted=False)))]))]

Based on this output, I can see that the parsed object contains the JOIN, WHERE, and WITH parts. However, I am unable to directly access them.

Has anyone encountered this issue before or could someone help me figure out how to access these parts of the parsed query?

[tobymao]

Read this guide

https://github.com/tobymao/sqlglot/blob/main/posts/ast_primer.md