2023年6月22日 15:18:14go评论127阅读模式

英文:

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, '&#39;', '&quot;')),
     '$.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:

{&#39;emp&#39;: &#39;myself&#39;, &#39;dept&#39;: &#39;IT&#39;, &#39;empid&#39;: &#39;12345&#39;, &#39;empdetail&#39;: {&#39;JoiningDetail&#39;: {&#39;JoinedGeneratedTimestamp&#39;: 1683295496534, &#39;position&#39;: 12115}, &#39;xyz&#39;: None, &#39;abc&#39;: None}, &#39;title&#39;: &#39;SrDevelop&#39;}

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, &#39;&#39;&#39;&#39;, &#39;&quot;&#39;)),
     &#39;$.empdetail.JoiningDetail.JoinedGeneratedTimestamp&#39;) as JoinedGeneratedTimestamp 
  from &quot;employee&quot; 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 &#39;{&#39;&#39;emp&#39;&#39;: &#39;&#39;myself&#39;&#39;, &#39;&#39;dept&#39;&#39;: &#39;&#39;IT&#39;&#39;, &#39;&#39;empid&#39;&#39;: &#39;&#39;12345&#39;&#39;, &#39;&#39;empdetail&#39;&#39;: {&#39;&#39;JoiningDetail&#39;&#39;: {&#39;&#39;JoinedGeneratedTimestamp&#39;&#39;: 1683295496534, &#39;&#39;position&#39;&#39;: 12115}, &#39;&#39;xyz&#39;&#39;: None, &#39;&#39;abc&#39;&#39;: None}, &#39;&#39;title&#39;&#39;: &#39;&#39;SrDevelop&#39;&#39;}&#39;
    AS employee_detail
)
select 
json_extract_scalar(replace(employee_detail, &#39;&#39;&#39;&#39;, &#39;&quot;&#39;), &#39;$.empdetail.JoiningDetail.JoinedGeneratedTimestamp&#39;) as join_generated_timestamp,
json_extract_scalar(replace(employee_detail, &#39;&#39;&#39;&#39;, &#39;&quot;&#39;), &#39;$.empdetail.JoiningDetail.position&#39;) as position
from dataset

output -

join_generated_timestamp, position
1683295496534, 12115

json_extract -

WITH dataset AS (
  SELECT &#39;{&#39;&#39;emp&#39;&#39;: &#39;&#39;myself&#39;&#39;, &#39;&#39;dept&#39;&#39;: &#39;&#39;IT&#39;&#39;, &#39;&#39;empid&#39;&#39;: &#39;&#39;12345&#39;&#39;, &#39;&#39;empdetail&#39;&#39;: {&#39;&#39;JoiningDetail&#39;&#39;: {&#39;&#39;JoinedGeneratedTimestamp&#39;&#39;: 1683295496534, &#39;&#39;position&#39;&#39;: 12115}, &#39;&#39;xyz&#39;&#39;: None, &#39;&#39;abc&#39;&#39;: None}, &#39;&#39;title&#39;&#39;: &#39;&#39;SrDevelop&#39;&#39;}&#39;
    AS employee_detail
)
select 
json_extract(replace(employee_detail, &#39;&#39;&#39;&#39;, &#39;&quot;&#39;), &#39;$.empdetail.JoiningDetail.JoinedGeneratedTimestamp&#39;) as join_generated_timestamp,
json_extract(replace(employee_detail, &#39;&#39;&#39;&#39;, &#39;&quot;&#39;), &#39;$.empdetail.JoiningDetail.position&#39;) as position
from dataset

output -

join_generated_timestamp, position
1683295496534, 12115

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

Unnest Json Array json_extract_scalar

问题

答案1

使用嵌套循环在Python中输入一个二维数组。

数组为什么需要引用，而向函数传递参数时，向量不需要引用？

如何在 JavaScript 的对象数组中格式化特定值？

Go – JSON Decoder not initializing my struct

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。