How to Extract Nested JSON Array in Redshift and Create a Table with Specific Columns?

Question

I have a table in AWS Redshift with a column called json, which stores a nested JSON object as a string. I need to create a new table with three columns: sid, skill_name, and skill_vdd. The json column contains a nested JSON array under the key rows, and I want to extract the values from this array.

Here is a sample JSON in the json column:

{
    "Id": 888,
    "Command": "uuuu",
    "step": "rows": [
        {"sid": 1515, "skill_name": "jjjj", "skill_vdd": 9999},
        {"sid": 333, "skill_name": "ttt", "skill_vdd": 333}
    ]
}

I've tried using various redshift json functions, but I’m not able to flatten the json

How can I flatten this nested JSON and create a table with the following columns: sid, skill_name, skill_vdd from the json column in Redshift?

Bill Weiner · Accepted Answer · 2024-12-03 23:46:55Z

1

I'm going to assume that all entries for this column don't just have 2 values in their array "rows". In this case you will want to json_parse() this data into a SUPER data type and then "unnest" these array values. AWS has a good page on unnesting here - https://docs.aws.amazon.com/redshift/latest/dg/query-super.html

This question comes up a lot and I've answered it before here - UNNEST Super data in redshift

If you need help with getting this to work reach out again and show what code you have so far.

answered Dec 3, 2024 at 23:46

Bill Weiner

11.3k2 gold badges12 silver badges22 bronze badges

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

3 Comments

nilesh1212 Dec 4, 2024 at 10:31

Hi Bill, followed this page and it worked for me: medium.com/fishtown-analytics/…

nilesh1212 Dec 4, 2024 at 13:09

if you have better solution please share here.

Bill Weiner Dec 4, 2024 at 15:10

Better is relative. If what you have fully works and is fast enough then you are done, so it's better. However, Redshift has a built-in method for unnesting arrays that will be much less SQL, faster, and more supportable in the long-run. It just takes parsing your json into a super type and then following the process laid out in the AWS page.

Collectives™ on Stack Overflow

How to Extract Nested JSON Array in Redshift and Create a Table with Specific Columns?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related