Beanquery: current state and a roadmap

[Chary Ev2geny]

Dear all,

There seems to have been quite a lot of recent development and nice features, introduced to beanquery. In fact in beancount v3 beancount itself is just a library, used by beanquery and other tools, and most of the interactions of a user with its ledger is probably done via beanquery.

So, I am just wondering whether somebody can outline the roadmap for beanquery development?

My understanding of the current state

I may be mistaken, but it is my understanding, that at the moment beanquery seems to be supporting 2 modes:

The original mode. 

This mode has a formal original documentation.

In this mode:

• all queries are running only against the postings table

• FROM part is responsible for entry-level filter expressions.

• In this mode also accounting- specific additions to the SQL-like language  like JOURNAL, OPEN ON, CLOSE ON, BALANCES are available

The new mode:

• More (much more) tables available for query

beanquery> .tables

accounts

balances

commodities

documents

entries

events

notes

postings

prices

transactions

beanquery>

• The FROM part has a traditional role to select a table

• To activate this “new mode” one has to include the table name preceded with the #

E.g.

beanquery> select account, open.date from #accounts

The JOIN feature of the SQL is not supported (yet) but for instance postings table is already joint with the transactions table (where it is called entry)

beanquery> .describe postings

table postings:

  type (str)

  id (str)

  date (date)

  year (int)

  month (int)

  day (int)

  filename (str)

  lineno (int)

  location (str)

  flag (str)

  payee (str)

  narration (str)

  description (str)

  tags (set)

  links (set)

  posting_flag (str)

  account (str)

  other_accounts (set)

  number (decimal)

  currency (str)

  cost_number (decimal)

  cost_currency (str)

  cost_date (date)

  cost_label (str)

  position (position)

  price (amount)

  weight (amount)

  balance (inventory)

  meta (dict)

  entry (transaction)        <===== Joint transaction table

  accounts (set[str])

beanquery>

In this mode all the additional perks like JOURNAL, OPEN ON, CLOSE ON, BALANCES do not seem to be available.

So, as far as I can see, we have this duality (old vs new mode). This creates a situation, that “there are often more ways to kill the cat” (I am not complaining, just concluding).

E.g. the following queries will all produce the same result

beanquery> select narration where date = 2020-01-03

beanquery> select narration from #postings where date = 2020-01-03

beanquery> select entry.narration from #postings where date = 2020-01-03

Questions:

Q1: Is my understanding of the current state of the beanquery correct?

Q2: what is the roadmap for beanquery? 

Will 2 modes supported going further?

Will the perks like JOURNAL, OPEN ON, CLOSE ON, BALANCES be brought in the new mode as well?

What new features are planned to be added to beanquery?

What features are planned to be dropped from beanquery?

[Daniele Nicolodi]

On 06/05/25 13:33, Chary Ev2geny wrote:

> I may be mistaken, but it is my understanding, that at the moment
> beanquery seems to be supporting 2 modes:

There aren't really two modes of operation. More simply, the dialect of
SQL that beanquery implements, the Beancount Query Language (BQL) allows
to write queries in what you call "the original mode" syntax. The query
evaluation is carried out in the same way.

> The original mode.

> In this mode:

> FROM part is responsible for entry-level filter expressions.

You can have a WHERE clause when omitting the FROM clause to default to
the posting table or specify filtering expressions in both FROM and WHERE.

> The new mode:

The "new mode" is simply writing queries in a syntax closer to standard
SQL. Treating the FROM clause more traditionally allows to query
information other than the postings table.

> The JOIN feature of the SQL is not supported (yet) but for instance
> postings table is already joint with the transactions table (where it is
> called entry)

Technically, there is no JOIN (in the SQL sense) as there isn't a key
used to join records from two distinct tables. The postings table
returns records that include a reference to the transaction that
contains them.

> In this mode all the additional perks like JOURNAL, OPEN ON, CLOSE ON,
> BALANCES do not seem to be available.

Unless there is a bug somewhere, OPEN ON and CLOSE ON are supported also
when using the new syntax (although they do no always make sense).
JOURNAL and BALANCES can be thought as macros that expand to a regular
SELECT query. What do you mean when you write that the new mode does not
support them?

> Q1:Is my understanding of the current state of the beanquery correct?

There isn't really a distinction between what you call the "old mode"
and the "new mode". The BQL parser simply allows to write queries with
some shortcuts.

> Q2:what is the roadmap for beanquery?

