Search code examples
sqlstringamazon-athenaprestotrino

Select a string within a string within ATHENA

I have a table in AWS ATHENA that I need to clean up for production, but having difficulties extracting only a specfic portion of a string.

EXAMPLE:

Column A
{"display_value":"TECH_FinOps_SERVICE","link":" https://sdfs.saff-now.com/api/now/v2/table/sys_user_group/8fc10b99dbeedf12321317e15b9619b2"}

Basically I would like to just extract Tech_FinOps_Service from the string in Column_A

Solution

  • Your string looks like json so you can try using json functions:

    -- sample data
WITH dataset(column_a) AS (
    values ('{"display_value":"TECH_FinOps_SERVICE","link":" https://sdfs.saff-now.com/api/now/v2/table/sys_user_group/8fc10b99dbeedf12321317e15b9619b2"}')
)

-- query
select json_extract_scalar(column_a, '$.display_value') display_value
from dataset;

    Output:

        display_value    
---------------------
 TECH_FinOps_SERVICE