Append data to JSON array in Redshift

Question

I have a Redshift table in which one of the columns is a JSON array. I would like to append some data into that array. Eg:

id | col1 | col2
1  | A    | {"key": []}
2  | B    | {"key": []}
3  | B    | {"key": ['A']}
4  | B    | {"key": ['A', 'B']}

I would like to create a statement like UPDATE <table> SET col2 = <something> where col1 = 'B' so that I get:

id | col1 | col2
1  | A    | {"key": []}
2  | B    | {"key": ['C']}
3  | B    | {"key": ['A', 'C']}
4  | B    | {"key": ['A', 'B', 'C']}

John Rotenstein · Accepted Answer · 2018-09-06 23:31:15Z

1

You'd have to write your own User Defined Function (UDF), passing in the current value of the column and the element you would like to add, then passing back the result.

Hwoever, you really should avoid JSON columns in Amazon Redshift if at all possible. They cannot take advantage of all the features that make Redshift great (columnar, SORTKEY, etc). Plus, you'll have problems like this that are not in the normal realm of a relational database.

answered Sep 6, 2018 at 23:31

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

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

Collectives™ on Stack Overflow

Append data to JSON array in Redshift

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related