N1ql join on value is in one of multiple arrays

SQL++
,
1

I have a large number of documents containing multiple arrays of objects such as

{
  "name": "42",
  "allies": [
    {
      "id": "1",
      "party": 1
    }
  ],
  "neutral": [
    {
      "id": "2",
      "party": 1
    }
  ],
  "enemy": [
    {
      "id": "unique",
      "party": 100
    }
  ]
}

{
  "name": "24",
  "allies": [
    {
      "id": "3",
      "party": 2
    }
  ],
  "neutral": [
    {
      "id": "4",
      "party": 2
    }
  ],
  "enemy": [
    {
      "id": "5",
      "party": 1
    }
  ]
}

and want to find any and all documents where that particular party appear. Hence output for these documents would be along the lines of

[
    {
        party: 1
        appear_in: [42, 42, 24]
    },
    {
        party: 2
        appear_in: [24, 24]
    }
]

Im trying to use the following query but have trouble getting it to work. As there might be a large number of results I want to use a join statement, but my trouble is how a join on value in one of multiple arrays work

SELECT biggerThan.party,
       ARRAY_AGG(META(d2).id) AS appear_in
FROM (
    SELECT data.party
    FROM `default` AS d
    UNNEST ARRAY_CONCAT(d.allies, d.neutral, d.enemy) AS data
    WHERE data.party IS VALUED
        AND META(d).id LIKE 'a:%'
    GROUP BY data.party
    HAVING COUNT(1) > 1 ) AS biggerThan
    JOIN `default` AS d2 ON biggerThan.party IN ARRAY_CONCAT(d2.allies, d2.neutral, d2.enemy).party
    AND biggerThan.party IS VALUED
    AND META(d2).id LIKE 'a:%'
GROUP BY biggerThan.party

Im having the (couchbase suggested) index

CREATE INDEX adv_ALL_array_concat_d_allies_d_neutral_d_enemy_party ON `default`(ALL ARRAY `data`.`party` FOR data IN array_concat((`allies`), (`neutral`), (`enemy`)) END)
2 
CREATE INDEX adv_ALL_array_concat_d_allies_d_neutral_d_enemy_party ON `default`(ALL ARRAY `data`.`party` FOR data IN array_concat((`allies`), (`neutral`), (`enemy`)) END) 
WHERE META().id LIKE "a:%";

SELECT data.party, ARRAY_AGG(META(d).id) appear_in
FROM `default` AS d
UNNEST ARRAY_CONCAT(d.allies, d.neutral, d.enemy) AS data
WHERE data.party IS VALUED AND META(d).id LIKE "a:%"
GROUP BY data.party
HAVING COUNT(1) > 1 ;

Above should work you no need JOIN

Issue is ON biggerThan.party IN ARRAY_CONCAT(d2.allies, d2.neutral, d2.enemy).party
should be ON biggerThan.party IN ARRAY_CONCAT(d2.allies, d2.neutral, d2.enemy)[*].party

4

Thanks

Without the join I get a problem with the query eating all my memory for a large dataset

So think I need to stream the result, which is why I try with a join

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.