Create Array of JSON Objects via tRESTCLient for Marketo Custom Object

One Star

Create Array of JSON Objects via tRESTCLient for Marketo Custom Object

Here's the data flow I'm trying to accomplish,

From an input file, in this example CSV (could also be from DB), I would push the records to a Marketo custom object for either insert / update operation.
I am using tXMLMap to create the JSON string. The payload will be passed to the tRESTClient component so it can issue operations to the Marketo web service.
Here's the mapping on the XML Map,

Here's the output when converted to JSON format,
{
    "input": {
        "email_address": "rollyminianotesttalend@yahoo.com",
        "customer_id": 12345,
        "lead_brand_id": 12345
    }
}

The issue with Marketo is that, even though you will only pass 1 record set, it needs the data set to be an array. The expected output should be,
{
    "input":
}

How can I format the output of tXMLMap to create array of JSON objects?
Please help.
Thanks,
Rolly

Re: Create Array of JSON Objects via tRESTCLient for Marketo Custom Object

I just went through this same exact issue when trying to create leads.  I found a great solution though and a solution I will probably reuse quite a bit.  Please excuse me if I can't remember all of the details as I don't have me work computer.  Also some of these steps may not be needed for you.  Let me know if you need more help.  I can take screenshots when I am at my work computer.
Here is what I did.
tfilelist to loop through directory-->Flat File with new leads-->tbufferinput--->tbufferoutput --->Tmap -->writetoJSON-->titeratetoflow-->TREST.
REasoning:
The flat file contains the data which is picked up in the filelist.
User the buffer in case there is only one record in the set.  May not be a big deal for you, but out leads are being accessed without any standards and may come in batches of 1-300.  This component is optional if you aren't scared of this phenomenon.
Tmap from source schema to write to JSON schema.  The write to JSON component should have one output being the payload.  Use the configure the JSON tree to accept the fields as subfields.  Map the rows under the root element which can be named any arbitrary thing.  Make what could be considered a primary key (in my case email address) as the loop element.
Iterate to flow will scoop up the payload with a key.
For TREST pass your authentication credentials through in the header.  In the body use this (for create lead anyway):
"{
   \"action\":\"createOnly\",
   \"lookupField\":\"email\",
   \"partitionName\":\"Swyft\",
   \"input\":"
    +((String)globalMap.get("row3.Payload"))+"
}"
Five Stars

Re: Create Array of JSON Objects via tRESTCLient for Marketo Custom Object

Hi,
I'm so happy because I just found how to force array after months of search and use of regex to bypass this issue... and the solution is so simple that I don't understand why there are so many topics without a clear answer from the Talend team.

Anyway, you just have to use attributes on the json mapping. I discovered the type attribute that can be use to force numbers (and to remove quotes by doing so), but it didn't work with the array type! However, the class attribute did work!
All you have to do is to define a parent in your json description and give it class attribute with array value.
Here is an example of json mapping (note the class attribute):
https://www.talendforge.org/forum/img/members/36736/mini_TOS_json.jpg.jpg
And here is the result (note the bracket within the materials and also note that the "material" doesn't appear):
{
    "root" :
    {
        "brand" : "CCH",
        "model" :
        {
            "code" : "3749017",
            "color" : "969"
        },
        "materials" :
    }
}
{
    "root" :
    {
        "brand" : "CCH",
        "model" :
        {
            "code" : "3749017",
            "color" : "970"
        },
        "materials" :
    }
}

Regards!
Seventeen Stars

Re: Create Array of JSON Objects via tRESTCLient for Marketo Custom Object

There is for sure enough room to improve the documentation!
One Star

Re: Create Array of JSON Objects via tRESTCLient for Marketo Custom Object

The array approach works fine but there is still an issue in the below case if the loop element has a single row .
The twriteJsonField componenet is configured as 
rootTag

 id ACCTID loop element 

 period PERIOD
The same works fine if the loop element has more than 1 record in the group and the json output does produce as below
{ id : , period : "Yearly"}
But if say the loop group has one row , the output is 
{id : 1 ,period : "Yearly" }
Is there any solution to the same Smiley Happy
Thanks  
Gaurav