toJson

Currently, we have a columnsToJson operator will converts the columns to json object where the column names become key and values in the cell become values in the json. The drawback of this operator is that if you want to provide a constant field, e.g. version which should be replicated in each of the fields, you have to create a separate column in order to be able to see it in the output. Another limitation, if you want to create a nested object you have to call this operator twice, but the problem is when you call it second time it will treat the output of the first call as a string and so it might not be a json object. There are other limitations but I think is enough to create a new operator.

New operator toJson will address the limitation of the columnsToJson operator where you can use it in three different ways (see examples for better understanding):

provide a template of a json in the arg, and the bindings (map) to replace the values with the values from the table
provide just the binding (map) and leave the filename arg empty, it will create a json from binding
provide a filename of json in the system and a binding, it will replace the values in the template with the values from the table
In addition, we provided an extension where you can provide "casting" to the values, currently we support only "int, double, string, json" types (see examples in the TEST section).
Note: casting will convert column to specified type
"$columnX$ as int" will convert columnX to int
"$columnX$ as json" will convert columnX to JSON, if you don't use as json statement and columnX is a JSON field, it will treat them as a string (you will notice that, you can't expand string in the UI as you can do it with JSON).

Operator Usage in Easy Mode

1. Click + on the parent node.
2. Enter the To JSON operator in the search field and select the operator from the Results to open the operator form.
3. In the Input Table drop-down, enter or select a table to apply the operator.
4. In the Output Column Name, enter the name of the field.
5. In the Template JSON, enter the JSON template or filename that contains the JSON template.
6. In the Bindings, enter the variable name in the input field to map JSON and the table.
7. Click Run to view the result.
8. Click Save to add the operator to the playbook.
9. Click Cancel to discard the operator form.

Usage Details

toJson(table:TableReference, outputColumn:String,  jsonfileOrTemplate:String,  binding:Map[String,String])

Parameters
table: input table that contains the information to create JSON object.
outputColum: output column name.
jsonfileOrTemplate: file name of JSON template that is stored on the backend or you can pass the template here (see examples for more info).
binding: Map of a variable name in the template to a value or column or const (see examples for more info).

📘

Note

Value in binding can be a constant value, column name, or casting value.

Examples:

• constant value: {"Version":"1.0.1"}, this will pass constant value "1.0.1" where Version is used
• column name: {"Id": "$lhub_id$"} this will pass values in lhub_id column where Id is used
• casting value: {"Description": "$details$ as json"}, details columns values will be cast to json and then pass to Description.

Examples

Input
inputTable

Example 1

toJson(input, "resultJson", "", {"A":"$user$", "B": "$path$"})

Output

• Explanation *

When jsonfileOrTemplate is empty string (e.g. "" in the args), this command will create JSON from Map parameter = {"A":"$user$", "B": "$path$"}, where "A" and "B" are keys, and "$user$", "$path$" are the values of Map parameter, since value part contains "$.*$" pattern, string between dollar signs is assumed to be a column name, if the column name does not exist in the table it will throw an error.

Example 2 Passing template as an argument
use output of Example 1 (A table)

A = toJson(input, "res", 
           "{\"id\":\"#id#\", \"json\":\"#jsonObject#\"}", 
           {"#id#":"$lhub_id$ as int", "#jsonObject#": "$resultJson$ as json"})

"$lhub_id as int" mean cast values in lhub_id column to int
"$resultJson$ as json means cast resultJson column to json object
"#id#" is identifier, it can be anything e.g. "&tmp&", "blabla", "x$y$z" etc, but make sure that binding key is unique and does not exist in anywhere in the values of the JSON template that you are going to replace, and remember the variable name, because it will be replaced by the values in the column that is provided in the binding, in this examples #id# in the template will be replaced with cast(lhub_id as int) values, because it is provided in the binding, if we dont find it will be left as it is, e.g. output will be "#id#" for "id" column
Here is the output of the LQL command.

Example 3 Passing template as a file
Save template as json file (testTemplate.json = {"id":"#id#", "json":"#jsonObject#"}), and store it inside /opt/docker/data/shared

A = toJson(input, "res", "testTemplate.json", 
{"#id#":"$lhub_id$ as int", "#jsonObject#": "$resultJson$ as json"})

it should give same output as Example 2 output.
As you can see, LQL command looks cleaner when we store template JSON in a file, and we provide only bindings to replace it in the LQL command.