Filter sql records based on a json stored as string value in a column?

I have a sql table with rows roll_num(int),name(string),class(string),marks(string). The marks column stores string thats actually a json for ex

{
"english":25,
"maths":30,
"science":78
}

Now for example I want all the rows where maths marks are greater than 30. Any idea how to do that ?

1 answer

  • answered 2019-09-30 07:11 Harsh Bafna

    To extract the properties from the JSON string, use the json_extract function.

    Sample code :

    WITH dataset AS (
      SELECT '{
    "english":25,
    "maths":30,
    
    "science":78
    }'
        AS jsondata
    )
    SELECT
      json_extract(jsondata, '$.english') AS english,
      json_extract(jsondata, '$.maths') AS maths,
      json_extract(jsondata, '$.science') AS science
    FROM dataset
    

    Output :

        english maths   science
    1     25     30       78
    

    For details on working with json strings in athena refer following AWS documentation:

    Extracting data from json string in athena