Query with object inside other objects

SQL++
1

Hi @vsr1

I am sytrugling with a query, and I was hopeing you could help me:

I have these documents:

document with key: app-rank_top-free_us_1619618400

 {
      "chart_apps": {
        "293778748": 148,
        "329218549": 181,
        "346453382": 485
      },
      "country_code": "us",
      "chart_timestamp": 1619618400,
      "type": "app-rank",
      "version": 1

}

Document with key:
app:a2t:293778748 (the number is the key from the chart_apps object above)

{
  "app_id": 293778748",
  "app_uuid": "070899ba-5e93-4905-bc1d-375d636f7eff",
  "sub_type": "a2t",
  "key": "app:a2t:293778748",
  "created_timestamp": 1602806431,
  "type": "app-mapping",
  "version": 2
}

Document with key:
meta:070899ba-5e93-4905-bc1d-375d636f7eff:us // meta:app_uuid:country_code

{
  "app_uuid": "070899ba-5e93-4905-bc1d-375d636f7eff",
  "sub_type": "app",
   "user_rating_avg": 4.15874,
   "user_rating_count": 18004
  "created_timestamp": 1602806763,
  "type": "app-tracker",
  "version": 2,
  "updated_timestamp": 1619495915
}

I have this query:

SELECT RAW OBJECT v3.app_uuid:d.chart_apps.[TO_STR(v3.app_id)] FOR v3 IN app_key END
FROM `tracker` d USE KEYS ["app-rank_top-free_us_1619618400", "app-rank_top-paid_dz_1619618400", "app-rank_top-free_ao_1619618400", "app-rank_top-free_ai_1619618400"]
LET app_key = (
    SELECT d2.app_uuid AS app_uuid,
           d2.app_id AS app_id
    FROM `app-live` AS d2 USE KEYS ARRAY "app:a2t:" || v FOR v IN OBJECT_NAMES(d.chart_apps) END)

and I am getting these results:

[
  {
    "070899ba-5e93-4905-bc1d-375d636f7eff": 298,
    "012496e0-1be7-4158-a220-acc1c0a14dbb": 272
 },
 { 
    "001dfa94-c107-42b6-bfac-83266e567eaa": 211,
    "00a802da-390e-46e6-951d-d4d93489a2a2": 61
 }
]

Now, what I am trying to achieve is a long list (approx 50k appuids in groups of approx 500) similar to this one:

 [
   {
      "070899ba-5e93-4905-bc1d-375d636f7eff":{
         "rank":148,
         "user_rating_avg":4.15874,
         "user_rating_count":18004
      },
      "001c4696-75cd-40c8-a9d1-f604f612dd23":{
         "rank":181,
         "user_rating_avg":2.3543,
         "user_rating_count":234234
      },
      "f604f612dd23d-a9d1-bc1d--375d636f7eff":{
         "rank":485,
         "user_rating_avg":4.254,
         "user_rating_count":6545656
      }
   },
   {
      "9b136f66-87fa-4907-95c6-f4db4a8445b6":{
         "rank":148,
         "user_rating_avg":4.67,
         "user_rating_count":3456
      },
      "ce42ca64-15da-479d-9666-f774c3c607b9":{
         "rank":181,
         "user_rating_avg":3.654,
         "user_rating_count":654
      },
      "010f764a-128c-4cd8-b2f3-2439bd9d5495":{
         "rank":485,
         "user_rating_avg":1.654,
         "user_rating_count":87
      }
   }
]

I don’t know how to create an object inside another object …

I would appreciate any help

Thank you,

2 
WITH apps AS ( SELECT RAW {at.app_uuid: {at.user_rating_avg, at.user_rating_count, "rank":ar.chart_apps.[am.app_id]}}
               FROM tracker AS ar USE KEYS ["app-rank_top-free_us_1619618400",
                                            "app-rank_top-paid_dz_1619618400",
                                            "app-rank_top-free_ao_1619618400",
                                            "app-rank_top-free_ai_1619618400"]
               JOIN tracker AS am ON KEYS (ARRAY "app:a2t:" || v FOR v IN OBJECT_NAMES(ar.chart_apps) END)
               JOIN tracker AS at ON KEYS "meta:" || am.app_uuid || ":" || ar.country_code
               WHERE ar.type = "app-rank" AND am.type = "app-mapping" AND at.type = "app-tracker")
SELECT RAW ARRAY_AGG(a)
FROM 1 AS d
UNNEST apps AS a
GROUP BY IMOD(UNNEST_POS(a),50);
3

let me see this sorcery

