parseJson in event-stream - no preview and error on publishing

[MaxTee]

Hi.

I am using IoT Hub as an event stream source and the schema of incoming data includes columns like: topic, message, PartitionId, etc. - this is the usual data that is coming from an IoT Hub. The "message" column is of type string but actually contains a json structure. I want to use to ManageFields node with the Parse_Json built-in function to parse that column so that I can select elements by key in following steps. However, this does not behave as expected.

First: I cannot preview the data of that ManageFields function. It says "unable to preview data". This is unhelpful since it means that I will have to do all following steps blindly.

But second and more crucial: When trying to publish the event-stream, an error is thrown:

"An invalid request has been received: '{"request.properties":["Required property 'functionName' not found in JSON. Path 'properties', line 1, position 1513321."]}'.(RootActivityID: cc44a29a-daf5-486c-8966-7d1560eb9252)"

This, of course, is a blocker.

Some help is appreciated.

[v-prasare]

Hi @MaxTee,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

@datacoffee , Thanks for your promt respons

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[datacoffee]

I meant to transform the data before the Eventstream. Sorry for the confusion...

if you can't do that, then you have to go through the Eventhouse and transform the data there. You can use the bag_unpack to unfold the entire json with no hassle.

[MaxTee]

Sorry for the delay, other things came up.

I am consuming from an IoT-Hub directly into the Eventstream so there really is no "before the Eventstream". Or is there?

I understand that I could query the Eventhouse using bag_unpack. It is a KQL database after all. However, I also would like to do downstream transformations after the unpack using Eventstream. This would not work easily, right?

[datacoffee · Accepted solution]

Correct - the downstream transformations should be a minimum and only what is really needed.
The rest of the transformations is better done in the Eventhouse after ingestion.

😊

[Abdel19]

Hi,

Thanks for the both the question and answer 🙂

I am facing a similar issue in which I need to parse a JSON column and extract specific properties, however my destination is a Fabric Activator not an Eventhouse. As far as I know, data activator doesn't provide any data transformation capabilities, so how would this be solved in this scenario ?

As @MaxTee mentioned before, a parse_string is available as a part of "Manage fields" transformation, but after publishing it returns this error:

Failed to create data source: data-activator.

Long running operation failed with status 'Failed'. Additional Info:'Stream Analytics job has validation errors: Query compilation error: Function 'parse_json' is unknown..'

This is my setup:

[datacoffee]

Hi there

in order to parse a json string you need to first tranform it to a dynamic datatype using a transform task. From there you will be able to parse it using the expected approach you are trying here with another transform task.

let me know if it works.

😊

[MaxTee]

Thanks for your reply. Unfortunately, I don't get it.

You say "transform task". Do you mean a "ManageFields" operation? I can change types of fields there, but not into a "dynamic type". This table suggests that event stream doesn't know a dynamic type, only eventhouse does. Or do I have to do the transformation outside of the event stream?

If you have a more detailed description, that would be great.