I am trying to find a way to map the columns resulting from an sql query back to their tables.
For example, with the following query :
"SELECT res.id AS res_id, client.name AS client_name FROM reservation AS res LEFT JOIN client ON res.client_id = client.id"
, have the following mapping:
res_id --> {"field": "id", "table": "reservation"}
client_name --> {"field": "name", "table": "client"}
I tried using several tools, but pglast seems the most adapted since I'm using postgresql, but the best I could do is getting the parse_tree from the query which looks like this :
{"version":150001,"stmts":[{"stmt":{"SelectStmt":{"targetList":[{"ResTarget":{"name":"res_id","val":{"ColumnRef":{"fields":[{"String":{"sval":"res"}},{"String":{"sval":"id"}}],"location":7}},"location":7}},{"ResTarget":{"name":"client_name","val":{"ColumnRef":{"fields":[{"String":{"sval":"client"}},{"String":{"sval":"name"}}],"location":25}},"location":25}}],"fromClause":[{"JoinExpr":{"jointype":"JOIN_LEFT","larg":{"RangeVar":{"relname":"reservation","inh":true,"relpersistence":"p","alias":{"aliasname":"res"},"location":57}},"rarg":{"RangeVar":{"relname":"client","inh":true,"relpersistence":"p","location":86}},"quals":{"A_Expr":{"kind":"AEXPR_OP","name":[{"String":{"sval":"="}}],"lexpr":{"ColumnRef":{"fields":[{"String":{"sval":"res"}},{"String":{"sval":"client_id"}}],"location":96}},"rexpr":{"ColumnRef":{"fields":[{"String":{"sval":"client"}},{"String":{"sval":"id"}}],"location":112}},"location":110}}}}],"limitOption":"LIMIT_OPTION_DEFAULT","op":"SETOP_NONE"}}}]}
Here, it can be pretty easy to unnest the json, but I would like to know if there is an easier way that could work also for more complicated queries (involving subqueries for instance).