Access struct field in BigQuery-CodePudding

I have the following table:

field1 (STRING), field2(STRING), field3(RECORD)

field3 contains three fields: fieldA(STRING) fieldB(STRING) fieldC(STRING)

I can access fieldB by:

select field3.fieldB from 'dataset.table'

An example table can be

field1 (STRING), field2(STRING), field3.fieldB
"AAA"            "BBB"           {"key1":"text1","key2":"text2","keyN":"textN"}
"CCC"            "DDD"           {"key1":"text1","key2":"text2","keyN":"textN"}

Now I would get this result:

field1 (STRING), field2(STRING), result
"AAA"            "BBB"           ["text2,textN"]
"CCC"            "DDD"           ["text2,textN"]

I am using SPLIT function to get an array and access it by index, but sometimes I get error because element at index 1 is located at index0 and viceversa. So using where clause to compare strings. How can I get this output?

CodePudding user response：

Try below

select field1, field2, 
  [json_value(field3.fieldB, '$.key2'),
  json_value(field3.fieldB, '$.keyN')
  ] as result
from your_table

if applied to sample data in your question - output is

CodePudding user response：

If you want one of the values back as string, and using them as individual columns, I would use JSON_EXTRACT_SCALAR (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract_scalar).

Example:

select JSON_EXTRACT_SCALAR(fieldB, "$.key1") as key1
from 'dataset.table'