hive - How to extract array elements from json string in hive table?

Question

I have a hive table which contains a json string:

column
{"id": 123, "ShortName":"test tax","LongName":"test","Description":"test","aFactor":"true","bFactor":"true","cFactor":"false"}
{"id": 456, "ShortName":"test tax","LongName":"test","Description":"test","aFactor":"false","bFactor":"true","cFactor":"true"}
{"id": 678, "ShortName":"test tax","LongName":"test","Description":"test","aFactor":"false","cFactor":"true"}

I want to create an array from this where ever aFactor, bFactor or cFactor is True:

id	factors
123	aFactor,bFactor
456	bFactor,cFactor
678	cFactor

being false or null is equivalent.

标签： hiveuser-defined-functions

Answer 1

您可以使用以下步骤执行此操作 -

1. 首先确定 *Factor 字段在哪里。
2. 如果它们为真，则使用它们，然后将它们连接起来整个查询应如下所示 -

select get_json_object(str,'$.id') as id,
if(get_json_object(str,'$.aFactor')='true','aFactor','') ||if(get_json_object(str,'$.bFactor')='true',',bFactor','')||if(get_json_object(str,'$.cFactor')='true',',cFactor','') factors
from mytable

示例结果在屏幕截图中。测试查询

with tab as ( select '{"id": 123, "ShortName":"test tax","LongName":"test","Description":"test","aFactor":"true","bFactor":"true","cFactor":"false"}' str)
select get_json_object(str,'$.id') as id,
if(get_json_object(str,'$.aFactor')='true','aFactor','') ||  
if(get_json_object(str,'$.bFactor')='true',',bFactor','') ||
if(get_json_object(str,'$.cFactor')='true',',cFactor','') 
 factors