I need to extract a json from below list of json based on id field value and display the key as columns
[{"id":"123","name":"ABC","age":"18","subject":"Maths","score":20},
{"id":"124","name":"ABCD","age":"20","subject":"History","score":40},
{"id":"213","name":"XYZ","age":"28","subject":"Economics","score":35}]
I am looking to display the final json based on key : 123 as below
id|name|age|subject|score
123|ABC|18|Maths|20
Please suggest ways to implement above in hive.
Explode array and use get_json_object to extract JSON elements.
Example:
with your_data as (
select stack(1, array(
'{"id":"123","name":"ABC","age":"18","subject":"Maths","score":20}',
'{"id":"124","name":"ABCD","age":"20","subject":"History","score":40}',
'{"id":"213","name":"XYZ","age":"28","subject":"Economics","score":35}')
) as json_array
)
select --t.json_array as initial_data,
--a.json,
get_json_object(a.json, '$.id') id,
get_json_object(a.json, '$.name') name,
get_json_object(a.json, '$.age') age,
get_json_object(a.json, '$.subject') subject,
get_json_object(a.json, '$.score') score
from your_data t
lateral view outer explode(json_array) a as json
where get_json_object(a.json, '$.id') = 123 ;
Result:
id name age subject score
123 ABC 18 Maths 20
# Header 1 #
Writing a function to get the index of the object by mapping it with the key
#Code#
let obj = [{"id":"123","name":"ABC","age":"18","subject":"Maths","score":20},{"id":"124","name":"ABCD","age":"20","subject":"History","score":40},{"id":"213","name":"XYZ","age":"28","subject":"Economics","score":35}]
function getColoumn(_key){
for(i in obj){
if(obj[i].id ===_key){
return obj[i];
}
}
}
getColoumn("123")
