英文:
Unnest Json Array json_extract_scalar
问题
我有一个表格 employee，其中有一个列 employee_detail，其中的数据如下：
{'emp': 'myself', 'dept': 'IT', 'empid': '12345', 'empdetail': {'JoiningDetail': {'JoinedGeneratedTimestamp': 1683295496534, 'position': 12115}, 'xyz': None, 'abc': None}, 'title': 'SrDevelop'}
我想在从 Athena 查询时获取 JoinedGeneratedTimestamp 和 position 数据。
以下是我尝试的查询：
with data as(
select employee_detail,
json_extract_scalar((replace(employee_detail, ''', '"')),
'$.empdetail.JoiningDetail.JoinedGeneratedTimestamp') as JoinedGeneratedTimestamp
from "employee" limit 5 )
select employee_detail, JoinedGeneratedTimestamp
from data
在 JoinedGeneratedTimestamp 字段中获取到空白。
英文:
I have a table employee , column employee_detail which have data like:
{'emp': 'myself', 'dept': 'IT', 'empid': '12345', 'empdetail': {'JoiningDetail': {'JoinedGeneratedTimestamp': 1683295496534, 'position': 12115}, 'xyz': None, 'abc': None}, 'title': 'SrDevelop'}
where I want to get JoinedGeneratedTimestamp and postition data while querying from athena
Below query I tried :
with data as(
select employee_detail,
json_extract_scalar((replace(employee_detail, '''', '"')),
'$.empdetail.JoiningDetail.JoinedGeneratedTimestamp') as JoinedGeneratedTimestamp
from "employee" limit 5 )
select employee_detail, JoinedGeneratedTimestamp
from data
Getting blank for JoinedGeneratedTimestamp field.
答案1
得分: 0
json_extract_scalar -
"json_extract_scalar" -
```JSON
{
"join_generated_timestamp": 1683295496534,
"position": 12115
}
json_extract -
"json_extract" -
```JSON
{
"join_generated_timestamp": 1683295496534,
"position": 12115
}
英文:
Are you sure the "json" like field is in that format? also, maybe the JoinedGeneratedTimestamp sometimes holds non- scalar values?
the json_exctract_scalar can only extract boolean, number and string
if you wish to extract arrays, maps or structs (more complex types) use the regular json_extract
For both cases, based only on the data example you provided, it seems to work just fine
json_extract_scalar -
WITH dataset AS (
SELECT '{''emp'': ''myself'', ''dept'': ''IT'', ''empid'': ''12345'', ''empdetail'': {''JoiningDetail'': {''JoinedGeneratedTimestamp'': 1683295496534, ''position'': 12115}, ''xyz'': None, ''abc'': None}, ''title'': ''SrDevelop''}'
AS employee_detail
)
select
json_extract_scalar(replace(employee_detail, '''', '"'), '$.empdetail.JoiningDetail.JoinedGeneratedTimestamp') as join_generated_timestamp,
json_extract_scalar(replace(employee_detail, '''', '"'), '$.empdetail.JoiningDetail.position') as position
from dataset
output -
join_generated_timestamp, position
1683295496534, 12115
json_extract -
WITH dataset AS (
SELECT '{''emp'': ''myself'', ''dept'': ''IT'', ''empid'': ''12345'', ''empdetail'': {''JoiningDetail'': {''JoinedGeneratedTimestamp'': 1683295496534, ''position'': 12115}, ''xyz'': None, ''abc'': None}, ''title'': ''SrDevelop''}'
AS employee_detail
)
select
json_extract(replace(employee_detail, '''', '"'), '$.empdetail.JoiningDetail.JoinedGeneratedTimestamp') as join_generated_timestamp,
json_extract(replace(employee_detail, '''', '"'), '$.empdetail.JoiningDetail.position') as position
from dataset
output -
join_generated_timestamp, position
1683295496534, 12115
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论