Group By a field with a DataRaptor Transform

data-raptorjsonomniscriptomnistudio

I'm attempting to group Quote Lines in the JSON output of a DataRaptor Extract with a Transform and am unsure if this is possible without an Integration Procedure.

I currently have Quote Lines outputting as such:

    "QuoteLine": [
    {
      "ProductSiteName": "BO1",
      "ProductId": "Prod1",
      "Quantity": 1,
      "Number": 1,
      "Quote": "a5LQL0000001k8r2AA"
    },
    {
      "ProductSiteName: "S0-1",
      "ProductId": "Prod2",
      "Quantity": 15,
      "Number": 2,
      "Quote": "a5LQL0000001k8r2AA"
    } ...

and would like to transform that to group based on the ProductSiteName as such:

    "Site" : [
    {
      "ProductSiteName": "BO1"
      "QuoteLine": [
      {
        "ProductId": "Prod1",
        "Quantity": 1,
        "Number": 1,
        "Quote": "a5LQL0000001k8r2AA"
      }
    },
    {
      "ProductSiteName": "S0-1",
      "QuoteLine": [
      {
        "ProductId": "Prod2",
        "Quantity": 15,
        "Number": 2,
        "Quote": "a5LQL0000001k8r2AA"
      }
    } ...

I'm relatively new to OmniScripts and feel like I might be already incorrect at the Extract level.

Best Answer

You will need to use an Integration Procedure to accomplish the required transformation.

High level steps are:

Extract all Quote Lines
Use a Dataraptor Transform to extract unique values for ProductSiteName.

This can be done by passing in the Quoteline list to the DR Transform, using the formula LISTMERGE("ProductSiteName",LIST(%QuoteLine%)) to "extract" the unique ProductSiteName values and then let the DR Transform return a list of all the unique ProductSiteName values:

The output will look like:

[
  {
    "ProductSiteName": "BO1"
  },
  {
    "ProductSiteName": "S0-1"
  }
]

Loop over these values using a Loop Block:
1. Create another Integration Procedure that accepts a ProductSiteName value as well as the list of Quote Lines
2. Use a List Action to Extract all Quote Lines for the given ProductSiteName
3. Use the Response Action to return the JSON for a given ProductSiteName in the required format:

{
  "ProductSiteName": "S0-1",
  "QuoteLine": [
  {
    "ProductId": "Prod2",
    "Quantity": 15,
    "Number": 2,
    "Quote": "a5LQL0000001k8r2AA"
  }
}

In the Parent Integration Procedure build the final output from the Loop Block and return it using a Response Action.

Good luck!

Related Solutions

[SalesForce] Transform JSON with lists inside into form data in Apex

You'll need to pre-create the data structure in an apex class to deserialize into that.

There's a teriffic online app, created by metadaddy and superfell, that can generate the class for you from the JSON string: http://json2apex.herokuapp.com

In your case, it will generate this APEX:

public class Vendor {

public String vendorcode;
public String startDate;
public List<Client> client;

public class Client {
    public String name;
}


public static Vendor parse(String json) {
    return (Vendor) System.JSON.deserialize(json, Vendor.class);
}

static testMethod void testParse() {
    String json = '[{\"vendorcode\":\"4444\",\"startDate\":\"2014-03-02\",\"client\":[{\"name\":\"John\"}]}]';
    Vendor obj = parse(json);
    System.assert(obj != null);
}
}

[SalesForce] Most efficient way to transform AggregateResults to String, Object Maps

Suggested solution:

Convert a list of aggregate results to a Json string and deserialize the string as an object.

    List<Map<String, Object>> getAggregateResultAsMap(List<AggregateResult> aggrs)
    {
        String js = Json.serialize(aggrs); // AggregateResult as Map
        List<Map<String, Object>> result = new List<Map<String, Object>>(); // result Object
        Object resultObject = Json.deserializeUntyped(js); // deserialized without type
        for (Object o : (List<Object>) resultObject)
        {
            result.add((Map<String, Object>) o);
        }
        return result;
    }

Best Answer

Related Solutions

[SalesForce] Transform JSON with lists inside into form data in Apex

[SalesForce] Most efficient way to transform AggregateResults to String, Object Maps

Related Topic