Parse Json in Logic App from Stream Analytics -> Service Hub -> Logic Apps

Question

I'm trying to build a logic app that inserts data into a Sql database. The data is coming from s Stream Analytics job, outputting it on a Service Bus topic, consumed in Logic Apps in Service Bus trigger.

To populate the properties of the row inserted (lets say it only has one column 'Name'), I've found that this should work using following syntax:

 "body": {
          "Name": "@{json(decodeBase64(triggerBody()['ContentData'])).Name}"
  },

Provided the message body contains a 'Name' property.

However I get following error message when running this:

{"code":"InvalidTemplate","message":"Unable to process template language expressions in action 'Insert_row' inputs at line '1' and column '2017': 'The template language function 'json' parameter is not valid. The provided value '@\u0006string\b3http://schemas.microsoft.com/2003/10/Serialization/��{\"time\":\"2016-05-25T10:29:17.4953250Z\",\"Name\":\"Y-Axis\",\"Value\":81.0,\"Date\":\"2016-05-25T10:29:17.4953250\",\"EventProcessedUtcTime\":\"2016-05-25T10:29:17.5525449Z\",\"PartitionId\":2,\"EventEnqueuedUtcTime\":\"2016-05-25T10:29:17.2220000Z\"}\u0001' cannot be parsed: 'Unexpected character encountered while parsing value: @. Path '', line 0, position 0.'. Please see https://aka.ms/logicexpressions#json for usage details.'."}

So it seems like that the content is enclosed in another envelope that is preventing json parsing to work.

1) Any simple way how to get around this?

2) Isn't such an integration all within Microsoft Stack just supposed to work without this mocking around?

Thanks, Stefan

Sriram Sriram · Accepted Answer · 2016-08-22T12:24:10

with the limited string functions available in he workflow definition language I had to use a long winded way for removing the additional strings

@{json(substring(replace(decodeBase64(triggerBody()['ContentData']),'@string3http://schemas.microsoft.com/2003/10/Serialization/��', ''),0,sub(length(replace(decodeBase64(triggerBody()['ContentData']),'@string3http://schemas.microsoft.com/2003/10/Serialization/��', '')),1))).fieldname}

is there a better way to do this

Parse Json in Logic App from Stream Analytics -> Service Hub -> Logic Apps

3 Answers