Filtering queries based on account metadata

[Shaarad Dalvi]

Hi folks,

I have a few accounts decorated with some metadata - like for fixed deposit accounts, I put the rate of interest as the metadata

2024-07-01 open Assets:MyBank:MyFixedDeposit1 INR

  rate-of-interest: "7.2%"

... And so on for other deposit accounts

I am wondering if I can somehow see "the fixed deposit accounts that are giving me the least rate of interest right now"?

I tried using entry_meta and any_meta but no success so far. 

Furthermore, if filtering on account metadata is possible, is it possible to cast the metadata value of a given key to another data type (like integer/float) and ORDER BY that?

Thanks!

[Matt Hellige]

I'm not sure whether it's possible to cast the data type, but I do know that selecting and ordering by account metadata are both possible, if obscure. Try something like this:

  select getitem(open_meta(account), 'rate-of-interest') as rate, account order by rate

Note this is still selecting transactions, not accounts directly, so you won't see rows for any accounts that don't have transactions, etc. Anyway, it does show how you can include account metadata in your queries.

Matt

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAL1KmEGhzeBac426ge0KRZL_PeNyg3CK6fqr-tpXYBXgut3-xw%40mail.gmail.com.

[Daniele Nicolodi]

On 13/07/24 17:29, Shaarad Dalvi wrote:
> Hi folks,
>
> I have a few accounts decorated with some metadata - like for fixed
> deposit accounts, I put the rate of interest as the metadata
>
> 2024-07-01 open Assets:MyBank:MyFixedDeposit1 INR
>   rate-of-interest: "7.2%"
>
> ... And so on for other deposit accounts
>
> I am wondering if I can somehow see "the fixed deposit accounts that are
> giving me the least rate of interest right now"?

beanquery defines a few more tables beside the 'postings' table. For a
list, see the output of the '.tables' command. Among these there is also
the 'accounts' table. For a description of the columns of the table, see
the output of the '.describe accounts' command:

beanquery> .describe accounts
table accounts:
account (str)
open (open)
close (close)

And the output of the '.describe open' command for a definition of the
'open' structured data type:

beanquery> .describe open
structured type open:
meta (metadata)
date (date)
account (str)
currencies (list)
booking (booking)

With this information, you can build this query:

SELECT
account,
open.meta['rate-of-interest']
FROM
#accounts
ORDER BY 2 DESC, 1

> I tried using entry_meta and any_meta but no success so far.

As the name suggest, these functions return metadata for the entry and
for the posting or the entry if the former is not present.

> Furthermore, if filtering on account metadata is possible, is it
> possible to cast the metadata value of a given key to another data type
> (like integer/float) and ORDER BY that?

In your example the 'rate-of-interest' metadata field is a string. The
easiest would be to encode it as a decimal, for example:

2024-07-01 open Assets:MyBank:MyFixedDeposit1 INR
rate-of-interest: 7.2

then there is nothing to do to get it interpreted correctly as a decimal
number in beanquery. Otherwise, you need to parse the numerical part out
of the string, which gets a bit clumsy:

SELECT
account,
decimal(substr(str(open.meta['rate-of-interest']), 0, -1)) AS interest
FROM
#accounts
ORDER BY 2 DESC, 1

Metadata values do not have a defined data type in Beancount, thus BQL
(the Beancount Query Language) is treats metadata as a dictionary that
uses strings as keys and untyped object as values. The str() function in
the query above coerces the metdata value to a string. The substr()
extracts the string part before the '%' sign, and decimal() parses the
resulting string as a decimal value (BQL does not have a floating point
data type).

Cheers,
Dan