Skip to main content
July 20, 2022
Question

Storing Data to Appian db after processing json from GET api

  • July 20, 2022
  • 5 replies
  • 0 views

Hi,

I am trying to save data in appian db which I am getting from an api call. API is : https://run.mocky.io/v3/ac936aeb-6ae5-46e1-ab38-b953ab3ac2a5 [Below is the body that I am getting after running API call in Appian]

[{
		"FXRateMicroservicesSuite1": [{
				"FXR": [{
						"FXRateGET": ["test step 1", "test step 2"],
						"link": "FXRateGET.com"
					},
					{
						"FXRateMiscellaneous": ["test step 1", "test step 2"],
						"link": "FXRateMiscellaneous.com"
					},
					{
						"FXRatePolicyServer": ["test step 1", "test step 2"],
						"link": "FXRatePolicyServer.com"
					},
					{
						"FXRatePOST_DELETE": ["test step 1", "test step 2"],
						"link": "FXRatePOST_DELETE.com"
					}
				]
			},
			{
				"Script2": [{
					"Test cases": ["test step 1", "test step 2"],
					"link": "Script2.com"
				}]
			}
		]
	},

	{
		"FXRateMicroservicesSuite2": [{
				"FXR2": [{
						"FXRateGET2": ["test step 1", "test step 2"],
						"link": "FXRateGET2.com"
					},
					{
						"FXRateMiscellaneous": ["test step 1", "test step 2"],
						"link": "FXRateMiscellaneous.com"
					},
					{
						"FXRatePolicyServer": ["test step 1", "test step 2"],
						"link": "FXRatePolicyServer.com"
					},
					{
						"FXRatePOST_DELETE": ["test step 1", "test step 2"],
						"link": "FXRatePOST_DELETE.com"
					}
				]
			},
			{
				"Script2_2": [{
					"Test cases": ["test step 1", "test step 2"],
					"link": "Script2_2.com"
				}]
			}
		]
	}
]

Below given is how I want to store it in database:

SuiteName Scripts SuiteID
FXRateMicroservicesSuite1 [FXR,Script2] 1
FXRateMicroservicesSuite2 [FXR2,Scrip2_2] 2

So basically, FXRateMicroservicesSuite1 object is an array of scripts which is further an array of steps as you can see in the response of the above api. However I only want to store the suiteName and then Scripts as an array in the Scripts column.

For that, I am basically writing an expression by which I can achieve the above target. However I am not getting the expected output.

Can you guyz pls help me in writing a Expression rule so that I can store it in a process variable and then can store it in database ? Below is the CDT that I am trying to save the output of the expression rule so that I can save it in appian db.

Below is the process model for the same:

 

5 replies

harshitb6843
July 20, 2022

Can you please add the raw output that you are getting from your integration object. For us to help to change the expression, we need to know what data we need to transform/manipulate. 
Please attach the code as a screenshot or even better option will be to embed the output as code here 

July 20, 2022

Thanks for replying.

I have added the body part that I am getting after doing an API call in Appian

stefanhelzle0001
Brainy
July 20, 2022

Please adapt below code snippet to your needs. instead of a!map, call your CDT data type.

a!forEach(
  items: a!fromJson(ri!json),
  expression: a!localVariables(
    local!field: a!keys(fv!item)[1],
    a!map(
      suitename: local!field,
      scripts: a!forEach(
        items: fv!item[local!field],
        expression: a!keys(cast(type!Map, fv!item))[1]
      )
    )
  )
)

A few tips:

- with() is deprecated

- add a field "id" to your CDT and make it an auto generated primary key

July 20, 2022

Thanks, It helped me a lot.

Can you pls modify your script for the cdt as shown in the post ?

Also If you see in the CDT, do I need to uncheck the array checkbox for scripts field ?

stefanhelzle0001
Brainy
July 20, 2022

Please have a look at the documentation for how to create a data structure from a CDT.

https://docs.appian.com/suite/help/22.2/Expressions.html#constructing-data-type-values

Making a basic data type field a list is against the best practices.