Parse json undetermined number of nested with hive

Question

I want to parse a JSON with Hive.

I know the usual functions for parsing a JSON, but the problem is the structure of the JSON. It has an indeterminate number of tracks.features maybe 2, maybe 10, maybe 100 and I need to take the coordinates field.

Is it possible to do this on Hive?

This is the JSON:

{
    "track":{
        "features":[
            {
                "geometry":{
                    "coordinates":[
                        -3.7175826,
                        40.4342724997222
                    ]
                }
            },
            {
                "geometry":{
                    "coordinates":[
                        -3.7170288,
                        40.4324255
                    ]
                }
            }
        ]
    }
}

enter image description here

Possible duplicate of [Explode the Array of Struct in Hive](https://stackoverflow.com/questions/11373543/explode-the-array-of-struct-in-hive) — mazaneicha, Jul 18 '19 at 00:14

score 0 · Answer 1 · answered Jul 18 '19 at 10:57

with your_data as(
select '{
    "track":{
        "features":[
            {
                "geometry":{
                    "coordinates":[
                        -3.7175826,
                        40.4342724997222
                    ]
                }
            },
            {
                "geometry":{
                    "coordinates":[
                        -3.7170288,
                        40.4324255
                    ]
                }
            }
        ]
    }
}' as json
)

select get_json_object(json, '$.track.features[0].geometry.coordinates[1]') as latitude1 from your_data d

Returns:

OK
40.4342724997222

Time taken: 0.058 seconds, Fetched: 1 row(s)

Parse json undetermined number of nested with hive

1 Answers1