Iterate on an defined object

SQL++
1

@vsr1 quick question :slight_smile:

Trying to make this query to work but I cannot get the v.cc in the bellow query

WITH data AS ([{"cc":"us", "app_id":"123"},{"cc":"us", "app_id":456"}])
SELECT RAW "meta:" || d2.app_uuid || ":" || v.cc
FROM `bucket` AS d2 USE KEYS ARRAY "app:a2t:" || v.app_id FOR v IN data END

What I am doing wrong?

2

v is not available in projection

WITH data AS ([{"cc":"us", "app_id":"123"},{"cc":"us", "app_id":"456"}])
SELECT RAW "meta:" || d2.app_uuid || ":" || FIRST v1.cc FOR v1 IN data WHEN "app:a2t:" || v1.app_id  == META(d2).id END
FROM `bucket` AS d2 USE KEYS ARRAY "app:a2t:" || v.app_id FOR v IN data END

OR

   WITH data AS ([{"cc":"us", "app_id":"123"},{"cc":"us", "app_id":"456"}])
    SELECT RAW "meta:" || d2.app_uuid || ":" ||  d.cc 
    FROM  data AS d  
    JOIN `bucket` AS d2 ON KEYS "app:a2t:" || d.app_id ;
1 Like
3

Thanks, the second options seems cleaner :slight_smile:

4

I still need a little bit of help

WITH data AS ([{"cc":"us", "app_id":"123"},{"cc":"gb", "app_id":"123"}])
SELECT OBJECT TO_STR(v3.app_id):v3 FOR v3 IN applications END AS apps
LET applications = (
    SELECT app_uuid,
           app_id,
           bla,
           foo,
          bla1
         country_code
    FROM `bucket` USE KEYS ARRAY v2 FOR v2 IN (
        SELECT RAW "meta:" || d2.app_uuid || ":" || d.cc
        FROM data AS d
            JOIN `app-live` AS d2 ON KEYS "app:a2t:" || d.app_id ) END)

and I am trying to get the resutls in this format:

{
   "apps":{
      "123":{
         "us":{
            "app_uuid":"123-abc",
            "country_code":"us"
         },
         "gb":{
            "app_uuid":"456-abc",
            "country_code":"gb"
         }
      }
   }
}

I tried to do the following and it is not working, I also tried with add_object or create another object inside the return of the previous select (applications) and nothing seems to work. Can I do what I want?

SELECT OBJECT TO_STR(v3.app_id.[to_str(v3.country.code)]):v3 FOR v3 IN applications END AS apps

5 
SELECT OBJECT v1.app_id:v1.av FOR v1 IN applications END AS apps
LET applications = (SELECT d.app_id, OBJECT v.country_code:v.acv FOR v IN avl END AS av
                    FROM (WITH datakeys AS ( WITH data AS ([{"cc":"us", "app_id":"123"},{"cc":"gb", "app_id":"123"}])
                                             SELECT RAW "meta:" || d2.app_uuid || ":" || d.cc
                                             FROM data AS d
                                             JOIN `app-live` AS d2 ON KEYS "app:a2t:" || d.app_id )
                          SELECT b.app_uuid,
                                 b.country_code,
                                 ARRAY_AGG({b.app_uuid, b.bla, b.foo, b.bla1, b.country_code }) AS acv
                          FROM `bucket` AS b USE KEYS datakeys
                          GROUP BY b.app_id, b.country_code) AS d
                    GROUP BY d.app_id
                    LETTING avl = ARRAY_AGG({d.country_code, d.acv}) );