How to concat two columns into new column in BQ Table

Linda December 19, 2021 Tagged google-bigquery, sql

I have tried to concat two column records into single column and landed into issue while doing that. Please help me to understand better what am I missing.

Table:

Row    add.line1          add.line2
1        name1             street1
2        name2             street2

code:

select concat('[{'line1':'add.line1','line2':'add.line2'}]') as completeaddr from `<project_id.dataset.table>`,
UNNEST(address) as add

Expected Output:

Row    add.line1          add.line2                completeaddr
1        name1             street1       [{'line1': 'name1','line2': 'street1'}]
2        name2             street2       [{'line1': 'name2','line2': 'street2'}]