求助：在Hive中实现JSON数据拆解为常规行的方法

Hey there! Let's fix this Hive JSON parsing issue you're stuck on. The problem with your original query is that you tried to extract key directly from the entire issues array string, instead of first breaking that array down into individual JSON objects. Let's walk through two solutions that match your desired output formats:

Solution 1: One Row Per Label (Expanded Format) #

This will split each label into its own row while keeping the associated key:

SELECT 
  get_json_object(issue, '$.key') AS Key,
  label AS labels
FROM demo_example d
-- Break the top-level issues array into individual issue objects
LATERAL VIEW explode(json_array(d.a1, '$.issues')) exploded_issues AS issue
-- Break the labels array inside each issue into separate rows
LATERAL VIEW explode(json_array(issue, '$.labels')) exploded_labels AS label;

How this works: #

• json_array(d.a1, '$.issues') pulls the issues array from your raw JSON string
• explode() splits that array into separate rows, each holding one issue object
• We repeat the explode process on the labels array inside each issue to get one label per row

Solution 2: Single Row With Comma-Separated Labels #

If you prefer all labels grouped into a single row for each key, use this query:

SELECT 
  get_json_object(issue, '$.key') AS Key,
  -- Strip the square brackets from the labels array to make a comma-separated string
  regexp_replace(get_json_object(issue, '$.labels'), '\\[|\\]', '') AS labels
FROM demo_example d
-- First split the issues array into individual objects
LATERAL VIEW explode(json_array(d.a1, '$.issues')) exploded_issues AS issue;

Alternatively, use grouping for more control over the concatenation:

SELECT 
  get_json_object(issue, '$.key') AS Key,
  concat_ws(',', collect_list(label)) AS labels
FROM demo_example d
LATERAL VIEW explode(json_array(d.a1, '$.issues')) exploded_issues AS issue
LATERAL VIEW explode(json_array(issue, '$.labels')) exploded_labels AS label
GROUP BY get_json_object(issue, '$.key');

Why Your Original Query Failed #

Your initial query used json_tuple to pull the entire issues array as a single string. When you tried to extract key from that string, Hive couldn't parse it—because it's an array of objects, not a single object. Using explode() on the array first breaks it down into manageable individual objects that you can parse with get_json_object or json_tuple.

内容的提问来源于stack exchange，提问作者Tharunkumar Reddy