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

SQL++
1

I have an array as a result of another query

  [
    "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"
  ]

the above is the result of “apps_array” query below

how can I use these values in another WHERE clause?

I am trying this query, and it doesn’t seem to work:

SELECT *
FROM `bucket` b
LET apps_array= (
    SELECT RAW apps_uuid
    FROM `bucket` 
    WHERE type = 'apps' )
WHERE b.type = 'boo'
    AND b.app_uuid = ARRAY v FOR v IN apps_array END

Definetly I am doing something wrong, but I cannot figure out what.

2

AND ANY v IN apps_array SATISFIES v = b.app_uuid END

OR

AND b.app_uuid IN apps_array

Also consider the following.

\checkout Subquery handling and potential query rewrite

WITH apps_array AS ( SELECT RAW apps_uuid
                     FROM `bucket` 
                     WHERE type = 'apps' )
SELECT b.*
FROM `bucket` b
WHERE b.type = 'boo'
    AND  ANY v IN apps_array SATISFIES b.app_uuid =  v END;

CREATE INDEX ix1 ON `bucket`(app_uuid) WHERE type = "boo";

OR

 WITH apps_array AS ( SELECT RAW apps_uuid
                         FROM `bucket` 
                         WHERE type = 'apps' )
    SELECT b.*
    FROM `bucket` b
    WHERE b.type = 'boo'
        AND   b.app_uuid IN apps_array ;

If apps_array produces too many elements use JOIN query

SELECT b.*
FROM `bucket`  AS a
JOIN `bucket` AS b ON a.apps_uuid = b.app_uuid AND b.type = "boo"
WHERE a.type = 'apps' ;
3

How much is too many ?

4

I don’t understand how you use the results of the apps_array in the join

5

this one returns an empty array. What could be the problem?

6

Low double digits are reasonable. If you more measure which option is better.

Left side of Join can be subquery that becomes your array and iterate each element.

empty array: checkout field names etc. that is what you provided.

7

it worked by doing this: AND b.app_uuid in apps_array[0]

Actually is not working… it is returning just one result.

8

hum that is not right try project apps_array and inspect json

9

this is what I get as a result:

What is strange is that I get one single app matched from the middle of the first array

