ERROR 42883 (undefined_function) function min(jsonb) does not exist

Questions / Help
1

I have a query for jsonb field which has 2 keys. sub and value

I want to implement an aggregate function on them to find the min value.

This is a query

a = from t in my_table, t.id == ^id, select: %{total: fragment("(? -> ?)::jsonb", t.product, "value")}

Then I have written subquery

from p in subquery(a), select: min(total)

But its giving me this error

function min(jsonb) does not exist

What can I do here?

2

You need to cast the result to a type which min can operate on: probably integer/bigint (or perhaps a float type like float8.)

Here you’re casting the result to jsonb, which isn’t helpful for two reasons: 1) ? -> ? would be of type jsonb regardless, as that is the type returned by ->; and 2) jsonb isn’t something you can take the min of, as it’s not necessarily true that any given pair of jsonb values can be meaningfully compared. (Is {"foo": 1} greater than or less than [{"bar": ["baz"]}]?)

By casting it to another type, you’d be asserting that the jsonb value in question is compatible with that type: i.e. that it’s not just any jsonb value, but rather specifically a jsonb number.

So: replace the cast to jsonb (::jsonb) with a cast to the relevant type (e.g. ::integer), and then your subquery will work, as it will then be taking the min of a type in which the values are mutually comparable.

1 Like
3

ah got it. Thanks :slight_smile: