2

From a REST API my Apex is receiving a list of JSON objects with previously unknown properties. To store those properties I want to dynamically enhance a Custom Object by adding suitable Custom Fields using the Metadata API.

I currently struggle with deciding which Schema.DisplayType to use for each of the resulting target SObjectFields. As default I could just store everything in a text field, but I also would like to detect and match at least those further types:

A method public Map<String, Schema.DisplayType> deduceType(String jsonArray) should produce from

[{
    name : "Peter",
    age : 34,
    married: true,
    partner: "Lucy",
    diabetesRisk: 0.4
},
..
{
    name : "Martha",
    age : 88,
    married: false,
    partner: null,
    diabetesRisk: 1
}]

this output

{
    name --> DisplayType.String,
    age --> DisplayType.Integer,
    married --> DisplayType.Boolean,
    diabetesRisk --> DisplayType.Percent
}

Ideas that came into my mind are:

  • Look at multiple value instances in the JSON (it's an array of same structure objects) to better cope with nulls or outlier values
  • Test a list of ordered Regexp pattern (from Text to more specific)
  • Maybe I can also use the parse() functions in Apex

Is there a better, a simpler or more robust approach? If so I would love to hear about it.

  • 1
    If the Json came in with all null property values, you'd be stuck. – cropredy May 20 at 20:51
  • 1
    JSONParser: nextToken or nextValue returns a JSONToken enum with type info – identigral May 20 at 21:00
1

I tried to parse the JSON and deduce its type using JSONParser.

I couldn't find any direct mapping between System.JSONToken and Schema.displayType, So I have created a local map.

Since there is no JSON Token for Date, DateTime, and Time I have leveraged JSONParser.getter functions(An alternate approach would be to use regex for URL and email types)

From format perspective Percentage, Currency and Number are ambiguous, so I have kept it under Number category.

Sample code:

private Map<String, Schema.DisplayType> parse(String input) {
    Map<String, Schema.DisplayType> result = new Map<String, Schema.DisplayType>();
    JSONParser parser = JSON.createParser(input);

    while (parser.nextToken() != null) {  
        if(parser.getCurrentToken() == JSONToken.FIELD_NAME) {
            String name = parser.getCurrentName();
            parser.nextValue();
            result.put(name, type(parser));
        }
    }

    return result;
}


private Schema.DisplayType type(JSONParser parser) {
    Schema.DisplayType result;

    Map<System.JSONToken, Schema.DisplayType> displayType = new Map<System.JSONToken, Schema.DisplayType> {
        JSONToken.VALUE_FALSE => Schema.DisplayType.Boolean,
        JSONToken.VALUE_TRUE => Schema.DisplayType.Boolean,
        JSONToken.VALUE_NULL => Schema.DisplayType.String,
        JSONToken.VALUE_STRING => Schema.DisplayType.String,
        JSONToken.VALUE_NUMBER_INT => Schema.DisplayType.Integer,
        JSONToken.VALUE_NUMBER_FLOAT => Schema.DisplayType.Double
    };

    JSONToken type = parser.getCurrentToken();

    if(type == JSONToken.VALUE_STRING && isTime(parser)) {
        result = Schema.DisplayType.Time;
    }
    else if(type == JSONToken.VALUE_STRING && isDate(parser)) {
        result = Schema.DisplayType.Date;
    }
    else if(type == JSONToken.VALUE_STRING && isDateTime(parser)) {
        result = Schema.DisplayType.DateTime;
    }
    else {
        result = displayType.get(type);
    }

    return result;
}


private Boolean isDate(JSONParser parser) {
    try {
        parser.getDateValue();
    }
    catch(Exception ex) {
        return false;
    }

    return true;
}


private Boolean isDateTime(JSONParser parser) {
    try {
        parser.getDatetimeValue();
    }
    catch(Exception ex) {
        return false;
    }

    return true;
}


private Boolean isTime(JSONParser parser) {
    try {
        parser.getTimeValue();
    }
    catch(Exception ex) {
        return false;
    }

    return true;
}


String input = '[ { "name": "Peter", "age": 34, "married": true, "partner": "Lucy", "diabetesRisk": 0.4, "testDate": "2020-05-21", "testDateTime": "2020-05-21T13:01:23", "testTime": "18:05" } ]';
System.debug(parse(input));

Output:

{
    "age": "INTEGER",
    "diabetesRisk": "DOUBLE",
    "married": "BOOLEAN",
    "name": "STRING",
    "partner": "STRING",
    "testDate": "DATE",
    "testDateTime": "DATETIME",
    "testTime": "TIME"
}

It would not be a robust solution to rely on code to deduce datatypes from sample JSON data. Admin should modify data types wherever necessary(Determining type for Text, Text Area, and Rich Text can be ugly and still not be 100% robust).

| improve this answer | |
  • How do you cope with null value and with more specific types like dates percentages etc – Robert Sösemann May 21 at 12:44
  • For safer side, the parser function treats null as a text field. I have updated my solution to handle the data type. Deducing type for Percentage, Currency, RichText Area field is hard just from the format and it should be left for admins to modify if necessary – Anmol Kumar May 21 at 13:55
1

I'm not going to write a full example, but I think it would be safe to go with a JSON.deserializeUntyped, then decode the keys to figure out what they should be. This gives you the types Boolean, Decimal, and Text, and from there, you need patterns (regular expressions) to detect date, date/time, picklist, and ID values. Considering that json2apex (not my app, obviously) is a thing, you're not the first to have done it, and it seems reasonable to do with fair accuracy.

| improve this answer | |
  • What do you mean by decode? Try to cast? – Robert Sösemann May 20 at 23:02
  • 1
    @RobertSösemann I would envision the code would check == null, instanceOf Boolean, instanceOf Decimal, instanceOf Id, followed by casting to a String and regex checking for date and datetime values, otherwise assume a plain string. Repeat for all the entries, recursively if necessary. – sfdcfox May 20 at 23:07
  • Clearly this doesn't address things like distinguishing text lengths, text vs email addresses, URLs etc. plus numbers vs currency and numeric precision and significant figures etc. though that can be applied based on extra analysis. Of course such analysis would only be as good as the input data set. – Phil W May 21 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.