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.

Guildencrantz · Accepted Answer · 2017-09-18 19:00:08Z

10

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'
)

edited Sep 18, 2017 at 19:00

Guildencrantz

1,9151 gold badge16 silver badges33 bronze badges

answered Oct 4, 2016 at 13:57

Jared Piedt

4665 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick Liu · Accepted Answer · 2019-02-20 19:06:37Z

4

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

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

answered Feb 20, 2019 at 19:06

Nick Liu

412 bronze badges

Collectives™ on Stack Overflow

Querying JSON array in Redshift?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related