JSON flattening in the Visual SQL Generator

Introduction

The Visual SQL Generator is an alternative to writing SQL by hand.

Analysts simply select columns from a table in a menu, which will then, not only, generate SQL in real-time, but also provide a preview of the output table - particularly useful for when data needs to be un-nested from JSON objects.

Limitations

Snowflake Only
Currently the Visual SQL Generator works with Snowflake, but Redshift will be supported in due course.

SQL Editing Not Supported
It is currently not possible to edit the SQL within the Visual SQL Generator. Please generate the SQL then copy it into the console and make any manual adjustments there.

Joins Not Supported
It is currently only possible to select a single input table. Joins and Unions between multiple input tables are not currently supported in the SQL Generator.

Accessing the VSG

To access the Visual SQL Generator, go to the Transforms section of the Kleene app and choose any of your Transform Groups, then click the plus icon in the top right to create a new transform. You will be presented with two options SQL Editor and SQL Generator.

🚧

Load Time

If you have a lot of tables within your database it may take several seconds for the database structure to load. If there are many columns in the selected input table or the data is heavily nested it may take several seconds to load.

After selecting SQL Generator choose the relevant input table from the database structure. It will then load the column structure of the data as shown in the image below.

How to generate SQL

Start clicking on the checkboxes of the columns you wish to include in the output table. JSON objects and Arrays can be expanded to display the nested keys. If the key of a JSON object is chosen it will be un-nested and displayed. If the key of an Array is chosen a LATERAL FLATTEN command is included, the granularity of the table will be increased and the chosen key will be displayed. All keys from a JSON object or Array can be included by selecting the checkbox of the JSON object. If you wish to include the JSON object or an Array as a string please click the ellipsis and choose the option Include as a stringified JSON object.

❗️

Selecting Keys from Arrays

Selecting keys from within an Array increases the level of granularity of the output table to the level of granularity of the array object. If the input table has 100 rows and row contains an array with 5 objects and one or more keys from the array is selected then the output table will have 500 rows.

Using the generated SQL

Once your SQL has been generated you can choose to either COPY SQL this will copy all the SQL to your clipboard so it can be pasted wherever you like or INSERT SQL TO THE TRANSFORM this will take you to a new Transform with the generated SQL populated.

Feature Requests

To suggest a new feature go into Resource Center and add a suggestion.