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.

[#1]

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

[#2]

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

[#3]

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

[#4]

Hi I am attaching the result for a similar example:

column xml_data contains json

[#5]

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;

[#6]

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

[#7]

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

[#8]

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;

[#9]

Thanks @smruti .

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

[#10 · Accepted solution]

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;