Query array for 'sub-array'

SQL++
1

I have two docs:

myStore:
{
“petDepartment”: {
“animals”: {
“mammals”: {
“canines”: [
“husky”,
“golden retriever”,
“daschund”,
“labrador retriever”,
“greyhound”,
“poodle”,
“golden doodle”
]
}
}
}
}

and

myStuff
{
“owner”: “My Name”,
“items”: {
“pets”: {
“canines”: [
“husky”,
“daschund”
]
}
}
}

myStore has all the items I can buy and myStuff has the items I own. I would like to find all the canines in the store that I don’t have.

I have tried a few different queries:
SELECT RAW petDepartment.animals.mammals.canines
FROM b4tPools AS myStore USE KEYS "myStore"
EXCEPT ALL
SELECT RAW items.pets.canines
FROM b4tPools USE KEYS “myStuff”

and

SELECT allMyStuff.owner,
(SELECT myStore.petDepartment.animals.mammals.canines
FROM b4tPools AS myStore USE KEYS "myStore"
WHERE myStore.petDepartment.animals.mammals.canines NOT IN allMyStuff.items.pets.canines
)
FROM b4tPools AS allMyStuff USE KEYS “myStuff”

Both of these queries return the full array of petDepartment.animals.mammals.canines

2

Try this:

select a
from myStore  USE KEYS "myStore" unnest myStore.petDepartment.animals.mammals.canines a
except
select a
from myStuff USE KEYS "myStuff" unnest myStuff.items.pets.canines a
3

Thanks for the help Keshav. This gave me a result of:

[
{
“a”: “golden retriever”
},
{
“a”: “labrador retriever”
},
{
“a”: “greyhound”
},
{
“a”: “poodle”
},
{
“a”: “golden doodle”
}
]

What I was really looking for was the result as an array as follows:

[
“golden retriever”,
“labrador retriever”,
“greyhound”,
“poodle”,
“golden doodle”
]

4 
SELECT RAW a.a FROM ( select a
from myStore  USE KEYS "myStore" unnest myStore.petDepartment.animals.mammals.canines a
except
select a
from myStuff USE KEYS "myStuff" unnest myStuff.items.pets.canines a
) AS a;

OR

select ARRAY v FOR v IN myStore.petDepartment.animals.mammals.canines WHEN v NOT IN mystuff END  from myStore  USE KEYS "myStore"
LET mystuff = (select RAW items.pets.canines from myStuff USE KEYS "myStuff")[0]
5

Thanks vsr1.

That still doesn’t give me the array I was looking for. The first query still returns:

[
{
“a”: “golden retriever”
},
{
“a”: “labrador retriever”
},
{
“a”: “greyhound”
},
{
“a”: “poodle”
},
{
“a”: “golden doodle”
}
]

Wile the second one gives me no results:
[
{}
]

6

First query needs RAW a.a, second query works

INSERT INTO default VALUES("myStore", { "petDepartment": { "animals": { "mammals": { "canines": [ "husky", "golden retriever", "daschund", "labrador retriever", "greyhound", "poodle", "golden doodle" ] } } } });
INSERT INTO default VALUES("myStuff", { "owner": "My Name", "items": { "pets": { "canines": [ "husky", "daschund" ] } } });

SELECT RAW a.a FROM ( SELECT a FROM default AS myStore  USE KEYS "myStore" UNNEST myStore.petDepartment.animals.mammals.canines a
                    EXCEPT
                    SELECT a FROM default AS myStuff USE KEYS "myStuff" UNNEST myStuff.items.pets.canines a
) AS a;

SELECT ARRAY v FOR v IN myStore.petDepartment.animals.mammals.canines WHEN v NOT IN mystuff END  FROM default AS myStore  USE KEYS "myStore"
LET mystuff = (SELECT RAW items.pets.canines FROM default AS myStuff USE KEYS "myStuff")[0];
7

That works :ok_hand:

Thanks

1 Like