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

SQL++
13

The problem is that what I need is more complicated

I need to get a result in the format of

{
   "provider_uuid":{
      "app_uuid":{
         "app_name":"foo",
         "country_code":"us"
      },
      "app_uuid1":{
         "app_name":"foo1",
         "country_code":"gb"
      }
   },
   "provider_uuid1":{
      "app_uuid2":{
         "app_name":"foo2",
         "country_code":"us"
      },
      "app_uuid3":{
         "app_name":"foo3",
         "country_code":"us"
      }
   }
}

what is very complicated is that

I am starting from a list of providers

and with this list I am getting the providers document which are in the below format:

{
  "provider_uuid": "83d33d5f-e430-43f7-b489-ad03efeeb89c",
  "provider_apps_uuid": [
    "545b071b-2dd1-42f0-b9fa-4f821e10bd6f",
    "210dbf5e-d78c-4554-ba3a-059f2683d62b",
    "f4d789a7-2a5d-4052-b307-7daac9b91202",
    "3781b6c0-51ca-4a29-bfd0-0ac4e87f66cf",
    "c9caa387-5c02-400f-8239-cebcafbc6c84",
    "126061ba-4503-4dd3-b5ce-c5f8c7fd1f75",
    "838e800b-412f-4d0c-aea0-22eb07908b81",
    "b05de374-5ea6-4d31-ba56-48b74015da9c",
    "150956cd-cf33-4fed-ae62-11b40b370fee",
    "ba9b73cf-4fe7-4e2c-a21d-bc31f61b11f1",
    "74857707-25d0-4736-8f5c-c90193c99f8f",
    "da6c7301-8e8e-4e7d-bfcc-7ea4ab1a00e8",
    "75527edb-84fc-4c94-bb47-ef1b823c517a",
    "d0f2843d-74eb-4149-a34e-ac392e73fd20",
    "43f47260-3604-46d5-9f08-e2b1793ae0bd",
    "7b3a1d67-0968-4967-aa21-973c439c3a21",
    "702512e0-fa8c-4d37-a3a7-7a99873350b8",
    "4e57286e-8d03-4fc1-bb41-3ee20e67318e",
    "3d7b101b-9360-4570-ace3-0074d06e86a6"
  ],
  "sub_type": "provider",
  "key": "provider:83d33d5f-e430-43f7-b489-ad03efeeb89c",
  "created_timestamp": 1602810162,
  "type": "app-provider",
  "version": 2
}

so, from this provider document, I need to get the list of apps (provider_apps_uuid), but nowI have multiple documents with the same app_uuid but with different country_codes (that’s why I had the other question regarding how I can order by country_code) because I was hoping that I can order by country code and limit 1, to get the first available country and not all of them.

so, now, the query is like this:

WITH apps_array AS (
select raw provider_apps_uuid  from `bucket` use keys 
(SELECT raw "provider:" || provider_uuid 
FROM `bucket` d where type = 'app-provider' and sub_type = 'provider-extra' ))
, orderobj AS ({"us":1, "gb":2, "es":3, "fr":4})

SELECT  b.app_uuid, b.country_code
FROM `bucket` b
WHERE b.type = 'tracker-app'
    AND b.sub_type= 'app'
    AND b.app_uuid in apps_array
ORDER BY orderobj.[b.country_code] NULLS LAST limit 1;

but if I do this, the last “limit 1” now is limiting the apps_array iteration not the first available app_details from the list of countries… also, I need to group them as in the above JSON structure … so it seems like this is mission impossible