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.

JSON-to-JSON Simplified with Apache NiFi and Jolt

How to Extract All Hive Tables DDL

Jolt spec to flatten the nested JSON

Table creation in HUE for JSON value

JSON to SQL using Spark

Extract string nested in JSON value

Convert data from JSON/CSV/Avro to Parquet with Ni...

Creating a HIVE table with structs inside structs ...

NIFI JSON event extraction only integers from stri...

Apache NIFI – JSON to XML Converter