How can you use array v in v for array end as where clause

SQL++
27

@flaviu ,

MB-45296. Try following workaround.

SELECT RAW OBJECT v1.provider_uuid:v1.providerApps FOR v1 IN providers END
LET providers = (SELECT d1.provider_uuid , OBJECT v.app_uuid:v.appdoc FOR v IN arrayProviderApps END AS providerApps
                 FROM (SELECT d.provider_uuid, a.app_uuid, appdoc
                        FROM (WITH orderobj AS ({"us":1, "gb":2, "es":3, "fr":4})
                               SELECT p.provider_uuid, appuid, orderobj
                              FROM `bucket` AS p
                              UNNEST p.provider_apps_uuid AS appuid
                              WHERE p.type = "app-provider" AND p.sub_type = "provider-extra"
                                    AND p.provider_uuid IS NOT NULL) AS d
                        JOIN `bucket` AS a ON a.type = "tracker-app" AND a.sub_type= "app" AND a.app_uuid = d.appuid
                        GROUP BY d.provider_uuid, a.app_uuid
                        LETTING appdoc = MIN([IFMISSING(d.orderobj.[a.country_code],a.country_code), {a.country_code, a.app_name}])[1]) AS d1
                 GROUP BY d1.provider_uuid
                 LETTING arrayProviderApps = ARRAY_AGG({d1.app_uuid,d1.appdoc}));

FYI: orderobj as static in query you can add one document and select that document using USE KEYS.

WITH orderobj AS ((SELECT RAW o FROM bucket AS o USE KEYS “orderkeys”)[0])
WITH orderobj AS (OBJECT v:pos FOR pos:v IN [“us”,“gb”,“es”,“fr”] END)

If each provider want have its own order in the document as “orderlist”: [“us”,“gb”,“es”,“fr”]
SELECT p.provider_uuid, appuid, OBJECT v:pos FOR pos:v IN p.orderlist END AS orderobj

1 Like