4

try this.

WITH apps AS ( SELECT at.app_uuid, at.user_rating_avg, at.user_rating_count, ar.chart_apps.[am.app_id] AS rank
               FROM tracker AS ar USE KEYS ["app-rank_top-free_us_1619618400",
                                            "app-rank_top-paid_dz_1619618400",
                                            "app-rank_top-free_ao_1619618400",
                                            "app-rank_top-free_ai_1619618400"]
               JOIN tracker AS am ON KEYS (ARRAY "app:a2t:" || v FOR v IN OBJECT_NAMES(ar.chart_apps) END)
               JOIN tracker AS at ON KEYS "meta:" || am.app_uuid || ":" || ar.country_code
               WHERE ar.type = "app-rank" AND am.type = "app-mapping" AND at.type = "app-tracker")
SELECT RAW OBJECT v.app_uuid:{v.user_rating_avg, v.user_rating_count, v.rank} FOR v IN ag END
FROM 1 AS d
UNNEST apps AS a
GROUP BY IMOD(UNNEST_POS(a),50)
LETTING ag = ARRAY_AGG(a);
1 Like
5

for some reason I am getting an empty results on both querys

Seems that the first join is not really working … but don;t understand why

Still looking into it

6

Actually it is working, it was a problem of bucket (for both joins)

SELECT RAW OBJECT v.app_uuid:{v.user_rating_avg, v.user_rating_count, v.rank} FOR v IN ag END
FROM 1 AS d
UNNEST apps AS a
GROUP BY IMOD(UNNEST_POS(a),50)
LETTING ag = ARRAY_AGG(a);

This is very cool. I didn’t meant to have them split like this (I justr tried to explain how the result should look like based on the number of apps in the chart_apps object.

But I learned something new.

Thank you @vsr1, again, you have saved me hours of work. You are fantastic!

7

I think I have a small problem

What happens is that if multiple documents “app-rank_top-free_us_1619618400”, “app-rank_top-paid_dz_1619618400”,
“app-rank_top-free_ao_1619618400”, “app-rank_top-free_ai_1619618400” have the same app_uuid they are rewritten, they do not remain as individual “documents”

so if the rank in app-rank_top-free_us_1619618400 is 234 and in app-rank_top-free_ao_1619618400 the value is 888 I don’t get two results

8

Then you need to form the key of object with app_uid:country_code
Or use ARRAY

9

GROUP BY app_uid

"070899ba-5e93-4905-bc1d-375d636f7eff": [{
         "rank":148,
         "user_rating_avg":4.15874,
         "user_rating_count":18004
      },
     {
         "rank":181,
         "user_rating_avg":2.3543,
         "user_rating_count":234234
      }]
10

it should be something like this I think, right?

  SELECT RAW OBJECT v.app_uuid:{v.user_rating_avg, v.user_rating_count, v.rank} FOR v IN a END
  FROM 1 AS d
  UNNEST apps AS a
  GROUP BY a.app_uuid

but it is not working

Expression object (v.app_uuid) : {“rank”: (v.rank), “user_rating_avg”: (v.user_rating_avg), “user_rating_count”: (v.user_rating_count)} for v in a end must depend only on group keys or aggregates."

11

SELECT RAW { a.app_uuid:ag}
FROM 1 AS d
UNNEST apps AS a
GROUP BY a.app_uuid
LETTEING ag = ARRAY_AGG({a.user_rating_avg, a.user_rating_count, a.rank})

Each app_uid gives as separate object. If u need all of them into one or split 50 each let me know

If you really don’y want to split then use this. Check keyspaces.

SELECT RAW { at.app_uuid:ag}
FROM tracker AS ar USE KEYS ["app-rank_top-free_us_1619618400",
                             "app-rank_top-paid_dz_1619618400",
                             "app-rank_top-free_ao_1619618400",
                             "app-rank_top-free_ai_1619618400"]
JOIN tracker AS am ON KEYS (ARRAY "app:a2t:" || v FOR v IN OBJECT_NAMES(ar.chart_apps) END)
JOIN tracker AS at ON KEYS "meta:" || am.app_uuid || ":" || ar.country_code
WHERE ar.type = "app-rank" AND am.type = "app-mapping" AND at.type = "app-tracker")
GROUP BY at.app_uuid
LETTEING ag = ARRAY_AGG({at.user_rating_avg, at.user_rating_count, "rank":ar.chart_apps.[am.app_id]});
1 Like
12

That’s it, fantastic :slight_smile:

13

oh, I am so-so-so-so happy :slight_smile: