1

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Imran
  • 121
  • 4
  • 9

2 Answers2

1

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
leftjoin
  • 36,950
  • 8
  • 57
  • 116
0
# 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")
Sri Harsha
  • 33
  • 6