SQL/JSON functions are partitioned into two groups: constructor functions (JSON_OBJECT, JSON_OBJECT_AGG, JSON_ARRAY, and JSON_ARRAYAGG ) and query functions (JSON_VALUE, JSON_TABLE, JSON_EXISTS, and JSON_QUERY).Ĭonstructor functions use values of SQL types and produce JSON values (JSON objects or JSON arrays) represented in SQL character or binary string types. Here we are discussing features does not support by Redshift described by the 2016 SQL standard. Let’s have a look which of these functions are supported by Redshift. 22 of these extensions are in relation to working with JSON documents inside a database.

The ANSI 2016 SQL standard introduced 44 optional extensions. Redshift and the ANSI 2016 SQL JSON extensions This makes it very inconvenient or impossible to convert JSON to a relational structure with tables, rows, and columns. There is not RedShift JSON functions that can expand an array record from one row into one row for each array element.Amazon Redshift doesn’t support VARCHAR data types larger than 64 KB, so we can’t store traditional LOBs on Amazon Redshift. VARCHAR is better if the strings include multi-byte characters. JSON uses UTF-8 encoded text strings, so JSON strings can only be stored as CHAR or VARCHAR data types.Redshift is not a good choice for storing large JSON data sets because, by storing disparate data in a single column, JSON does not leverage Amazon Redshift’s column store architecture.This actually increase execution time of JSON functions. Redshift checks for valid JSON text every time it execute any JSON function on JSON text field.

Select channelgrouping, json_extract_path_text ( trafficsource, 'campaign', true ) As campaign from traffic Following shows contents of a file named customer.json The order of the elements in the JSON file does not matter (matching is done by name). To load a JSON document with the ‘auto’ option requires a template that exactly maps the JSON elements to the table column names. This is a mapping document that COPY will use to map and parse the JSON source data into the target. We can automatically COPY fields from the JSON file by specifying the ‘auto’ option, or we can specify a JSONPaths file. The COPY command loads data into Redshift tables from JSON data files in an S3 bucket or on a remote host accessed via SSH. If you have questions or would like to see Flexter in action you can request a demo. You can find out more on our product page. The whole conversion process is fully automated. You don’t have to write a single line of code. Flexter fully automates the conversion of JSON to a relational model on Redshift.

This has other limitations which will also be covered in this post.Īnother option is Flexter, our data warehouse automation tool for JSON (and XML).

We can load whole JSON documents into Redshift and transform and query them with the JSON-SQL functions offered in Redshift.

We will cover them in detail in this post. There are some serious limitations to this approach. This requires us to pre-create the relational target data model and to manually map the JSON elements to the target table columns.

We can convert JSON to a relational model when loading the data to Redshift ( COPY JSON functions).

We have three options to load JSON data into Redshift.

Redshift offers limited support to work with JSON documents.

Json to redshift