yes, it is working, the only problem seems to be with the country code
but, man, you are incredible … how can you do these without even seeing the data … that’s speechless
in my case I am always getting a country which is not in our list of countries (orderobj )
If you add another entry:
INSERT INTO default VALUES (UUID(), {“app_name”: “abcus”, “app_uuid”: “ap1”, “country_code”: “it”,“type”: “tracker-app” , “sub_type”:“app”});
does it return “IT” as country codes?
ON a.type = “tracker-app” AND a.sub_type= “app” AND a.app_uuid = d.appuid
appuid is “ap1” it should return 3 documents of country “us”, “gb”, “it”
MIN([IFMISSING(orderobj.[a.country_code],a.country_code), {a.country_code, a.app_name}])[1])
makes this as [1,{…}], [2, {…}], [“it”,{]] so it picks “us”
orderobj.[a.country_code] only converts in the list ordered number. In case of “it” there is none so it leaves as string
MIN number is lower than string.
In above case if it gives “it”, “in” none of document qualify in ordered list then it picks “in” (sorted by country_code string)
ok, but then why do I get these results?:
[
{
"83d33d5f-e430-43f7-b489-ad03efeeb89c": {
"126061ba-4503-4dd3-b5ce-c5f8c7fd1f75": {
"app_name": "Heroes Saga",
"country_code": "ae"
},
"150956cd-cf33-4fed-ae62-11b40b370fee": {
"app_name": "Saga",
"country_code": "ae"
}
}
}
]
and the app exists for country “us”
{
"app_uuid": "150956cd-cf33-4fed-ae62-11b40b370fee",
"country_code": "us",
"app_name": "Saga",
"key": "meta:150956cd-cf33-4fed-ae62-11b40b370fee:us",
"created_timestamp": 1602815218,
"type": "tracker-app",
"version": 2,
"updated_timestamp": 1616718319
}@flaviu ,
Check your data or conditions. You don’t have sub_type field in the document so the document is eliminated by a.type = “tracker-app” AND a.sub_type= “app” then went and picked next one in the qualified list based on MIN.
That’s not it, what I have pasted was just a small part of the document in which by mistake I have omitted the sub_type…
If I do a count instead of min I get 155 (which is the correct number of countries) so, it seems that I have all the countries from that join
also, one more thing that is happening is that if I change MIN with MAX instead of “ae” as a country, I get “zw”
so, I added to your above list of value another one with country “ae”
INSERT INTO default VALUES (UUID(), {“app_name”: “abcgb-ae”, “app_uuid”: “ap1”, “country_code”: “ae”,“type”: “tracker-app” , “sub_type”:“app”});
and now, if you re-run your query again, you will see that AP1 will become “ae” instead of “us”
So, I think the MIN doesn’t work correctly (or in the way we expect)
@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
Yes, it is working, thank you so much!