How to concat an aggregate result into existing array

SQL++
1

Hello,
i would append a aggregate result from a subquery into a existing array.
But the aggregate is append like a sub class like this

image
image445×601 8.74 KB

I want concat into one array result like this

image
image445×601 8.54 KB

example:
SELECT myArray AS originalArray,
ARRAY_CONCAT (myArray, (SELECT ARRAY_AGG(id)
FROM myBucket2
WHERE …)) AS concatenedArray
FROM myBucket1
WHERE
…

SELECT
ARRAY_CONCAT(associationOffreKit.kitsIds, (
SELECT ARRAY_AGG(k.id)
FROM referential k
WHERE k.type=‘Kit’
AND ‘5b2b145d-c2cd-eb11-8235-000d3ab038ca’ IN k.offresIds))
FROM referential
WHERE type=‘Document’
AND ‘5b2b145d-c2cd-eb11-8235-000d3ab038ca’ IN associationOffreKit.offresIds

2 
SELECT myArray AS originalArray,
        ARRAY_CONCAT (myArray, (SELECT RAW id
                                FROM myBucket2
                                WHERE …)) AS concatenedArray
FROM myBucket1
WHERE ........;

Subquery itself ARRAY and you don’t have GROUP BY no need ARRAY_AGG() , also Use RAW remove OBJECT

CREATE INDEX ix1 ON referential(DISTINCT offresIds, id) WHERE type = "Kit"
CREATE INDEX ix2 ON referential(DISTINCT associationOffreKit.offresIds) WHERE type = "Document";

WITH koffresIds AS (SELECT RAW k.id
                    FROM referential k
                    WHERE k.type = "Kit"
                          AND ANY v IN k.offresIds SATISFIES v = "5b2b145d-c2cd-eb11-8235-000d3ab038ca" END)
SELECT ARRAY_CONCAT(associationOffreKit.kitsIds, koffresIds) AS ids
FROM referential
WHERE type = "Document"
       AND ANY v IN associationOffreKit.offresIds SATISFIES v = "5b2b145d-c2cd-eb11-8235-000d3ab038ca" END;
3

Thanks !
with RAW, the ARRAY-FLATTEN function merge my array and the subarray

SELECT associationOffreKit.kitsIds AS originalArray ,
       ARRAY_FLATTEN (ARRAY_CONCAT(associationOffreKit.kitsIds, (
               SELECT RAW ARRAY_AGG(k.id)
               FROM referential k
               WHERE k.type='Kit'
 			     AND k.isDeleted = false
                 AND '5b2b145d-c2cd-eb11-8235-000d3ab038ca' IN k.offresIds )), 2) as concatenedArray
FROM referential
WHERE type='Document'
    AND '5b2b145d-c2cd-eb11-8235-000d3ab038ca' IN associationOffreKit.offresIds
    and id='215169f9-1c67-4b3f-96dd-dfaa1ae85746'
4

Also try this

SELECT associationOffreKit.kitsIds AS originalArray ,
       ARRAY_CONCAT(associationOffreKit.kitsIds, (
               SELECT RAW k.id
               FROM referential k
               WHERE k.type='Kit'
                             AND k.isDeleted = false
                 AND '5b2b145d-c2cd-eb11-8235-000d3ab038ca' IN k.offresIds )) as concatenedArray
FROM referential
WHERE type='Document'
    AND '5b2b145d-c2cd-eb11-8235-000d3ab038ca' IN associationOffreKit.offresIds
    and id='215169f9-1c67-4b3f-96dd-dfaa1ae85746'
5

yes it works too.
it’s better.