Geek Answers Handbook

Do you combine the way to migrate to BigQuery when retrieving data from multiple repeating fields?

Consider this scheme:

key: REQUIRED INTEGER
description: NULLABLE STRING
field: REPEATED RECORD {
    field.names: REQUIRED STRING
    field.value: NULLABLE FLOAT
}

Where: keyunique in the table, field.namesin fact, it is a list of properties separated by commas ("property1", "property2", "property3" ...).

An example of a data set (do not pay attention to the actual values, they are intended only to demonstrate the structure):

{"key":1,"description":"Cool","field":[{"names":"\"Nice\",\"Wonderful\",\"Woohoo\"", "value":1.2},{"names":"\"Everything\",\"is\",\"Awesome\"", "value":20}]}
{"key":2,"description":"Stack","field":[{"names":"\"Overflow\",\"Exchange\",\"Nice\"", "value":2.0}]}
{"key":3,"description":"Iron","field":[{"names":"\"The\",\"Trooper\"", "value":666},{"names":"\"Aces\",\"High\",\"Awesome\"", "value":333}]}

I need a way to instantly request multiple values field.names. The result should look like this:

+-----+--------+-------+-------+-------+-------+
| key |  desc  | prop1 | prop2 | prop3 | prop4 |
+-----+--------+-------+-------+-------+-------+
| 1   | Desc 1 | 1.0   | 2.0   | 3.0   | 4.0   |
| 2   | Desc 2 | 4.0   | 3.0   | 2.0   | 1.0   |
| ... |        |       |       |       |       |
+-----+--------+-------+-------+-------+-------+

If the same key contains fields with the same requested name, only the first value should be considered.

And here is my request:

select all.key as key, all.description as desc, 
t1.col as prop1, t2.col as prop2, t3.col as prop3 //and so on...

from mydataset.mytable all

left join each 
(select key, field.value as col from 
mydataset.mytable
where lower(field.names) contains '"trooper"'
group each by key, col
) as t1 on all.key = t1.key

left join each 
(select key, field.value as col from 
mydataset.mytable
where lower(field.names) contains '"awesome"'
group each by key, col
) as t2 on all.key = t2.key

left join each 
(select key, field.value as col from 
mydataset.mytable
where lower(field.names) contains '"nice"'
group each by key, col
) as t3 on all.key = t3.key

//and so on...

The result of this query will be:

+-----+-------+-------+-------+-------+
| key | desc  | prop1 | prop2 | prop3 |
+-----+-------+-------+-------+-------+
|   1 | Cool  | null  | 20.0  | 1.2   |
|   2 | Stack | null  | null  | 2.0   |
|   3 | Iron  | 666.0 | 333.0 | null  |
+-----+-------+-------+-------+-------+

, : ? , , 200 , 200 ? , , ? , BigQuery?

.

+4
1

50 , . , .

, , . , , .

, , :

select
  key,
  desc,
  max(if(lower(field.names) contains "trooper", field.value, null))
      within record as prop1,
  max(if(lower(field.names) contains "awesome", field.value, null))
      within record as prop2,
  ...
from mydataset.mytable

"prop" , , null, , "max". , , , . , , - , .

" " BigQuery , , "group by" .

+7

More articles:


All Articles