Extracting data from JSON field in Amazon Redshift

I am trying to extract some data from a JSON field in Redshift.

Given below is a sample view of the data I am working with.

{"fileFormat":"excel","data":{"name":John,"age":24,"dateofbirth":1993,"Class":"Computer Science"}}

I am able to extract data for the first level namely data corresponding to fileFormat and data as below:

select CONFIGURATION::JSON -> 'fileFormat' from table_name;

I am trying to extract information under data like name, age,dateofbirth

Answer

You could use Redshift’s native function json_extract_path_text
– https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html

SELECT
  json_extract_path_text(
    configuration,
      'data',
      'name'
  )
    AS name,
  json_extract_path_text(
    configuration,
      'data',
      'age'
  )
    AS age,
  etc
FROM
  yourTable