How to aggregate an array with ARRAY_AGG and get performance

SQL++
, , ,
15

Use request profiling Look page 341 in https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf

The query is complex and have limited options. If 6.0.0 can think few more improvements.

Try the following if helps anything further. If you are not interested _ID, _CAS values from same document, you can use MAX or MIN instead of ARRAY_AGG() which is costly to form array if lot of rows.

CREATE INDEX ix2 ON `test-data` (origin,
          DISTINCT ARRAY [p.name, ARRAY_COUNT(p.val) > 0, metadata.configurations.[p.name].enabled,
                          ARRAY_CONTAINS(IFMISSINGORNULL(deletedFor, []) ,p.name)]
                   FOR p IN OBJECT_PAIRS(productIds) END,
          metadata.configurations.`GENERAL`.enabled, childIds
         ) WHERE _class = "com.test.category.Category";

SELECT ARRAY_AGG(META(c).id)[0] AS _ID,
       ARRAY_AGG(META(c).cas)[0] AS _CAS,
       ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY_AGG(c.productIds.`US`), 1)) AS productIds
FROM ( SELECT RAW c.id
       FROM ( SELECT META(c).id, c.childIds
              FROM  `test-data` AS c USE INDEX (ix2)
              WHERE c._class = "com.test.category.Category"
                    AND c.origin = "GENERAL" AND c.metadata.configurations.`GENERAL`.enabled = true
                   AND ANY p IN OBJECT_PAIRS(c.productIds)
                       SATISFIES [p.name, ARRAY_COUNT(p.val) > 0, c.metadata.configurations.[p.name].enabled,
                          ARRAY_CONTAINS(IFMISSINGORNULL(c.deletedFor, []) ,p.name)] = ["US", true, false, false] END
              UNION
              SELECT META(c).id, c.childIds
              FROM  `test-data` AS c USE INDEX (ix2)
              WHERE c._class = "com.test.category.Category"
                   AND c.origin = "US"
                   AND ANY p IN OBJECT_PAIRS(c.productIds)
                       SATISFIES [p.name, ARRAY_COUNT(p.val) > 0, c.metadata.configurations.[p.name].enabled,
                          ARRAY_CONTAINS(IFMISSINGORNULL(c.deletedFor, []) ,p.name)] = ["US", true, false, false] END) AS c
       LET visibleChildren = ( SELECT RAW COUNT(1)
                               FROM `test-data` AS cc USE KEYS c.childIds
                               WHERE cc.origin = "US" OR (cc.origin = "GENERAL"
                                     AND NOT ARRAY_CONTAINS(cc.deletedFor, "US"))
                            )[0]
       WHERE visibleChildren == 0 ) AS ckeys
JOIN `test-data` AS c ON KEYS ckeys;
1 Like