How to extract a value from a string column using hive-CodePudding

I need to extract a field from a string column using hive

Input:  [{"name":"MANAGER"}]
Output:    MANAGER

I was able to fetch the record using the below regular expression, but I am not able to remove ] from the output.

Query built:

select split(regexp_replace('([{"name":"MANAGER"}])','^\\(|\\)$|[{"}]',''),': *')[1];

Output obtained:

MANAGER]

Could you please help me to remove the ] from the output and get only MANAGER in this example using hive.

CodePudding user response：

You can atually parse this with get_json_object function as the string you shared is a JSON string:

select get_json_object(regexp_replace('[{"name":"MANAGER"}]', '[\\[\\]]', ''), '$.name')

See the documentation:

get_json_object
A limited version of JSONPath is supported:

$ : Root object

. : Child operator

[] : Subscript operator for array

* : Wildcard for []

Syntax not supported that's worth noticing:

: Zero length string as key

.. : Recursive descent

@ : Current object/element

() : Script expression

?() : Filter (script) expression.

[,] : Union operator

[start:end.step] : array slice operator