5

How to transform JSON response retrieved from external system to meaningful data (key/value pairs) in ESQL?

Retrieved JSON:

{
    "data": [
        {
            "name": "application.info.header",
            "value": "headerValue"
        },
        {
            "name": "entity.statistics.name.fullName",
            "value": "fullNameValue"
        },
        {
            "name": "application.info.matter",
            "value": "matterValue"
        },
        {
            "name": "entity.statistics.skill",
            "value": "skillValue"
        }
    ]
}

where,

  • name ~ hierarchy of JSON (last attribute being the key)

  • value ~ value against the key

Expected JSON:

{
    "data": {
        "application": {
            "info": {
                "header": "headerValue",
                "matter": "matterValue"
            }
        },
        "entity": {
            "statistics": {
                "name": {
                    "fullName": "fullNameValue"
                },
                "skill": "skillValue"
            }
        }
    }
}

Needless to say this can be easily achieved in Java through Split method - I'm looking for a suitable method in ESQL.

Current ESQL Module:

CREATE COMPUTE MODULE getDetails_prepareResponse
    CREATE FUNCTION Main() RETURNS BOOLEAN
    BEGIN
        DECLARE data REFERENCE TO InputRoot.JSON.Data.data.Item[1];
        SET OutputRoot.JSON.Data = InputRoot.JSON.Data;
        SET OutputRoot.JSON.Data.data = NULL;

        WHILE LASTMOVE(data) DO
            DECLARE keyA CHARACTER SUBSTRING(data.name BEFORE '.');
            DECLARE name CHARACTER SUBSTRING(data.name AFTER '.');
            DECLARE keyB CHARACTER SUBSTRING(name BEFORE '.');
            DECLARE key CHARACTER SUBSTRING(name AFTER '.');
            CREATE LASTCHILD OF OutputRoot.JSON.Data.data.{EVAL('keyA')}.{EVAL('keyB')}
            NAME key VALUE data.value;
            MOVE data NEXTSIBLING;
        END WHILE;

        RETURN TRUE;
    END;
END MODULE;

This is currently handled through SUBSTRING method in ESQL (for 3 levels only), but now the JSON levels are dynamic (no limit to key/value pairs) as per requirements.

Muhammad Faizan Uddin
  • 1,339
  • 12
  • 29
  • Are you banned from using JavaCompute Nodes? If not use a JavaCompute Node. – TJA Apr 20 '19 at 00:56
  • @TJA - this is clearly mentioned in the question "Needless to say this can be easily achieved in Java through Split method - I'm looking for a suitable method in ESQL" – Muhammad Faizan Uddin Apr 28 '19 at 06:24
  • Muhammad the reason I asked about Java was to try and understand how strong a prescription against Java you were up against. Some of the places I've worked at it's an absolute Never Ever, at others you are allowed to request an architectural exception. In some extreme and special cases I've seen performance improve from 500 seconds using ESQL to just over 2 seconds using Java, although in your case it's unlikely to be that bad. – TJA Apr 28 '19 at 10:01
  • @TJA I don't know why your initial on-topic comments were removed, exactly. Obviously they were flagged by someone as "no longer needed", and a moderator chose to remove them. I have restored the on-topic comments, but deleted the ones you left complaining about how your answer was downvoted. Please do not leave comments complaining about downvotes; those don't help anyone, and the downvoters will never see them. Regarding comments in general, they are regularly deleted by moderators. They are just like temporary sticky notes requesting clarification or suggesting improvements. – Cody Gray - on strike May 09 '19 at 17:59
  • @CodyGray thank you. Interesting that comments are temporary I haven't seen them vanish before. – TJA May 10 '19 at 00:26

1 Answers1

2

You could implement your own procedure to split a string. Take a look at this answer for an example.

ESQL for splitting a string into mulitple values

The method splits S on Delim into an array in Env (Environment.Split.Array[]) and removes Environment.Split before refilling it.

Muhammad Faizan Uddin
  • 1,339
  • 12
  • 29
Adam Rice
  • 880
  • 8
  • 20