question Re: 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. in Support Questions

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.

Rich_Learner — Wed, 12 Feb 2025 09:38:33 GMT

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

ID	Flag	Code	Line	Status
123	1	DZ9e	10000	C
123	1	EZ9e	19000	C

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

Anybody has any solution

Re: 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.

VidyaSargur — Thu, 13 Feb 2025 07:41:36 GMT

@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.

Re: 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.

smruti — Thu, 13 Feb 2025 09:04:56 GMT

@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.

Re: 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.

Rich_Learner — Thu, 13 Feb 2025 09:48:46 GMT

Hi I am attaching the result for a similar example:

column xml_data contains json

Re: 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.

smruti — Thu, 13 Feb 2025 10:57:06 GMT

Try something like this :

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, get_json_object(xml_data, '$.app.apply[0].Product') AS products_json FROM check ) SELECT ID, Flag, get_json_object(product_item, '$.Code') AS Code, get_json_object(product_item, '$.Line') AS Line, get_json_object(product_item, '$.status') AS Status FROM json_extract LATERAL VIEW OUTER EXPLODE(split(regexp_replace(regexp_replace(products_json, '\\[|\\]', ''), '\\}\\s*,\\s*\\{', '}|{'), '|')) p AS product_item;

Re: 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.

Rich_Learner — Thu, 13 Feb 2025 12:11:27 GMT

Thanks for the solution.

Right the code is creating separate columns . But I am getting around 143 rows whereas technically there should be 3 rows and value is coming as NULL.

here consider first column as id

Re: 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.

Rich_Learner — Thu, 13 Feb 2025 13:18:50 GMT

Here Phonetype and Phone availabilityflag are my array which I want in rows

Re: 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.

smruti — Thu, 13 Feb 2025 16:54:00 GMT

Try :

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, 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 OUTER EXPLODE(SPLIT(products, '},')) p AS product_data;

Re: 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.

Rich_Learner — Fri, 14 Feb 2025 14:53:10 GMT

Thanks @smruti .

I am getting 3 rows now but the first two values as populated as NULL only the last values is correctly populated.

Re: 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.

smruti — Mon, 17 Feb 2025 14:54:34 GMT

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;

Re: 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.

Rich_Learner — Mon, 17 Feb 2025 15:55:11 GMT

Thanks a lot.. It worked like magic 😄