Forum Discussion

peter's avatar
peter
New Contributor III
6 years ago

JSON formating String

I have a pipeline that uses the ServiceNow API Snap to retrieve data about ServiceNow Tasks. The Snap returns the data and I pass through a mapper.

However the ServeirNow form that I am getting data from contains ServiceNow custom variables and these come back from the API as a string and I am struggling to get these into a JSON format.

This is the data after the mapper

{
“number”:“RITM123456”
“etc:123”
“variables”:“Auto ID = 123\n Autoversion ID = 345\n Autoname = SQL stuff\n etc…”
}

I have tried using split and replace, but I cannot get the data into the right format.

How do I get everything into a JSON format?

10 Replies

  • peter's avatar
    peter
    New Contributor III
    6 years ago

    Thanks very much.

    I dont understand how the toObject works.

    When I use the code I get an error for one record;
    Cannot access element 1 of ‘x.split(’=‘)’, which has only 1 elements,

    The issue seems to be with a description field which has line returns, i.e.

    Description = Great things\r\nare a foot

    The code is expect a new field after the \n hence do I need to replace the \r\n with a space?

    Is there a good way to do this?

    • alchemiz's avatar
      alchemiz
      Contributor III
      6 years ago

      On top of my head you can replaceAll the ‘\r\n’ with a keyword e.g. ‘[CRLF]’ this will be a place holder for you to revert it back when doing the toObject e.g.

      $variables.replaceAll(‘\r\n’,‘[CRLF]’).split(‘\n’)…toObject((x,y)=>…], (x,y)=>x.split(‘=’)[1].replaceAll(‘[CRLF]’,‘\r\n’)) 😄

  • peter's avatar
    peter
    New Contributor III
    6 years ago

    I am nearly there with my pipeline however I now have “nested” JSON, that is the first set of JSON returned by the API and the variables. Is there a way to flaten so that I have a single level of JSON?

    I now have:
    {
    “number”:“RITM123456”,
    “etc:123”,
    variables : {
    “Auto ID : 123”
    “Autoversion ID : 345”
    “Autoname : SQL stuff”
    }
    }

    And I would like

    {
    “number”:“RITM123456”,
    “etc:123”,
    “Auto ID : 123”
    “Autoversion ID : 345”
    “Autoname : SQL stuff”
    }
    }

    • alchemiz's avatar
      alchemiz
      Contributor III
      6 years ago

      Or in the scenario wherein the objects inside the variables object is dynamic you can always use the merge then filter variables to destroy the variables object 😄

  • peter's avatar
    peter
    New Contributor III
    6 years ago

    Thanks for all your help.

    I now have nearly what I am after. but how do I remove the white space and the beginning and end of the field names after the merge, i.e.

    in the mapper I see;

    $[’ Additional Comments ‘]
    $[’ Risk Comments ‘]
    $[’ Automation author ']

    and I would like to see
    $Additional Comments or $Additional_Comments
    $Risk Comments or $Risk_Comments
    $Automation author or $Automation_author

    Thanks
    Peter

    • alchemiz's avatar
      alchemiz
      Contributor III
      6 years ago

      first trim the key then do a replace of spaces with underscore

      $obj = " word of the day = my very energetic mother just serve us nine pie[CRLF][CRLF]HeHeheHeHehEhe "

      sl.ensureArray($obj).toObject((x,y)=> x.split(‘=’)[0].trim().replace(/\s/g,‘_’), (x,y)=> x.split(‘=’)[1].replace(/[CRLF]/g,‘\r\n’))

  • peter's avatar
    peter
    New Contributor III
    6 years ago

    I dont quite following the link above?

    What is the sl in sl.ensure Array?

    I switched $obj for my variable name.

    I get the following error when I try, Could not compile expression: sl.ensureArray($variables).toO … (Reason: Invalid token: ‘’’ for expression: sl.ensureArray($variables).toO

    • alchemiz's avatar
      alchemiz
      Contributor III
      6 years ago

      Hi Peter,

      I just mocked the $obj, the sl.ensureArray is to cast $obj as array so that I can invoke toObject() function

      So, to answer your question about the white-spaces… the toObject function is basically creating key/value pair… the 1st callback generates the key and the 2nd callback generates the value, all you need to do is use the script that defines the key and update what you have

      toObject((x,y)=> x.split(’=’)[0].trim().replace(/\s/g,’_’), (x,y)=> x.split(’=’)[1].replace(/[CRLF]/g,’\r\n’))

      ~EmEm