Thank you for your reply.
As I can see you provided 4 possible solutions to what I asked for, 2 for v6.50-beta and 2 for pre-v6.50.
I’m still using Couchbase 6.0.x, which is why I tried the upper part of your answer.
There seems to be an error inherent in the 2nd SELECT:
Error evaluating projection. - cause:
FROM in correlated subquery must have USE KEYS clause: FROM authors.
The 1st SELECT, however, works fine. Thank you again for sharing your knowledge!
I reformatted your answer and renamed some aliases, but that’s just for readability:
encode_json( sub.names), '\",\"', ', '
), '[\"', ''
), '\"]', ''
) AS author_names
ARRAY_AGG( a.first_name || " " || a.last_name) AS names
FROM books AS b
UNNEST b.authors AS author_id
JOIN authors AS a
ON a.id = author_id
GROUP BY b.title
) AS sub;
Now I realize I need 2 SELECTs to do what I want:
The subquery conceptually performs a JOIN of the author ID array ‘b.authors’ with its parent object. By introducing ‘author_id’ we have a variable that loops over all elements from this array, haven’t we? That way we can generate multiple matches using the clause ‘JOIN … ON a.id = author_id’, each of which is stored in the ‘names’ array.
Since ARRAY_AGG( expr) requires a group of elements as ‘expr’ to build an array from, I wonder if in this example the array grows dynamically by adding name parts of matching authors, or if there is a list of author matches constructed beforehand.
Anyway, we can omit the WHERE clause in the subquery, but yes, we need a GROUP BY to isolate book titles, as we would otherwise have to aggregate them.
The outer query selects book titles as well, but uses nested REPLACE statements to piecewise transform the ‘names’ array into a string of comma separated author names. The final result is a string since encode_json() puts double quotes around ‘sub.names’.
Pretty cool to see that version 6.50-beta makes a SELECT more comfortable in my case. CONCAT2() seems to eliminate the need to use a nested REPLACE to make one string from many array elements, and even includes a delimiter option.
Great to see the progress of the language.