Concat strings from array returned by SELECT subquery with UNNEST and JOIN

SQL++
1

Hi,

I have two buckets: books and authors.

Each document in authors has a numeric ‘id’ field as a unique identifier, e.g. “id”: 22345.
The documents from ‘books’ have an array field ‘authors’ storing IDs for each author who (co-)wrote the book, e.g. “authors”: [ 22345, 22457, … ].

I would like to display the book title together with a string containing the full names of the book’s authors on a web page.

The result of a query should look like this:

[
  {
    “title”: “An introduction to N1QL”
    “author_names”: “Alvin Awesome, Billy Bombastic, …”
  },
  …
  {…}
]

How would an appropriate SELECT look like?

SELECT b.title, a.first_name || " " || a.last_name AS author_names
  FROM books AS b
    JOIN authors AS a
      ON a.id = …

Can this be accomplished using a loop?

Thanks in advance.

2 
SELECT d.title,
REPLACE(REPLACE(REPLACE(encode_json(d.names), '\",\"',','), '[\"',''), '\"]','') AS author_names
FROM (SELECT b.title, ARRAY_AGG(a.first_name || " " || a.last_name) AS names
       FROM books AS b 
       UNNEST b.authors AS baid
       JOIN authors AS a ON a.id = baid
       WHERE .....
       GROUP BY b.title ) AS d;


SELECT b.title,
       REPLACE(REPLACE(REPLACE(encode_json((SELECT RAW (a.first_name || " " || a.last_name)
                                            FROM b.authors AS baid JOIN authors AS a ON a.id = baid)),
                       '\",\"',','), '[\"',''), '\"]','') AS author_names
FROM books AS b
WHERE b.title IS NOT NULL;

In 6.50 Beta release you can try the following

SELECT d.title,
       CONCAT2(",", d.names) AS author_names
FROM (SELECT b.title, ARRAY_AGG(a.first_name || " " || a.last_name) AS names
       FROM books AS b
      UNNEST b.authors AS baid
       JOIN authors AS a ON a.id = baid
       WHERE .....
       GROUP BY b.title ) AS d;

SELECT b.title,
       CONCAT2( ",",(SELECT RAW (a.first_name || " " || a.last_name)
                                            FROM b.authors AS baid JOIN authors AS a ON a.id = baid)) AS author_names
FROM books AS b
WHERE b.title IS NOT NULL;
3

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:


SELECT sub.title,
       REPLACE(
         REPLACE(
           REPLACE(
             encode_json( sub.names), '\",\"', ', '
           ), '[\"', ''
         ), '\"]', ''
       ) AS author_names
  FROM (
    SELECT b.title,
           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.:+1:

Thanks again!

4

Ignore 2nd SELECT. correlated subqueries requires USE KEYS to limit result set. I thought one of sources is expression it may not required USE KEYS, it looks like still need USE KEYS.

https://blog.couchbase.com/ansi-join-support-n1ql/ Example 13

ARRAY_AGG() grows dynamically. If you need dedup names use ARRAY_AGG( DISTINCT expr) .

1 Like