Capturing a value from a json response of an FSM outbound call

Hi @Miraj,

Did you find a solution for this at all.

I think I have a similar issue where by the outbound message is using the JSON Connector, and the response is in JSON format.

The result contains a token that I want to capture and store in a table for later use.

I have several other processes where i’m capturing a XML response and mapping the data back to use again. But with JSON I am hitting a dead end.

Cheers Ady

cc @Saranga Amaraweera 

I am not able to view the post you copied above. Would that help me at all?

@AdrianEgley 

Hi Adrian,

I’m afraid right now there’s no way (out of the box) to parse JSON responses within the FSM solution. After trying various approaches as shown above, I had to resort to asking the resource owners to send me an xml instead.

In my case, I got them to return an xml based on a header (Note this works only from FSM 6 U11 and onwards, previous versions don’t capture this header)

@Miraj 

Thanks for the reply.

I think in the meantime I have figured a solution for what I need.

Don’t think it’s great but using a Business Rule on the response I can use the perform_exec_db_edit to update the record I need.

On the Integration Log Detail I used a function in SQL called JSON_VALUE to extract the data in the response. This can then but used in an update statement to update the record I want to store the token against. Using data in both the transaction input and result you’re able to extract whatever is required.

Ady

@AdrianEgley 

That’s great!

If it’s not too much trouble, would you be able to share some screenshots (or email to miraj.mohajireen@opportunitybox.com.au) of the setup you have.

Cheers,
Miraj

Sure, 

I’ll try to explain here.

This is the response I was getting from the API.

{

  "policyValidationToken": "FTNc5gBvvNJxEa9RjEdNbzqBa",

  "vrn": "ABC123",

  "axaPolicyNumber": "300201477",

  "brokerPolicyNumber": null,

  "glassCoverExists": "True",

  "glassReplacementExcess": "75.00",

  "glassRepairExcess": "15.00",

  "policyStartDate": "2021-05-26T10:02:00Z",

  "policyEndDate": "2022-05-26T00:00:00Z",

  "brandName": "AXA-Direct",

  "policyHolderSurname": "Test",

  "policyHolderFirstName": "User",

  "vehicleMake": "Ford",

  "vehicleModel": "Fiesta",

  "postCode": "KT11 1HY",

  "address": "74 Portsmouth Road, Cobham, Surrey, KT11 1HY"

}

Essentially to start with I just need the policyValidationToken, and put that against a table associated with the Request.

I have a XML basic business rule running on the Integration Log.

But the XML being posted is;

<perform_exec_db_edit>
  <parameters>
    <sql_command>
update aw_product_policy_account_info
set authorization_code = (select JSON_VALUE(transaction_result,'$.policyValidationToken') from integration_log_detail where run_id = '@expression[integration_log.run_id]' )
where request_id = (select JSON_VALUE(transaction_input,'$.validatePolicy.RequestID')from integration_log_detail where run_id = '@expression[integration_log.run_id]')

</sql_command>
  </parameters>
</perform_exec_db_edit>

The JSON_VALUE is used to just get the data in the response I need.

select JSON_VALUE(transaction_result,'$.policyValidationToken') from integration_log_detail

The function is pretty self-explanatory, and that is used in both the set and where clauses to get the token and also update the correct request_id.

Hope that helps

Ady 

@AdrianEgley Lovely. This is a neat workaround.
Thanks a lot for sharing