Getting data from queryparser

February 24, 2022

I am attempting to use queryparser to extract table relationships from an SQL query. I can get most of what I need, I’m just having issues unpacking the lists.

library(queryparser)

file <- "select 
  p.name,
  p.age,
  p.hometown,
  c.state,
  c.country,
  n.capitol,
  n.leader
 from person p
   inner join city c
     on  p.hometown = c.name
   inner join nation n
     on c.county = n.name
 where C.country != 'Antarctica' "

query <- parse_query(file, tidyverse = TRUE)
query$from

yields the following lists:

> query$from
$p
person

$c
city

$n
nation

attr(,"join_types")
[1] "inner join" "inner join"
attr(,"join_conditions")
attr(,"join_conditions")[[1]]
p.hometown == c.name

attr(,"join_conditions")[[2]]
c.county == n.name

I would like to have a datafame that has each table name and it’s alias, and a second table with the join criteria. What is the easiest way to do this dynamically so that I don’t have to adjust code between scanning different scripts?

>Solution :

Convert to character and then use stack. For the info in the attributes remove the names and simplify giving the character matrix shown.

stack(sapply(query$from, as.character))
##   values ind
## 1 person   p
## 2   city   c
## 3 nation   n

simplify2array(attributes(query$from)[-1])
##      join_types   join_conditions       
## [1,] "inner join" "p.hometown == c.name"
## [2,] "inner join" "c.county == n.name"