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


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 '{
        AS jsondata
      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