Skip to main content
Rich_Learner
Silver ⭐️⭐️⭐️
February 12, 2025
Solved

I have a HIVE table which contains JSON as a column. I need to extract a struct in this JSON .But Lateral explode is not helping.

The JSON is example is :

{
"app": {
"Id": "123",
"apply": [
{
"flag": "1",
"Product": [
{
"Code": "DZ9e",
"Line": "10000",
"status": "C",
}
{
"Code": "EZ9e",
"Line": "19000",
"status": "C",
}
]
}
}

I need output like

IDFlagCodeLineStatus
1231DZ9e10000C
1231EZ9e19000C

 

I have used lateral explode/lalteral inline explode . But it is not giving value in separate rows.

Anybody has any solution

smruti
smrutiAnswer
Gold ⭐️⭐️⭐️
February 17, 2025

ahh 
@Rich_Learner please use the following query. I just tested. It should work.

WITH json_extract AS (
SELECT
get_json_object(xml_data, '$.app.Id') AS ID,
get_json_object(xml_data, '$.app.apply[0].flag') AS Flag,
regexp_replace(regexp_replace(get_json_object(xml_data, '$.app.apply[0].Product'), '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{') AS products
FROM check
)
SELECT
ID,
Flag,
get_json_object(product_data, '$.Code') AS Code,
get_json_object(product_data, '$.Line') AS Line,
get_json_object(product_data, '$.status') AS Status
FROM json_extract
LATERAL VIEW explode(split(products, ';')) p AS product_data;

10 replies

VidyaSargur
Community Manager
Community Manager
February 13, 2025

@Rich_Learner, Welcome to our community! To help you get the best possible answer, I have tagged our Hive experts @smruti who may be able to assist you further.

Please feel free to provide any additional information or details about your query, and we hope that you will find a satisfactory solution to your question.

Translate
Regards, Vidya Sargur, Community Manager Was your question answered? Make sure to mark the answer as the accepted solution. If you find a reply useful, say thanks by clicking on the thumbs up button.
smruti
Gold ⭐️⭐️⭐️
Gold ⭐️⭐️⭐️
February 13, 2025

@Rich_Learner could you please share your table definition? 

show create table <table_name>

I wanted to see if you are using textfile or json serde. Also I could try replicating the issue with that info.

Translate
Rich_Learner
Silver ⭐️⭐️⭐️
February 13, 2025

Hi I am attaching the result for a similar example:

Rich_Learner_0-1739440051869.png

column xml_data contains json

Translate
Rich_Learner
Silver ⭐️⭐️⭐️
February 17, 2025

Thanks a lot.. It worked like magic 😄

 

Translate