How to save the query result in the current table without changing the table schema?

Question

I have a structure

  {
    id: "123",
    scans:[{
       "scanid":"123",
       "status":"sleep"
      }]
  },
  {
    id: "123",
    scans:[{
       "scanid":"123",
       "status":"sleep"
      }]
  }

Request to remove duplicate:

      SELECT *
    FROM (
      SELECT
          *,
          ROW_NUMBER()
              OVER (PARTITION BY id)
              row_number,
      FROM table1
    )
    WHERE row_number = 1

I have specified the destination table as table1.

Here I did the scan as duplicate entries, scanid as a string and status as a string. But when I make a request (I make a request to remove the duplicate) and overwrite the existing table, the table schema changes. He becomes scans_scanid(string)and scans_status(string). Now the scan pattern will be changed. Please suggest where am I mistaken?

Answer 1

, NEST() UnFlatten .

, INTEGER id scanid. STRING, .

. parseInt() t = {scanid:parseInt(x[0]), status:x[1]}

SELECT id, scans.scanid, scans.status 
FROM JS(
  (      // input table
    SELECT id, NEST(CONCAT(STRING(scanid), ',', STRING(status))) AS scans
    FROM (
      SELECT id, scans.scanid, scans.status 
      FROM (
        SELECT id, scans.scanid, scans.status, 
               ROW_NUMBER() OVER (PARTITION BY id) AS dup
        FROM table1
      ) WHERE dup = 1  
    ) GROUP BY id
  ),
  id, scans,     // input columns
  "[{'name': 'id', 'type': 'INTEGER'},    // output schema
    {'name': 'scans', 'type': 'RECORD',
     'mode': 'REPEATED',
     'fields': [
       {'name': 'scanid', 'type': 'INTEGER'},
       {'name': 'status', 'type': 'STRING'}
     ]    
    }
  ]",
  "function(row, emit){    // function 
    var c = [];
    for (var i = 0; i < row.scans.length; i++) {
      x = row.scans[i].toString().split(',');
      t = {scanid:parseInt(x[0]), status:x[1]}
      c.push(t);
    };
    emit({id: row.id, scans: c});  
  }"
)

BigQuery. , Limits Limitations, . - ,

. , JOIN CROSS JOIN .

Answer 2

1) -, , , .

, -, .

2) -, , , , , :

select 'foo' as scans.scanid

, ​​ , , , .

3) NEST(expr) ,

. , "SELECT x, NEST (y) FROM... GROUP BY x" x y x . NEST GROUP BY.

BigQuery , , NEST , . NEST , .