4. Generate a list of publication categories 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 categories associated with the publication, separated by semicolons.

It's very similar to the publication authors query. You may also be interested in the tutorial about working with nested fields.

Query

WITH categories AS (
  SELECT
    id,
    ARRAY(
        SELECT name
        FROM UNNEST(category_for.first_level.FULL)
    ) AS category_names
  FROM
    `dimensions-ai.data_analytics.publications`
  WHERE
    id = 'pub.1132070778'
)

SELECT
  id,
  ARRAY_TO_STRING(category_names, '; ') AS categories_list
FROM categories

Results

[
  {
    "id": "pub.1132070778",
    "categories_list": "Physical Sciences; Chemical Sciences"
  }
]