Search code examples
sqlhivehiveql

Hive/SQL query to extract value of the keyword "swid" from a table struct filed "Details"

Hive/SQL query to extract value of the keyword "swid" from a table struct filed "Details".

column - "Details"

Value - "id":123;"name":"Alex";"depID":100;"swid":5456213

Desired Output:

swid
5456213
Solution

  • Using sts_to_map function:

     with test_data as (select '"id":123\\;"name":"Alex"\\;"depID":100\\;"swid":5456213' as str)

 select str, str_to_map(regexp_replace(str,'\\"',''),'\\;',':')['swid'] as swid from test_data
;

    Result:

    OK
str                                                     swid
"id":123;"name":"Alex";"depID":100;"swid":5456213       5456213
Time taken: 0.971 seconds, Fetched: 1 row(s)

    One more solution is to convert to valid JSON (replace semicolon with comma) then use get_json_object to extract element:

    with test_data as (select '"id":123\\;"name":"Alex"\\;"depID":100\\;"swid":5456213' as str)

select str, get_json_object(concat('{',regexp_replace(str,'\\;',','),'}'),'$.swid')  as swid from test_data;
OK
str                                                     swid
"id":123;"name":"Alex";"depID":100;"swid":5456213       5456213
Time taken: 6.54 seconds, Fetched: 1 row(s)

    Using regexp_extract:

    select str, regexp_extract(str,'\\"swid\\":(\\d+)',1) as swid from test_data;