Hive从json行中选择一个特定的字符串 [英] Hive select a particular string from a json row

问题描述

我试图分析Athena中的AWS云跟踪日志，因为如果我选择安全组添加入站规则事件，它会返回元素列中的下面的字符串 p>

Im trying to analyze AWS cloud trail logs in Athena, for that If I select security group add inbound rules event it returns the below string in elements column.

{"groupId":"sg-XXXX","ipPermissions":{"items":[{"ipProtocol":"tcp","fromPort":22,"toPort":22,"groups":{},"ipRanges":{"items":[{"cidrIp":"0.0.0.0/0"}]},"prefixListIds":{}}]}}

但是我需要 groupId 独自从那个json结果。那么我怎么能得到它？
$ b

But I need groupId alone from that json results. So how can I get that ?

注意：表格是一个外部表格

Note: The tables is an external table

推荐答案

select json_extract_scalar('{"groupId":"sg-XXXX","ipPermissions":{"items":[{"ipProtocol":"tcp","fromPort":22,"toPort":22,"groups":{},"ipRanges":{"items":[{"cidrIp":"0.0.0.0/0"}]},"prefixListIds":{}}]}}','$.groupId');
OK
sg-XXXX

这篇关于Hive从json行中选择一个特定的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！