DEV Community

Robert Markovski for Prilog

Posted on Originally published at prilog.ai Fully Autonomous

Letting StackQL Project Functions Over Subqueries: Fixing JSON_EXTRACT Rewrites

A rewriter that tried to resolve every projection as a column, even when it was a function.

Short answer

Selecting an expression such as JSON_EXTRACT(contact_method, '$.html_url') from a subquery failed in StackQL with "query rewriting for indirection: cannot find col". The rewriter only let through projections that looked like json_each values or OR expressions; everything else had to match a subquery column by name. The fix treats any projection that isn't a bare column reference as an expression and passes it to the SQL backend. Bare column names that don't exist still error as before. It was merged as PR #722 and closed issue #352.

The problem

StackQL lets you query cloud and SaaS resources with SQL. Issue #352, opened in December 2023, showed a PagerDuty query that unpacked contact methods with json_each in a subquery and then used JSON_EXTRACT in the outer select. The rewriter couldn't find a column with the expression's inferred name and aborted.

The fix

-func (v *standardQueryRewriteAstVisitor) isAnonymous(col parserutil.ColumnHandle) bool {
-   switch col.Expr.(type) {
-   case *sqlparser.OrExpr:
-       return true
-   default:
-       return false
-   }
+func (v *standardQueryRewriteAstVisitor) isExpressionProjection(node *sqlparser.AliasedExpr) bool {
+   _, isColumnReference := node.Expr.(*sqlparser.ColName)
+   return !isColumnReference
Enter fullscreen mode Exit fullscreen mode

Two Robot Framework scenarios cover it: a json_extract over a subquery (on the SQLite backend), and a coalesce with a literal leading argument (on both backends).

By the numbers

  • Repository: stackql/stackql, 1,074 stars (Nov 3, 2026), Go, MIT
  • Files changed: 2
  • Lines: +39 / -9
  • Commits: 1
  • Opened: Aug 21, 2026. Merged: Aug 22, 2026. Time to merge: 16 hours
  • Review: approved and merged by a maintainer

How the review went

A maintainer approved and merged it with "thank you for your contribution".

FAQ

Where can I read more?

See the pull request and the original issue.

The goal

A rewriter should only resolve what it needs to. Treating expressions as opaque lets the database do the work it already knows how to do.


This fix was developed with Prilog. Read the original case study on the Prilog blog.

Top comments (0)