How to convert array of string into json string in hive?

Question

My hive table test_tbl has a string field tag_score, which contains some json string separated by ";":

----------
tag_score |
--------------------------------------------------------------------------------
{"keyword":"abc","score": "0.6"};{"keyword":"烟花","score":"0.516409816917747"} |
--------------------------------------------------------------------------------

How can I extract the correct json from it? The result string should be like this:

[{"keyword":"abc","score": "0.6"},{"keyword":"烟花","score":"0.516409816917747"}]

I've tried this hive sql:

select split(tag, ";") from test_tbl;

But I got array of string, not the desired one:

["{"keyword":"abc","score": "0.6"}","{"keyword":"烟花","score":"0.516409816917747"}"]

leftjoin · Accepted Answer · 2021-03-10 09:11:29Z

1

You may need to split array and parse struct elements if you want to get array<struct<...>> type. If you just want to get JSON string, all you need is string manipulation: replace and concat.

Replace semicolon between curly brackets with comma, concatenate with square brackets

concat('[',regexp_replace(tag_score ,'\\}\073\\{','},{'),']')

\073 - is a semicolon.

If it can be spaces between curly brackets and semicolon, use '\\}\\s*\073\\s*\\{' regexp, it will work the same with any number of spaces, like this: } ; {

edited Mar 10, 2021 at 9:11

answered Mar 10, 2021 at 8:52

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

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

Collectives™ on Stack Overflow

How to convert array of string into json string in hive?

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