3. Generate a list of publication authors by flattening/concatenating nested data

Level: Medium

This query requires basic knowledge of SQL and the Dimensions data model

Description

This query returns a table with a single row. One field contains the Dimensions publication ID, and the other contains a string of all author names associated with the paper, separated by semicolons.

For more details about working with nested fields, see the tutorial page on the topic. Example 3 in the tutorial deals with this query specifically.

Query

WITH author_array AS (
  SELECT
    id,
    ARRAY (
      SELECT CONCAT(first_name, " ", last_name)
      FROM UNNEST(authors)
    ) AS author_names
  FROM
    `dimensions-ai.data_analytics.publications`
  WHERE
    id = 'pub.1132070778'
)

SELECT
  id,
  ARRAY_TO_STRING(author_names, '; ') AS authors_list
FROM author_array

Results

[
  {
    "id": "pub.1132070778",
    "authors_list": "O Grånäs; A Mocellin; E S Cardoso; F Burmeister; C Caleman; O Björneholm; A Naves de Brito"
  }
]