Convert SUPER field array of json objects into rows for single field

Question

Assume I have a Redshift table my_schema.my_table that contains a record as such:

id   col1   my_list
-------------------
1    true   [{"id": "A", "name": "foo"}, {"id": "B", "name": "bar"}]

The column my_list is a SUPER field. My understanding is that I would need to somehow iterate over the array, using get_array_length(my_list) and I recognize I can extract elements as such:

select
  my_list[0]."id",
  my_list[0]."name"
from my_schema.my_table

Is there a simple Redshift method for doing this and ultimately achieving a table that looks as such:

id   name
---------
A    foo
B    bar

Important to note, that this should happen dynamically. I will not know the size of the array. But the same key value pairs will always exist, i.e. id and name.

EDIT: Here is my best guess.

SELECT
  my_list[idx]."id",
  my_list[idx]."name"
FROM my_schema.my_table
CROSS JOIN (
  SELECT generate_series(0, GET_ARRAY_LENGTH(my_list) - 1) AS idx
  FROM my_schema.my_table
) AS idx_table

but I am getting the error:

[0A000] ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

Does this answer your question? Return elements of Redshift JSON array on separate rows — nbk
– nbk, Commented Feb 22, 2023 at 20:54
why not, you have a json and need to unest it, as you can read readsiht has not that much json support — nbk
– nbk, Commented Feb 22, 2023 at 21:06

Derek Herincx · Accepted Answer · 2023-02-22 22:20:51Z

2

Yes, this is possible within Redshift. You would first need to iterate over my_list and create a single row per list item. Once you do that, you are able to parse out whatever fields you need from each individual array.

with cte as (
    SELECT item
    FROM my_schema.table t, t.my_list AS item AT index
)
select item.{whatever-key} from cte

edited Feb 22, 2023 at 22:20

answered Feb 22, 2023 at 22:02

Derek Herincx

486 bronze badges

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

Collectives™ on Stack Overflow

Convert SUPER field array of json objects into rows for single field

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related