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']}

Answer 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.