Getting the latest (highest) key value from an object

Dear @vsr1 ,

I need a little bit of help if you have a few minutes to look over this query

WITH orderobj AS ({“us”:1, “gb”:2, “es”:3})
SELECT RAW appdoc
FROM app-live e
JOIN app-live AS a ON KEYS “meta:” || e.app_uuid || “:e:1”
WHERE e.sub_type= “app”
AND e.app_uuid IS NOT MISSING
AND e.game = TRUE
GROUP BY e.app_uuid
LETTING appdoc = MIN([IFMISSING(orderobj.[e.country_code],e.country_code), { e.country_code, e.app_thumbnail, a.dl}])[1]

the a.dl is an object of the format

"dl": {
      "ae": {
        "1614643200": 100,
        "1614816000": 100
      },
      "ar": {
        "1616457600": 100
      },
      "at": {
        "1614729600": 100,
        "1615075200": 100
      },
      "au": {
        "1614643200": 400,
        "1614729600": 300,
        "1614816000": 200
      }
}

I need to get the latest value in the object for a specific country
I have tried with a.dl.[e.country_code].[-1] but it doesn’t work, also it seems that I cannot use things like to_number() inside the creation of the appdoc object.

Is there a way to get the latest value (the highest key value) from the object?

@flaviu ,

It is OBJECT, not ARRAY to use subscript. Also object names can appear any order (not sorted)

LETTING appdoc = MIN([IFMISSING(orderobj.[e.country_code],e.country_code), { e.country_code, e.app_thumbnail, a.dl
, "leastvalue":  ARRAY_MIN(OBJECT_VALUES(a.dl.[e.country_code]))}])[1]
1 Like

Thanks,

It seems it is very close, but what I need is the value of the biggest key

so, for example, here:

"dl": {
      "ae": {
        "1614643200": 100,
        "1614816000": 100
      },
      "ar": {
        "1616457600": 100
      },
      "at": {
        "1614729600": 100,
        "1615075200": 100
      },
      "au": {
        "1614643200": 400,
        "1614729600": 300,
        "1614816000": 200
      }
}

for the country “au” it needs to return 200 because 1614816000 is the highest value from the 1614816000, 1614729600, 1614643200

It seems to be working doing this:

“leastvalue”: a.dl.[e.country_code].[ARRAY_MAX(OBJECT_NAMES(a.dl.[e.country_code]))]

is this the best way?

yes that is right way

Great, thanks so much for your help. As always you are amazing :slight_smile: