Horizontal Filtering on UTxO Lovelace Value

[fallen-icarus]

I'm trying to filter out UTxOs that contain less than 5 ADA, but since Koios returns the value as a string, the horizontal filtering is not working as I would expect. I am not sure if I am doing something wrong, or if this is not actually possible.

This is the base query I am using.

curl -X POST 'https://preprod.koios.rest/api/v1/address_utxos?select=value'  -H "accept: application/json" -H "content-type: application/json"  -d '{"_addresses":["addr_test1zqql5djxthlrdcnvy87m7uswf0d0es9cdw6nvl72gcqj74h0c7g4mfe8tn70pveep8eep4dzuu0f784unh4wk5p7jkaszryq59"],"_extended":true}' | jq

It returns:

[
  {
    "value": "2629100"  # 2.629100 ADA
  },
  {
    "value": "17769460" # 17.769460 ADA
  },
  {
    "value": "7635150" # 7.635150 ADA
  },
  {
    "value": "3420480" # 3.420480 ADA
  },
  {
    "value": "7426530" # 7.426530 ADA
  },
  {
    "value": "3435150" # 3.435150 ADA
  },
  {
    "value": "6787147" # 6.787147 ADA
  },
  {
    "value": "6976927" # 6.976927 ADA
  },
  {
    "value": "12706120" # 12.706120 ADA
  }
]

I've tried this:

curl -X POST 'https://preprod.koios.rest/api/v1/address_utxos?select=value&value=gte.5000000'  -H "accept: application/json" -H "content-type: application/json"  -d '{"_addresses":["addr_test1zqql5djxthlrdcnvy87m7uswf0d0es9cdw6nvl72gcqj74h0c7g4mfe8tn70pveep8eep4dzuu0f784unh4wk5p7jkaszryq59"],"_extended":true}' | jq

but it returns:

[
  {
    "value": "7635150"
  },
  {
    "value": "7426530"
  },
  {
    "value": "6787147"
  },
  {
    "value": "6976927"
  }
]

It is missing the UTxOs with more than 10 ADA. It appears to be doing a text comparison rather than a numerical one. So I tried casting the text to a bigint type like:

curl -X POST 'https://preprod.koios.rest/api/v1/address_utxos?select=value::bigint&value::bigint=gte.(5000000::bigint)'  -H "accept: application/json" -H "content-type: application/json"  -d '{"_addresses":["addr_test1zqql5djxthlrdcnvy87m7uswf0d0es9cdw6nvl72gcqj74h0c7g4mfe8tn70pveep8eep4dzuu0f784unh4wk5p7jkaszryq59"],"_extended":true}' | jq

but it returns the same result as before, just as integers instead of text.

Am I doing something wrong or is it not actually possible to do horizontal filtering on the ada value of a UTxO?

---

Possible Bug

The last curl query returns the entire list at least 10% of the time. This leads me to believe there is a bug in Koios.

[rdlrt]

This is something limited on PostgREST end for now, as adding filter/order on a cast exposes a large surface attack for DDoS, especially when table is pretty huge (like in this case would be tx_out, second largest table on dbsync).

The original requirement to have this casted as text came due to ability for parsing numbers that could go into quadrillion, which would end up mucking most parsers into expressing exponential formats. This resulted in all fees/ADA values/asset quantities being turned where possible into text format.

Will need to have a think about it before adding a change - as I see this as a valid requirement too (which is why we originally had it as lovelace/numeric), but required switching due to above-mentioned issue. Some potential alternatives could be use of alternate endpoints or duplication of value field, both not very graceful IMO.

[fallen-icarus]

This isn't a deal breaker for me; I can just do the extra filtering locally for now.

[rdlrt]

Thanks - will put it to backlog for now, but leave it open as it's something we would want to tackle, perhaps could be a candidate for lite node.