[
  {
    "apps_array": [
      [
        "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"
      ],
      [
        "adebf0ba-a73c-42f8-a6e8-9a63c46ab511",
        "d310cd1a-8a6d-46ab-ae88-0473aee1920c",
        "615c6aa1-010e-424f-a52f-5db692641803",
        "d1a8541b-276a-4ded-9e27-2401765f5a0f",
        "b4c50480-236f-49ac-b95e-92dd8cb3bcb7",
        "9f5aa875-b6f6-43f4-a312-a4e003645ec8",
        "e758824b-6e06-4772-8d8f-fad2300f7a16",
        "ec732d04-ba65-45eb-9d81-0f4664c76ea9",
        "8bc6476b-7113-4736-8889-9d6642d9f43d",
        "51f7ab80-840f-4c17-bb7f-77af5e0eecb8",
        "d3656d5e-7d7f-4f06-977b-db74079c1a87",
        "88511e99-c329-4c11-b70d-68d16d79a70e",
        "ceedfbbe-a8d7-4b00-8670-7adad80665f8",
        "ad3823ee-6c43-46e9-abb3-48d9e310066d",
        "29754193-b70d-4737-8a49-5391bc082cd8",
        "f183ec91-5f07-48f7-9ebe-455a09011e60",
        "073e2720-fb87-492e-bf22-ad5367047ad9",
        "9f36392e-b6d9-4f84-8a83-baeb90ac3202",
        "8bb15413-bb17-4b59-802e-1d520d3b8ca3",
        "3adf94ea-79e5-44a3-b0ab-cabb0958d71d",
        "15dc2e14-9ef6-44e3-b762-b85d69ad74d7",
        "17e106e2-f2c0-4b12-9a1a-90b229439436",
        "5786d9d9-a675-491a-97a5-74d93263e252",
        "842796a2-2a6d-4fc4-bef5-6405d734554a",
        "c3be4386-4c29-4065-9001-9009e725b6a0",
        "61b94a07-58f2-4de6-9ed8-43781c4f439a",
        "c062ad2d-412d-4ae4-9234-6627d1ee781d",
        "5fd6159b-0ace-4af8-9fd4-4422a3f89719",
        "f9b5244c-748d-4c94-b121-6280668c2047",
        "6d3106a1-a226-41a7-b856-dad1fc649dcd",
        "e73a06e6-def8-4c49-a364-75553a49f6f2",
        "742595c4-555e-4e6a-a7c9-02260af88161",
        "a807d6de-5e08-4ae7-8e81-c60e90370cde",
        "063d286d-1142-4620-b593-f584d5e424c1",
        "3250d80b-f8b3-4554-9fd7-41ede1ee83aa",
        "cebaf8f2-7995-4e5e-a87a-8c8db997f097",
        "514c1063-1619-409c-9ace-87920c982fa7",
        "42271eca-12ba-45eb-bbc2-24b8020f7341",
        "f35cffa2-f652-4084-964a-e4199aaf1a2f",
        "ac813cf0-7576-4da1-804f-8acffe23fb91",
        "238205de-ae2b-4530-838f-b4d2b46cb90b",
        "c68ed892-6b07-4793-a025-8a76e573132d",
        "8d35c40d-4ae1-4731-ba25-047bd030b43d",
        "60aa5fc4-f1f6-495b-8191-071d6ad3676e",
        "8f91108e-de41-4c22-855f-c6865e6f4cd4",
        "f2a1a480-285c-4c39-ab87-33f036a02081",
        "7fa3237b-5ac4-4f7b-9238-11500651f2f2",
        "b5ba8cba-225f-48ea-847e-eef9cc35ae5e",
        "b3793488-5df1-4177-8a51-f74d1e0d3d32",
        "b844d1b1-b114-47a5-9043-11f29f7a49dd",
        "6ab14e75-2355-4f83-9a5d-8c606f61c886",
        "98e24dc4-5c40-4027-810e-f24b1f00be7f",
        "1352c6dd-fd92-487e-b00b-9dc3a0b60a75",
        "3a177906-9367-4234-96e8-d8b787ac3a5a",
        "435bbdb0-ebf0-4903-88de-09d7858285fa",
        "ef170ee4-7ecf-4f01-bdc3-1e744acb6981",
        "085cebe5-6ffd-4a6f-8d3d-3f8277ae1f46",
        "7153517d-1218-49f2-9f70-bb04a7b6a84b",
        "785bba04-2724-4d76-966b-0b03f55dd022",
        "35beca93-ea78-4d3e-a05c-fa48fc15e824",
        "f51a480e-44ec-40fb-b1bd-9179d0897fb4",
        "59099a21-8225-4356-a031-29743891c4fb",
        "d970ed3b-8e0b-4afa-9dac-e13d3554f78e",
        "9ce3be0a-d59f-43f8-97dc-4794f279432c",
        "e5044d50-1d2b-49a1-9552-cae133aca303",
        "47a953a7-a0bb-4d4e-9be4-ccd411e2d1b1",
        "93144165-0feb-40a3-bf81-e1576ecad590",
        "1e74b8bf-2ee9-431b-95fd-51e91163a8ef",
        "464a0f11-ca8c-42f0-a506-d334917de866",
        "5bf48a77-0dfc-4cdc-a31d-bd16d9d4f0d6",
        "9a92240b-75a6-45a7-a2d4-efe71f8b2af9"
      ]
    ],
    "b": {
      "apps": {
      "app_uuid": "126061ba-4503-4dd3-b5ce-c5f8c7fd1f75",
      "country_code": "us",
      "created_timestamp": 1602812486,
      "key": "meta:126061ba-4503-4dd3-b5ce-c5f8c7fd1f75:us",
      "should_refresh": false,
      "sub_type": "app",
      "type": "tracker-app",
      "updated_timestamp": 1616717568,
      "version": 2
    }
  }
]
10

It looks like apps_uuid is ARRAY, apps_array become ARRAY of ARRAYS. You must do FLATTEN or UNNEST then do DISTINCT

WITH apps_array AS ( ARRAY_DISTINCT(ARRAY_FLATTEN((SELECT RAW apps_uuid
                         FROM `bucket` 
                         WHERE type = 'apps' ),1)))
    SELECT b.*
    FROM `bucket` b
    WHERE b.type = 'boo'
        AND   b.app_uuid IN apps_array ;
11

yes, it makes sense

The problem I have is that it becomes clear that going this path will not work… I need to rethink how to do this better.

It is not your fault. You did great, and I REALY-REALY appreciate your help, is that I just cannot grasp my head around my problem.

12

As you have so many elements the following will JOIN query if you want try.

SELECT b.*
FROM (SELECT DISTINCT  RAW au
      FROM `bucket` AS a 
      UNNEST a.apps_uuid AS au
      WHERE a.type = "apps") AS a1
JOIN `bucket` AS b ON a1 = b.app_uuid AND b.type = "boo";

CREATE INDEX ix1 ON `bucket`(app_uuid) WHERE type = "boo";
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

14

How many countries you are talking about it. what is it names and order

15

there are 155 countries, but not sure what you ask me in the last question. What name and order do you refer?

16

prioritized countries

17

this is the order of the countries:

orderobj AS ({“us”:1, “gb”:2, “es”:3, “fr”:4})

the reason the languages are important is related to the fact that if we do a select * from bucket where app_uuid = "xxx" we will get 150 results, but the problem si that not all the apps have all the countries, so if we hardcode let’s say “US” or “GB” (select * from bucket where app_uuid = "xxx" and country_code = ‘us’) we may end up with no app details. So ideal would be to order countries by country , and always use the first result. In this way we will always have one result starting with english speaking languages and the spanish, etc

Or maybe you have another idea…

18

@flaviu ,

Try this. MIN([IFMISSING(orderobj.[a.country_code],a.country_code), {a.country_code, a.app_name}])[1]
If entry is in the ordered country it prioritize if not the use country_code order and gives MIN document

CREATE INDEX ix1 ON `bucket` (app_uuid, app_name) WHERE type = "tracker-app" AND sub_type= "app";
CREATE INDEX ix2 ON `bucket` (provider_uuid, provider_apps_uuid) WHERE type = "app-provider" AND sub_type= "provider-extra";


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 ( WITH orderobj AS ({"us":1, "gb":2, "es":3, "fr":4})
                        SELECT d.provider_uuid, a.app_uuid, appdoc
                        FROM (SELECT p.provider_uuid, appuid
                              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(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}));
19

Give me a sec please to see why I get an empty result

20

Then start with inner most subquery and see where it went wrong. Also made so many updates try latest

INSERT INTO default VALUES (UUID(), {"app_name": "abcus", "app_uuid": "ap1", "country_code": "us","type": "tracker-app" , "sub_type":"app"});
INSERT INTO default VALUES (UUID(), {"app_name": "abcgb", "app_uuid": "ap1", "country_code": "gb","type": "tracker-app" , "sub_type":"app"});
INSERT INTO default VALUES (UUID(), {"app_name": "ap2us", "app_uuid": "ap2", "country_code": "us","type": "tracker-app" , "sub_type":"app"});
INSERT INTO default VALUES (UUID(), {"app_name": "ap3es", "app_uuid": "ap3", "country_code": "es","type": "tracker-app" , "sub_type":"app"});
INSERT INTO default VALUES (UUID(), {"provider_uuid": "p1", "provider_apps_uuid": ["ap1","ap2","ap3"],"type": "app-provider" , "sub_type":"provider-extra"});
INSERT INTO default VALUES (UUID(), {"provider_uuid": "p2", "provider_apps_uuid": ["ap1","ap2","ap4"],"type": "app-provider" , "sub_type":"provider-extra"});

CREATE INDEX ix1 ON `default` (app_uuid, app_name) WHERE type = "tracker-app" AND sub_type= "app";
CREATE INDEX ix2 ON `default` (provider_uuid, provider_apps_uuid) WHERE type = "app-provider" AND sub_type= "provider-extra";

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 ( WITH orderobj AS ({"us":1, "gb":2, "es":3, "fr":4})
                        SELECT d.provider_uuid, a.app_uuid, appdoc
                        FROM (SELECT p.provider_uuid, appuid
                              FROM `default` 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 `default` 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(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}));

{
    "requestID": "ce6d85c7-5203-490b-8915-a879fd2de74e",
    "signature": "object",
    "results": [
    {
        "p1": {
            "ap1": {
                "app_name": "abcgb",
                "country_code": "gb"
            },
            "ap2": {
                "app_name": "ap2us",
                "country_code": "us"
            },
            "ap3": {
                "app_name": "ap3es",
                "country_code": "es"
            }
        },
        "p2": {
            "ap1": {
                "app_name": "abcgb",
                "country_code": "gb"
            },
            "ap2": {
                "app_name": "ap2us",
                "country_code": "us"
            }
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "7.450498ms",
        "executionTime": "7.389728ms",
        "resultCount": 1,
        "resultSize": 608,
        "serviceLoad": 2
    }
}