Concatenating lists from several documents

SQL++
1

We want to concatenate lists from different documents with the following structure:
(the bucket name is ‘data’)

{
   "id": "u12",
   "data": [{...},{...}]
}

We have several of these documents, and we want to gather all the “data” lists into one big list.
What is the correct SELECT statement look like?

2

try this:

SELECT a.k,ARRAY_AGG(a.item_d) AS `data_list`
  FROM (
SELECT item_d,1 AS k  FROM `data` d UNNEST d.`data` item_d  use keys["u12"]
  ) AS a
  GROUP BY a.k

where you can rewrite your condition of use keys["u12"].

3

Care to elaborate? I can’t make it work.

What is d? Is it an alias? What is item_d? What does the 1 after comma in the second select mean?

Thanks for answering!

4

yes, d , k and item_d are alias,can you post some sample docs,I can write a N1QL for you.

5

Bucket: ‘data’

{
   "id1": "u12",
   "id2": "v4",
   "data": [{"value1":"ret", "value2": "etr"}, {"value1":"tre", "value2": "atr"}, {"value1":"relt", "value2": "btr"}]
}

Let’s say there’s 3 of those documents in the ‘data’ bucket.

Desired output:

{
   "id1": "u12",
   "id2": "v4",
   "data": [{"value1":"ret", "value2": "etr"}, {"value1":"tre", "value2": "atr"}, {"value1":"relt", "value2": "btr"},
            {"value1":"rt", "value2": "etr"}, {"value1":"tre", "value2": "atr"}, {"value1":"relt", "value2": "btr"},
            {"value1":"ret", "value2": "etr"}, {"value1":"tree", "value2": "atr"}, {"value1":"relt", "value2": "btr"}]
}

Thanks again :stuck_out_tongue:

6

let’s say the id of doc1/doc1/doc3 is doc1,doc2,doc3

SELECT a.k,ARRAY_AGG(a.item_d) AS `data_list`
  FROM (
      SELECT item_d,1 AS k  FROM `data` d UNNEST d.`data` item_d  USE KEYS["doc1","doc2","doc3"]
  ) AS a
  GROUP BY a.k

you can rewrite the condition to filter the data that you want to gather to.

7

Sorry for being a complete noob here and not specifying my problem properly. All the documents will have the same ‘id1’ and ‘id2’ and this is what I want to match. Can I replace USE KEYS with WHERE id1=“u12” AND id2=“v4”, since I don’t know the doc_id’s? I’m trying to run the query now but it seems to take a long time.

8 
SELECT a.k,ARRAY_AGG(a.item_d) AS `data_list`
  FROM (
      SELECT item_d,1 AS k  FROM `data` d UNNEST d.`data` item_d  WHERE d.id1 == "u12" AND d.id2 == "v4"
  ) AS a
  GROUP BY a.k
9 
  SELECT d.id1, d.id2, ARRAY_FLATTEN(ARRAY_AGG(d.data),2) AS data FROM `data` d GROUP BY d.id1, d.id2;

OR
 
       SELECT a.id1, a.id2, ARRAY_AGG(a.item_d) AS data
          FROM ( SELECT item_d, d.id1, d.id2  FROM `data` d UNNEST d.`data` item_d ) AS a
          GROUP BY a.id1, a.id2;
10

Thanks for the help, guys :slight_smile:

11

@vsr1 's suggestion is better.