Can you store JSON fields on Redshift?

Question

Does Redshift support JSON fields, like Postgresql's json data type? If so what do I do to use it?

Somewise

Answer 1

You can store JSON in Amazon Redshift, within a normal text field.

There are functions available to extract data from JSON fields, but it is not an effective way to store data since it doesn't leverage the full capabilities of Redshift's column-based architecture.

See: Amazon Redshift documentation - JSON Functions

Isooctane

Answer 2

You can use the SUPER type to store JSON data within Redshift, using JSON_PARSE(...) to convert text to JSON as required.

See https://docs.aws.amazon.com/redshift/latest/dg/r_SUPER_type.html

For example:

CREATE TABLE t
(
   id   INT,
   item SUPER
);

INSERT INTO t VALUES(1, JSON_PARSE('{"name":"Beethoven", "born":1770}'));

SELECT id, item.born FROM t WHERE item.name = 'Beethoven';

You can query attributes within your JSON field content using the dot notation, as shown above. You can also join on JSON attributes.

Note that Redshift's SUPER type currently has a limit of 16MB.

Zolnay

Answer 3

UPDATE: Redshift now supports Data column of type "super" which allows saving JSONs and also querying over it. Added a link to video that further explains the new option: https://www.youtube.com/watch?v=PR15TVZDgy4

Iranian

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Squires