Add new column with a JSON value

[joliveiramrs]

Hi,

Is it possible to create a new column with a value of several columns but in a json format?

Is there any function available to do this in the transformation area?

Regards

[Heinvandenheuvel · Accepted solution]

@joliveiramrs wrote:

>> Is it possible to create a new column with a value of several columns but in a json format?

Yes

>> Is there any function available to do this in the transformation area?

No, not as such. But you are free to use ADD COLUMN and (carefully) type in a bunch of quoted strings glued together with data values.  Such expression might look like:

'{
	"json_column": {
		"Comment": "' || $Comment || '",
		"id": "' || $id || '",
		"SourceDB": "' || $SourceDB || '",
		"SourceDT": "' || $SourceDT || '"
	}
}
'
--- no whitespace alternative --
'{"json_column": {"Comment": "' || $Comment || '","id": "' || $id || '","SourceDB": "' || $SourceDB || '","SourceDT": "' || $SourceDT || '"}}'

Note - all the whitespace is of course optional. Those newlines, tabs and spaces are not required for json, just for us humanoids.

Now that's admittedly going to be tedious. I'd urge you to use a program or script in your favorite language to 'generate' such expression as opposed to type it in. Yeah making the program may take an hour versus typing it in taking a few minutes, but you know it will be 'correct' and it van easily be redone when something new is desired (upcase, quote-handling, date-handling)

For example the above is generate using perl with a command

perl json.pl Comment id SourceDB SourceDT > x.json

That program is:

print qq('{\n\t"json_column": {\n);
while ($f = shift) {
	print qq(,\n) if $i++;
	print qq(\t\t"$f": "' || \$$f || '");
}
print qq(\n\t}\n}\n');
print STDERR "$i columns processed.\n";

-- no whitespace formating --

print qq('{"json_column": {);
while ($f = shift) {
	print qq(,) if $i++;
	print qq("$f": "' || \$$f || '");
}
print qq(}}'\n);
print STDERR "$i columns processed.\n";

You'll need to test a lot. Depending on the column datatypes some enhanced formatting may be needed (dates, strings with quotes, numeric precisions). Admittedly that may well make it impossible in which case you'll have to look for a solution (trigger) on the target.

hth,

Hein