Concatenate the values of a nested field on BigQuery

Gilberto Torrezan

Supose I have the following schema:

[
   {
        'name': 'id',
        'type': 'INTEGER' 
   }
   {
        'name': 'record',
        'type': 'RECORD',
        'fields': [
            {
                'name': 'repeated',
                'type': 'STRING',
                'mode': 'REPEATED'
            }
         ]
   }
]

And the following data:

+--------------------+
|id  |record.repeated|
+--------------------+
|1   |'a'            |
|    |'b'            |
|    |'c'            |
+--------------------+
|2   |'a'            |
|    |'c'            |
+--------------------+
|3   |'d'            |
+--------------------+

What I need is to create a query that returns this:

+--------------------+
|id  |record.repeated|
+--------------------+
|1   |'a,b,c'        |
+--------------------+
|2   |'a,c'          |
+--------------------+
|3   |'d'            |
+--------------------+

In other words, I need to query that allows me to concatenate the values of a nested field using a separator (in this case, comma). Something like the GROUP_CONCAT function of MySQL, but on BigQuery.

Related idea: Concat all column values in sql

Is that possible?

Thanks.

Pentium10

It's very simple

select group_concat(record.repeated) from table

an example from publicdata is

SELECT group_concat(payload.shas.encoded)
FROM [publicdata:samples.github_nested]
WHERE repository.url='https://github.com/dreamerslab/workspace'

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to concatenate field values with recursive query in postgresql?

Concatenate string values in array in a single field in MongoDB

Update nested field in BigQuery table

How to concatenate model field values in pre_save Django

Get the first row of a nested field in BigQuery

How to get the length of a repeated nested field in bigquery

BigQuery: how to count specific values in a REPEATED field?

SSRS: Concatenate field's distinct values

Concat nested field values if they exist

Replace nested field values in an object

BigQuery check if a nested field exist

how to concatenate tow Values as strings into an odoo Char field?

BigQuery: Concatenate two arrays and keep distinct values within MERGE statement

Flatten BigQuery nested field contents into new columns instead of rows

Update a nested field in BigQuery using another nested field as a condition

BigQuery UPDATE nested array field

Elasticsearch sort values in nested field

sql bigquery: extract string after ":" from nested field

BigQuery: filter according to counts in nested field

bigquery pivoting with nested field

Mule 4 : DW transformation : How to concatenate the values of a nested node in XML?

Concatenate string field using dplyr when some values are NA

How to fill values by referencing other field values in BigQuery SQL?

Concatenate values of dictionary of list with nested dictionaries

How to group-filter field by values in a different field in BigQuery

Failing to concatenate same key values within nested dictionary

Concatenate nested same key values in sub objects in python dictionary

Join two tables based on a nested field in bigquery

BigQuery extract nested JSON with dynamic keys and values

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive