BigQuery - extract array from JSON String

Question

I have a key with a value that is an array in a BQ table, see below

"foo": [{"name":"bar","type":"FLOAT","value":"16.0"},{"name":"baz","type":"STRING","value":"F"}]

and I am trying to extract the value of foo.

This is what I am trying:

JSON_EXTRACT_ARRAY('$.f.foo') AS fooBarBaz

and then

ARRAY_AGG(f.foo) AS fooBarBaz

but this is not even returning null

What am I doing wrong?

Mikhail Berlyant · Accepted Answer · 2021-03-09 19:18:14Z

1

Consider below "to extract the value of foo"

select json_extract_array('{' || col || '}', '$.foo') foo_items
from `project.dataset.table`

if applied to sample data in your question - output is

answered Mar 9, 2021 at 19:18

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1