CODEWITHSUNDEEP
splithivekey-value
Jon
Jon

Reputation: 11

Parse/ Query Key Value Pairs in SQL/ Hive

I am trying to Parse/ Retrieve Data (Key Value Pairs) Value from a Column in Hive.

Did not manage to extract the "State Name" with a Split Function and perhaps a Regular Expression.

**split(company.headquaters, "\...")[1] as State**

Please need some quick help or advice :) Best regards

company.headquarters:

{"city":"St. Paul","state":" Minnesota"}
{"city":"North Chicago","state":" Illinois"}
{"city":"Dublin","state":" Ireland"}
{"city":"Santa Monica","state":" California"}

Upvotes: 1

Views: 375

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

Could you pls try this -

select get_json_object(company.headquarters,'$.state') as state from company; work

Here is what i tested - Test Screenshot

Upvotes: 1

Related Questions