Pulling Row Data into JSON Content Body

[wisemanleo]

I have a PUT request that works well for a single field, single record update. The jsonContent body looks like this:

** **{ "recordtype":"customrecord_ticket_record",** **"id":"201",** **"values":{"custrecord_priority": "3" } }** **

This is fed into the first node, Table Creator, which looks like this:
URL Method jsonContent
(scripturl) PUT thecodeabove

I have another node, Table Creator, that has two columns that look like this:
ID custrecord_priority
201 3
202 3
203 4
204 3
205 3
206 3
207 4

How do I get that array into the jsonContent code? My thought is to follow Table Reader with Table to JSON:

Which gives me this:

But it is missing the key, non-array components of the original jsonContent code in the first part of this post.

How do I mesh the two?

[wisemanleo]

So I think I need to use JSON Transformer, but have trialed and error this a few times unable to structure the jsonContent correctly. I’m sure I can use basic string manipulation but thinking Transformer node is the more elegant way to go.

[sauberns]

Have you had a look at the “Columns to JSON” node?

Columns to JSON

I’ve found that 2 or 3 of those nodes in sequence can build multi-level nested JSON data, but on a row by row basis. Not the whole table at once.

(the other)
Simon

[wisemanleo]

I haven’t! That got me a lot closer.

This is what my payload now looks like - needs a few tweaks (bolded areas are incorrect):

{
"values (#1)" : {
“recordtype”:“customrecord_ticket_record” : {
“id” : [ 155, 159, 162, 163, 165, 167, 169, 177, 188, 189, 191, 194, 195, 196, 197, 198, 199, 201 ]
},
“values” : {
“custrecord_priority” : [ 3, 3, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4 ]
}
}
}

Target Structure (array not shown as I haven’t a successful example yet)
{ “recordtype”:“customrecord_ticket_record”, “id”:“201”, “values”:{ “custrecord_priority”: “3” } }

Current Workflow:

[wisemanleo]

I got a bit further with this workflow and result:

Result:
{
“object” : [ {
“recordtype” : “customrecord__ticket_record”
} ],
“recordID” : {
“id” : [ 155, 159, 162, 163, 165, 167, 169, 177, 188, 189, 191, 194, 195, 196, 197, 198, 199, 201 ]
},
“values” : {
“custrecord_priority” : [ 3, 3, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4 ]
}
}

How do I get Columns to JSON node to not add the column name “object” into the payload here? If I can just remove that!

[qqilihq]

Hi wisemanleo,

I’m assuming a structure as follows:

{
  "recordtype" : "customrecord_ticket_record",
  "id" : [ 201, 202, 203, 204, 205, 206, 207 ],
  "values" : {
    "custrecord_priority" : [ 3, 3, 4, 3, 3, 3, 4 ]
  }
}

This you can build for example as follows:

I created the two arrays using the GroupBy node, then create the values object using Columns to JSON, and finally the result JSON using Table to JSON.

I’ll share my workflow on my NodePit space with you. Hope it helps!

pulling-row-data-into-json-content-body-42505

–Philipp

[wisemanleo]

@qqilihq to the rescue again! This worked like a charm! Thank you very much!