There isn't a precise roadmap. Mostly because having one would imply
that there is at least the thought of resources dedicated to implement
it. The general idea is form beanquery to grow into a query tool that
can be used on more than beancount ledgers and to implement a wider
section of the SQL standard.

> Will 2 modes supported going further?

Supporting the old query syntax requires compromises that I would like
to revisit at some point. Having to prefix tables in the FROM clause
with # to disambiguate between a filter expression and a table name is
the most annoying. In the future (maybe the far future) specifying a
table with the FROM clause and using WHERE for filter expression will
become mandatory. Other things will be deprecated.

> Will the perks like JOURNAL, OPEN ON, CLOSE ON, BALANCES be brought in
> the new mode as well?

They already are, so I don't understand this comment.

> What new features are planned to be added to beanquery?

Next on my list are JOIN support (a partial implementation is already
available, but it require some more work) and support for windowed
functions, or at least a proper balance() function. Most likely other
things will be added as I need them (or as they are contributed)

> What features are planned to be dropped from beanquery?

None, really. Except for dropping some unfortunate syntax that will
force users to type a few more characters when composing a query.

Cheers,
Dan

[Chary Ev2geny]

Dan,

thanks for clarification.

On Tuesday, May 6, 2025 at 4:12:28 PM UTC+2 dan...@grinta.net wrote:

> In this mode all the additional perks like JOURNAL, OPEN ON, CLOSE ON,
> BALANCES do not seem to be available.

Unless there is a bug somewhere, OPEN ON and CLOSE ON are supported also
when using the new syntax (although they do no always make sense).
JOURNAL and BALANCES can be thought as macros that expand to a regular
SELECT query. What do you mean when you write that the new mode does not
support them?

let us look at the OPEN ON, as seems to be the one, which actually adds a new functionality, rather then just being a syntactic sugar

Suppose I have the following: ledger, 

============================

2020-01-01 open Assets:Bank USD

2020-01-01 open Assets:Cash

2020-01-01 open Equity:Initial-Balance

2020-01-01 open Expenses:Food

2020-01-01 * "Initial Balance"

    Assets:Bank          1000.00 USD

    Equity:Initial-Balance

2020-01-02 * "Buy Groceries 2020-01-02"

    Assets:Bank          

    Expenses:Food        1.00 USD

2020-01-03 * "Buy Groceries 2020-01-03"

    Assets:Bank          

    Expenses:Food        10.00 USD

2020-01-04 * "Buy Groceries 2020-01-04"

    Assets:Bank          

    Expenses:Food        100.00 USD

===============================

 

This one works

beanquery> select sum(position) where account ~ "^Expenses"                        
sum(positi
----------
111.00 USD

This one also works, when we switch to what I call "a new mode"

beanquery> select sum(position) FROM #postings where account ~ "^Expenses"                    
sum(positi
----------
111.00 USD

This one also works  in the "old mode"(but expectedly different result)

beanquery> select sum(position) FROM OPEN ON 2020-01-03 where account ~ "^Expenses"
sum(positi
----------
110.00 USD

But this one does not work (I use an OPEN ON with the #postings)

beanquery> select sum(position) FROM #postings OPEN ON 2020-01-03 where account ~ "^Expenses"
error: syntax error
| select sum(position) FROM #postings OPEN ON 2020-01-03 where account ~ "^Expenses"
|                                     ^
beanquery>

I am not sure, how much it is a limitation now, but what about the future, when JOIN functionality is introduced?

[Chary Ev2geny]

On Tuesday, May 6, 2025 at 4:12:28 PM UTC+2 dan...@grinta.net wrote:

or at least a proper balance() function. 

Do you mean, that you plan to change the functionality we struggled to understand here:

https://groups.google.com/g/beancount/c/lASto8Em3B4

and here:

https://groups.google.com/g/beancount/c/pEeDNcGhq3o/m/1lRQU5BxAwAJ

 

[Chary Ev2geny]

Dan,

please see below why I though that   OPEN ON is not supported on the "new mode"

On Tuesday, May 6, 2025 at 4:12:28 PM UTC+2 dan...@grinta.net wrote:

> In this mode all the additional perks like JOURNAL, OPEN ON, CLOSE ON,
> BALANCES do not seem to be available.

Unless there is a bug somewhere, OPEN ON and CLOSE ON are supported also
when using the new syntax (although they do no always make sense).
JOURNAL and BALANCES can be thought as macros that expand to a regular
SELECT query. What do you mean when you write that the new mode does not
support them?

So, I came to conclusion, that once you "activate" and new mode by adding a table name, preceded with #, then the OPEN ON does not work any longer