Support Questions

Rich_Learner · ‎02-12-2025

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

smruti · ‎02-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;

View solution in original post

Rich_Learner · ‎02-17-2025

Thanks a lot.. It worked like magic 😄

Cloudera Community

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.