3

I have joined Countries to Locations in the HR Sample database in OracleXE.

I am then using a tMap to generate a nested JSON document.

enter image description here

it works, but for some reason null values in the Location are coming through as arrays in the final output in the console (have also tried MongoDB).

enter image description here

smackenzie
  • 2,880
  • 7
  • 46
  • 99

3 Answers3

2

I believe the elegant (and short) solution would be the following.

Talend docs state that:

When configuring a JSON tree, the default type of an element is string. If an element is not of type string, you need to add an attribute for the element to set its type.

So, to the object receiving a null value, you should add an attribute named class and set its static value to object.

Pic: JSON Tree Configuration

And voilà!

PIC: "Complemento":null

Gustavo Regal
  • 21
  • 2
1

Because tWriteJSONField generates an xml, then converts it to JSON using json-lib. Your null value will be converted to an empty xml node <STATE_PROVINCE/>, and json-lib, having no context of this node, will assume it is a parent node with no children, instead of an empty text (null notion is already far at this point).

Here is what happens in short:

package test.json;

public class JSONTest {

    public static void main(String[] args) {
        net.sf.json.xml.XMLSerializer s = new net.sf.json.xml.XMLSerializer();
        s.clearNamespaces();
        s.setSkipNamespaces(true);
        s.setForceTopLevelObject(true);
        net.sf.json.JSON json = s.read("<?xml version=\"1.0\" encoding=\"ISO-8859-15\"?>" +
                "<org>" +
                "<STATE_PROVINCE/>" +
                "</org>"
        );
        System.out.println(json.toString());
    }

}

Result:

{"org":{"STATE_PROVINCE":[]}}

A dirty solution is to use attributes instead of nodes in your tWriteJSONField, but it will prefix your properties with @. So after this component you put a tReplace, search "\"@", replace with "\"", uncheck whole word, check global expression. Your final JSON will have no property if null.

kdev
  • 705
  • 7
  • 11
  • Or, because Talend is brain-dead. I understand the technical reason: but no reasonable software package would be designed with that behavior. I've seen explanations that this is "expected" behavior. We have those little search and replace java snippets throughout our jobs. – fool4jesus Jul 26 '17 at 06:54
1

Thanks to https://www.talendforge.org/forum/viewtopic.php?id=27791

Insert a tJavaRow with the following code right after your tWriteJsonField:

output_row.output = input_row.output.replaceAll(",?\"[a-zA-Z_0-9]*\":\\[\\]", "");
Jbalberge
  • 163
  • 7