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.
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