时间戳

首页 > 解决方案 > 如何在 Athena (Presto) 中查询和迭代结构数组？

sql - 如何在 Athena (Presto) 中查询和迭代结构数组？

问题描述

我有一个包含 500,000 多条记录的 S3 存储桶json，例如。

{
  "userId": "00000000001",
  "profile": {
    "created": 1539469486,
    "userId": "00000000001",
    "primaryApplicant": {
      "totalSavings": 65000,
      "incomes": [
        { "amount": 5000, "incomeType": "SALARY", "frequency": "FORTNIGHTLY" },
        { "amount": 2000, "incomeType": "OTHER", "frequency": "MONTHLY" }
      ]
    }
  }
}

我在 Athena 中创建了一个新表

CREATE EXTERNAL TABLE profiles (  
  userId string,
  profile struct<
    created:int,
    userId:string,
    primaryApplicant:struct<
      totalSavings:int,
      incomes:array<struct<amount:int,incomeType:string,frequency:string>>,
    >
  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://profile-data'

我对incomeTypes例如感兴趣。"SALARY", "PENSIONS", "OTHER", 等等.. 并且jsonData.incometype每次运行这个查询都会改变：

SELECT jsonData
FROM "sampledb"."profiles"

CROSS JOIN UNNEST(sampledb.profiles.profile.primaryApplicant.incomes) AS la(jsonData)

WHERE jsonData.incometype='SALARY'

这很好用CROSS JOIN UNNEST，它使收入数组变平，以便上面的数据示例跨越 2 行。唯一特殊的事情是CROSS JOIN UNNEST使所有字段名称都小写，例如。一行看起来像这样：

{amount=1520, incometype=SALARY, frequency=FORTNIGHTLY}

现在有人问我有多少用户有两个或更多"SALARY"条目，例如。

      "incomes": [
        { "amount": 3000, "incomeType": "SALARY", "frequency": "FORTNIGHTLY" },
        { "amount": 4000, "incomeType": "SALARY", "frequency": "MONTHLY" }
      ],

我不知道该怎么做。

  1. 如何查询结构数组以查找重复incomeTypes的"SALARY"？

  2. 我必须遍历数组吗？

  3. 结果应该是什么样子？

标签： sqljsonamazon-athenapresto

解决方案

UNNEST是一个非常强大的功能，使用它可以解决这个问题。但是，我认为使用Presto 的 Lambda 函数更直接：

SELECT COUNT(*)
FROM sampledb.profiles
WHERE CARDINALITY(FILTER(profile.primaryApplicant.incomes, income -> income.incomeType = 'SALARY')) > 1

此解决方案FILTER在profile.primaryApplicant.incomes数组上​​使用仅获取具有incomeTypeof 的那些SALARY，然后CARDINALITY提取该结果的长度。

区分大小写对 SQL 引擎来说绝非易事。总的来说，我认为你不应该期望他们尊重案例，很多人不这样做。Athena 尤其将列名显式转换为小写。

推荐阅读