BigQuery：使用旧版SQL过滤重复的字段 [英] BigQuery : filter repeated fields with legacy SQL

问题描述

我有以下表格：

I have the following table :

row | query_params | query_values
1     foo            bar  
      param          val
2     foo            baz 

JSON：

JSON :

{ 
"query_params" : [ "foo", "param"], 
"query_values" : [ "bar", "val" ] 
}, { 
"query_params" : [ "foo" ], 
"query_values" : [ "baz" ] 
}

使用传统SQL，我想对它们的值过滤重复字段，例如

Using legacy SQL I want to filter repeated field on their value, something like

SELECT * FROM table WHERE query_params = 'foo'

哪一个会输出

Which would output

row | query_params | query_values
1     foo            bar  
2     foo            baz       

PS：这个问题是相关的到相同的问题，但使用标准的SQL答案这里

PS : this question is related to the same question but using standard SQL answered here

推荐答案

试试看下面的版本

Try below version

    SELECT [row], query_params, query_values 
    FROM (
        SELECT [row], query_params, param_pos, query_values, POSITION(query_values) AS value_pos 
        FROM FLATTEN((
            SELECT [row], query_params, POSITION(query_params) AS param_pos, query_values 
            FROM YourTable
        ), query_params)
        WHERE query_params = 'foo'
    )
    WHERE param_pos = value_pos

这篇关于BigQuery：使用旧版SQL过滤重复的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！