Hive explode each JSON element in JSON array to rows

Question

I have neted JSON data like:

{"body":{"metrics":[{"a":1,"a1":1.1,"properties":{"propA":"name","propB":"age"}},{"b":2}]}}
{"body":{"metrics":[{"c":3}]}}

I can use select get_json_object(get_json_object(columnA,'$.Body'),'$.metrics') from... to extracted nested json arrays like:

[{"a":1,"a1":1.1,"a2":{"propA":"name","propB":"age"}},{"b":2}]
[{"c":3}]

I want to convert them into rows like:

{"a":1,"a1":1.1,"a2":{"propA":"name","propB":"age"}}
{"b":2}
{"c":3}

It seems the array returned by get_json_object is string and when I use later view it always says： UDFArgumentException explode() takes an array or a map as a parameter

Length of each array is uncertain and I do not have permit to upload jar files to active new udf or serde clases. How to achieve this...

@leftjoin Thanks for your reply. I'm not sure... It's returned by get_json_object and it looks like string.. Or do you have any better idea? Btw, I just modified the original question post. — user2894829
– user2894829, Commented Jun 22, 2020 at 11:32

Prabhat Ratnala · Accepted Answer · 2020-06-23 16:57:37Z

2

Say your json is in a table test. You can use below query to get the desired output:

select m.* from test s
LATERAL view explode(split(regexp_replace(get_json_object(value,'$.body.metrics'),'^\\[|]$',''), ',(?!")')) m

answered Jun 23, 2020 at 16:57

Prabhat Ratnala

7058 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hive explode each JSON element in JSON array to rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related