json - 使用 BigQuery 从 JSON 中提取动态参数
问题描述
我在每一行都有一个带有 JSON 的表，我正在尝试从下面的 JSON 中提取温度值。
JSON_EXTRACT由于日期，我无法使用，它是动态的并且在每一行中都不同。
JSON_EXTRACT(features_JSON, $.historical.2021-08-09.hourly[0].temperature)
如果我能够使用以下代码，它将解决问题
select
JSON_EXTRACT(features_JSON,concat('$.historical.',left(cast(local_time as string),10), '.temperature' )) from table
但我收到以下错误
JSONPath must be a string literal or query parameter
JSON
{"request":{"type":"LatLon","query":"Lat 37.13 and Lon -93.71","language":"en","unit":"m"},"location":{"name":"Chesapeake","country":"United States of America","region":"Missouri","lat":"37.117","lon":"-93.681","timezone_id":"America/Chicago","localtime":"2021-09-05 05:53","localtime_epoch":1630821180,"utc_offset":"-5.0"},"current":{"observation_time":"10:53 AM","temperature":19,"weather_code":122,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png"],"weather_descriptions":["Overcast"],"wind_speed":9,"wind_degree":30,"wind_dir":"NNE","pressure":1015,"precip":0,"humidity":94,"cloudcover":100,"feelslike":19,"uv_index":1,"visibility":16,"is_day":"no"},"historical":{"2021-08-09":{"date":"2021-08-09","date_epoch":1628467200,"astro":{"sunrise":"06:26 AM","sunset":"08:13 PM","moonrise":"07:21 AM","moonset":"09:22 PM","moon_phase":"Waxing Crescent","moon_illumination":3},"mintemp":23,"maxtemp":33,"avgtemp":29,"totalsnow":0,"sunhour":11.6,"uv_index":6,"hourly":[{"time":"0","temperature":25,"wind_speed":25,"wind_degree":187,"wind_dir":"S","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.4,"humidity":71,"visibility":9,"pressure":1012,"cloudcover":83,"heatindex":26,"dewpoint":19,"windchill":25,"windgust":51,"feelslike":26,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"100","temperature":24,"wind_speed":24,"wind_degree":190,"wind_dir":"S","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.2,"humidity":74,"visibility":9,"pressure":1012,"cloudcover":82,"heatindex":26,"dewpoint":19,"windchill":24,"windgust":50,"feelslike":26,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"200","temperature":23,"wind_speed":23,"wind_degree":193,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.2,"humidity":76,"visibility":10,"pressure":1012,"cloudcover":81,"heatindex":25,"dewpoint":19,"windchill":23,"windgust":49,"feelslike":25,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"300","temperature":23,"wind_speed":23,"wind_degree":196,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.2,"humidity":78,"visibility":10,"pressure":1012,"cloudcover":80,"heatindex":25,"dewpoint":19,"windchill":23,"windgust":48,"feelslike":25,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"400","temperature":23,"wind_speed":23,"wind_degree":199,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.1,"humidity":78,"visibility":10,"pressure":1012,"cloudcover":80,"heatindex":25,"dewpoint":19,"windchill":23,"windgust":48,"feelslike":25,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"500","temperature":23,"wind_speed":24,"wind_degree":203,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0025_light_rain_showers_night.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.1,"humidity":77,"visibility":10,"pressure":1012,"cloudcover":80,"heatindex":25,"dewpoint":19,"windchill":23,"windgust":48,"feelslike":25,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"600","temperature":23,"wind_speed":24,"wind_degree":206,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.2,"humidity":76,"visibility":10,"pressure":1012,"cloudcover":80,"heatindex":25,"dewpoint":19,"windchill":23,"windgust":48,"feelslike":25,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":5},{"time":"700","temperature":24,"wind_speed":26,"wind_degree":207,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png"],"weather_descriptions":["Patchy rain possible"],"precip":0.1,"humidity":74,"visibility":10,"pressure":1013,"cloudcover":63,"heatindex":26,"dewpoint":19,"windchill":24,"windgust":46,"feelslike":26,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":5},{"time":"800","temperature":25,"wind_speed":27,"wind_degree":209,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png"],"weather_descriptions":["Patchy rain possible"],"precip":0,"humidity":72,"visibility":10,"pressure":1013,"cloudcover":47,"heatindex":28,"dewpoint":20,"windchill":25,"windgust":43,"feelslike":28,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":6},{"time":"900","temperature":26,"wind_speed":28,"wind_degree":210,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png"],"weather_descriptions":["Patchy rain possible"],"precip":0,"humidity":70,"visibility":10,"pressure":1013,"cloudcover":30,"heatindex":29,"dewpoint":21,"windchill":26,"windgust":40,"feelslike":29,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":6},{"time":"1000","temperature":28,"wind_speed":28,"wind_degree":211,"wind_dir":"SSW","weather_code":176,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0009_light_rain_showers.png"],"weather_descriptions":["Patchy rain possible"],"precip":0,"humidity":67,"visibility":10,"pressure":1013,"cloudcover":22,"heatindex":30,"dewpoint":21,"windchill":28,"windgust":39,"feelslike":30,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":6},{"time":"1100","temperature":29,"wind_speed":29,"wind_degree":212,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":63,"visibility":10,"pressure":1013,"cloudcover":14,"heatindex":32,"dewpoint":21,"windchill":29,"windgust":37,"feelslike":32,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":7},{"time":"1200","temperature":31,"wind_speed":30,"wind_degree":213,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":59,"visibility":10,"pressure":1013,"cloudcover":6,"heatindex":34,"dewpoint":22,"windchill":31,"windgust":35,"feelslike":34,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":8},{"time":"1300","temperature":31,"wind_speed":29,"wind_degree":213,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":56,"visibility":10,"pressure":1013,"cloudcover":10,"heatindex":35,"dewpoint":22,"windchill":31,"windgust":34,"feelslike":35,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":8},{"time":"1400","temperature":32,"wind_speed":28,"wind_degree":213,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":54,"visibility":10,"pressure":1013,"cloudcover":14,"heatindex":36,"dewpoint":22,"windchill":32,"windgust":33,"feelslike":36,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":8},{"time":"1500","temperature":33,"wind_speed":27,"wind_degree":213,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":51,"visibility":10,"pressure":1013,"cloudcover":18,"heatindex":37,"dewpoint":22,"windchill":33,"windgust":32,"feelslike":37,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":8},{"time":"1600","temperature":33,"wind_speed":25,"wind_degree":211,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":53,"visibility":10,"pressure":1012,"cloudcover":18,"heatindex":37,"dewpoint":22,"windchill":33,"windgust":33,"feelslike":37,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":8},{"time":"1700","temperature":32,"wind_speed":23,"wind_degree":209,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":55,"visibility":10,"pressure":1012,"cloudcover":17,"heatindex":36,"dewpoint":22,"windchill":32,"windgust":35,"feelslike":36,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":8},{"time":"1800","temperature":32,"wind_speed":21,"wind_degree":208,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":57,"visibility":10,"pressure":1012,"cloudcover":17,"heatindex":36,"dewpoint":22,"windchill":32,"windgust":36,"feelslike":36,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"1900","temperature":31,"wind_speed":21,"wind_degree":205,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":56,"visibility":10,"pressure":1012,"cloudcover":15,"heatindex":34,"dewpoint":21,"windchill":31,"windgust":38,"feelslike":34,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"2000","temperature":30,"wind_speed":21,"wind_degree":201,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":56,"visibility":10,"pressure":1013,"cloudcover":14,"heatindex":33,"dewpoint":20,"windchill":30,"windgust":40,"feelslike":33,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"2100","temperature":29,"wind_speed":22,"wind_degree":198,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":55,"visibility":10,"pressure":1013,"cloudcover":12,"heatindex":31,"dewpoint":19,"windchill":29,"windgust":42,"feelslike":31,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"2200","temperature":28,"wind_speed":21,"wind_degree":197,"wind_dir":"SSW","weather_code":116,"weather_icons":["https://assets.weatherstack.com/images/wsymbols01_png_64/wsymbol_0004_black_low_cloud.png"],"weather_descriptions":["Partly cloudy"],"precip":0,"humidity":60,"visibility":10,"pressure":1013,"cloudcover":19,"heatindex":30,"dewpoint":19,"windchill":28,"windgust":41,"feelslike":30,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1},{"time":"2300","temperature":27,"wind_speed":20,"wind_degree":197,"wind_dir":"SSW","weather_code":299,"weather_icons":["https://assets.png"],"weather_descriptions":["Moderate rain at times"],"precip":0.1,"humidity":65,"visibility":10,"pressure":1013,"cloudcover":26,"heatindex":29,"dewpoint":20,"windchill":27,"windgust":40,"feelslike":29,"chanceofrain":0,"chanceofremdry":0,"chanceofwindy":0,"chanceofovercast":0,"chanceofsunshine":0,"chanceoffrost":0,"chanceofhightemp":0,"chanceoffog":0,"chanceofsnow":0,"chanceofthunder":0,"uv_index":1}]}}}
我能做些什么？
解决方案
推荐阅读
- python-3.x - 连接熊猫数据框中的列表并在另一列中获取唯一标记
- azure - 并行运行时打包器“azure-arm”失败
- tensorflow - 将此代码从 tensorflow 1 移植到 tensorflow 2
- amazon-web-services - 凭据和访问密钥之间的区别
- excel - 如何使用 VBA 连接多个列
- datetime - 2018-12-21 10:57 GMT+0 的日期时间格式
- python - 如何不接受大写字母作为输入？同时也让这个人重新输入他们的用户名，直到它全部小写
- google-sheets - 如果时间戳在 24 小时之内，则突出显示重复的名称
- javascript - react js元素类型无效：需要一个字符串（用于内置组件）或一个类/函数（用于复合组件）但得到：未定义
- javascript - “传递给 useEffect 的最后一个参数改变了渲染之间的大小”，但我认为它不会？