Json_extract_scalar not working

Hi Team,

I was trying to use custom sql to query a JSON string from one of the column which has data in this format

          "duration": 0,
          "start_time": "2023-09-20T01:40:11Z",
          "created_time": "2023-09-20T01:40:08Z",
          "parent_account_sid": "",
          "end_time": "2023-09-20T01:51:51Z"

I am using json_extract_scalar(data,‘$.duration’) as duration, but it cannot fetch the value and coming up with the exception as below

8:27: Column '$.duration' cannot be resolved or requester is not authorized to access requested resources

Any help would be appreciated


Thank You ! I have figured out the issue. This is of type Struct so I dont need to use json_scalar_extract