I have a Table A , where one of the column is a Repeated RECORD like
+- children: record (repeated)
| |- name: string
| |- gender: string
| |- age: integer
I have a Table B , where one of the column is a STRING (Repeated)
+- names : string (repeated)
Looking for options to move name list from Table A which is inside RECORD to a String Array of Table B .
Any suggestions will be of great help
You can use the ARRAY function. Try this:
#standardSQL
SELECT
ARRAY_TO_STRING(
ARRAY(SELECT name FROM UNNEST(children))
) AS names
FROM `dataset.table`
It creates new arrays from just the name field within the structs, then converts the arrays to strings.
Below is for BigQuery Standard SQL
If you expect to get array you can use below
#standardSQL
SELECT ARRAY(SELECT name FROM UNNEST(children)) AS names
FROM `yourproject.yourdataset.yourtable`
you can test / play with it using dummy data as
#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
SELECT [STRUCT<name STRING, gender STRING, age INT64>('abc1','m',12),('xyz1','m',13),('uvw1','f',14)] children UNION ALL
SELECT [STRUCT<name STRING, gender STRING, age INT64>('abc2','f',12),('xyz2','m',13),('uvw2','f',14)]
)
SELECT ARRAY(SELECT name FROM UNNEST(children)) AS names
FROM `yourproject.yourdataset.yourtable`
output is
Row names
1 abc1
xyz1
uvw1
2 abc2
xyz2
uvw2
In case if you would expected string
#standardSQL
SELECT (SELECT STRING_AGG(name) FROM UNNEST(children)) AS names
FROM `yourproject.yourdataset.yourtable`
You can test / play with it using same dummy data
#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
SELECT [STRUCT<name STRING, gender STRING, age INT64>('abc1','m',12),('xyz1','m',13),('uvw1','f',14)] children UNION ALL
SELECT [STRUCT<name STRING, gender STRING, age INT64>('abc2','f',12),('xyz2','m',13),('uvw2','f',14)]
)
SELECT (SELECT STRING_AGG(name) FROM UNNEST(children)) AS names
FROM `yourproject.yourdataset.yourtable`
and output now is
Row names
1 abc1,xyz1,uvw1
2 abc2,xyz2,uvw2
