Function not found: group_concat

Question

I'm interesting in execute a query using the "group_concat" BigQuery function. When I execute the query directly in the BigQuery interface, the query ends successfully, but when I try to execute that query via Node js, the bellow error appeared:

errors:
   [ { domain: 'global',
       reason: 'invalidQuery',
       message: 'Function not found: group_concat at [4:3]',
       locationType: 'other',
       location: 'query' } ]

The code is not the problem since it executes a simple query without any problem.

My query:

SELECT
  st_hub_session_id,
  num_requests,
  group_concat( group.code, '|' ) as Codes
FROM
  table.name
GROUP BY
  st_hub_session_id,
  group_concat
LIMIT
  1000

Where could be the problem?

Answer 1

BigQuery has two SQL modes, Standard SQL and Legacy SQL. You probably set your app to default Standard SQL, and by the interface runs LegacySQL.

try running the query using the pragma

#legacySQL
select group_concat(col) from (select '1' as col)

group_concat function is only available in Legacy SQL and it's not part of Standard SQL 2011.

Answer 2

Use STRING_AGG in standard SQL instead of the legacy GROUP_CONCAT. You can use standard SQL through the UI by unchecking "Use legacy SQL" under "Show Options" or else putting #standardSQL at the top of your query. See also Enabling Standard SQL.