如何在具有JSON值的Amazon Athena上查询(搜索)sql? [英] how to query(search) of sql on Amazon Athena which has JSON value?

问题描述

[查询时出现第一个错误

 从属性'year'= 2007//不起作用的表中选择*//请检查表格的屏幕截图 

我想通过sql查询对Athena数据集进行查询.我尝试了每个查询，但不适用于该雅典娜数据

解决方案

我假设 properties 列是 STRING ，在这种情况下，您可以这样做以提取 year 字段并在过滤器中使用它:

  SELECT * FROM表WHERE JSON_EXTRACT_SCALAR(properties，'$ .year')='2007' 

请注意，它是'2007'，而不是 2007 ，因为从您的屏幕快照中看，值似乎是字符串.

您可以详细了解 JSON_EXTRACT_SCALAR > Athena当前使用的Presto版本的文档中的>函数和其他JSON操作函数.

.

[1st err while querying][1]

select * from table where properties 'year' = 2007 // is not working
//please check the screen shot of the table

I want to query on Athena dataset throug sql query. I tried every query but its not working on this Athena data

解决方案

I assume the properties column is a STRING, in that case you can do this to extract the year field and use it in a filter:

SELECT * FROM table WHERE JSON_EXTRACT_SCALAR(properties, '$.year') = '2007'

Notice that it's '2007', not 2007, since from your screenshot it looks like the values are strings.

You can read more about the JSON_EXTRACT_SCALAR function and other JSON manipulation functions in the documentation for the Presto version that Athena is currently using.

这篇关于如何在具有JSON值的Amazon Athena上查询(搜索)sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！