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?

You can use the .toObject method of the array

image

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?

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”
}
}

Looks like all you need is a mapper snap.

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 :smile:

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’)) :smile:

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

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’))

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

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