I have a game where a player has to chose a set of chips. To choose they are given collection of pairs of chips. They must chose one chip from each pair. For example the chip sets are:
a/b, c/d, e/f, g/h
INSERT INTO default (KEY, VALUE)
VALUES (“player1”, {“inPlay”: {“game1”: [{“chips”: [{“chipid”: “1”,“chip”: “a”},{“chipid”: “2”,“chip”: “c”},{“chipid”: “3”,“chip”: “e”},{“chipid”: “4”,“chip”: “g”}]}]}});
INSERT INTO default (KEY, VALUE)
VALUES (“player2”, {“inPlay”: {“game1”: [{“chips”: [{“chipid”: “1”,“chip”: “b”},{“chipid”: “2”,“chip”: “d”},{“chipid”: “3”,“chip”: “f”},{“chipid”: “4”,“chip”: “h”}]}]}});
INSERT INTO default (KEY, VALUE)
VALUES (“player3”, {“inPlay”: {“game1”: [{“chips”: [{“chipid”: “1”,“chip”: “b”},{“chipid”: “2”,“chip”: “c”},{“chipid”: “3”,“chip”: “e”},{“chipid”: “4”,“chip”: “h”}]}]}});
INSERT INTO default (KEY, VALUE)
VALUES (“player4”, {“inPlay”: {“game1”: [{“chips”: [{“chipid”: “1”,“chip”: “b”},{“chipid”: “2”,“chip”: “d”},{“chipid”: “3”,“chip”: “e”},{“chipid”: “4”,“chip”: “g”}]}]}});
INSERT INTO default (KEY, VALUE)
VALUES (“player5”, {“inPlay”: {“game1”: [{“chips”: [{“chipid”: “1”,“chip”: “a”},{“chipid”: “2”,“chip”: “c”},{“chipid”: “3”,“chip”: “e”},{“chipid”: “4”,“chip”: “g”}]}]}});
I want to be able to get the unique set of all the selections. To do that I am running the following query to correctly get the unique sets
SELECT DISTINCT RAW inPlay.game1[0].chips[*].chip
FROM default
WHERE inPlay.game1[0].chips[*].chip
RESULT: [[“a”,“c”,“e”,“g”],[“b”,“d”,“f”,“h”],[“b”,“c”,“e”,“g”],[“b”,“d”,“e”,“g”]]
As the game goes on I want to be able to eliminate some of the ‘sets’ from the query, for example, I want the unique set of chip collections ignoring a & b. By eleminating them I would expect a result of:
[[“c”,“e”,“g”],[“d”,“f”,“h”],[“d”,“e”,“g”]]
I tried a number of different queries, including something like:
SELECT DISTINCT RAW inPlay.game1[0].chips[*].chip
FROM default
WHERE inPlay.game1[0].chips[*].chip AND (inPlay.game1[0].chips[*].chip != 'a' OR inPlay.game1[0].chips[*].chip != 'b')
This query return the full set of: [[“a”,“c”,“e”,“g”],[“b”,“d”,“f”,“h”],[“b”,“c”,“e”,“g”],[“b”,“d”,“e”,“g”]], not the reduced set I was looking for.
I tried different versions with UNNEST in them.
Any suggestions?