Querying JSON array in Redshift?

Question

Are you able to query a field within an array in a Redshift JSON column?

I have the following JSON:

{"sort_details":[{"sort_by":"name","order":"asc"}]}

Is it possible to query anything lower than the highest level element in Redshift? I've tried using

json_extract_path_text( myjson , 'sort_details' , 'sort_by' ) 

but got a null row back. I'm guessing that being an array and conceivably returning multiple results per record, this might not be possible.

Answer 1

You could use nested JSON functions:

json_extract_path_text(
    json_extract_array_element_text(
        json_extract_path_text( 
            myjson, 
            'sort_details'
        ), 
        0
    ), 
    'sort_by'
)

Answer 2

JSON_EXTRACT_PATH_TEXT(myjson, 'sort_details', 0, 'sort_by')

This also works, but it's not documented in AWS Doc