How to split json keys and values separately without data flow in azure data factory

Question

I have HubSpot dataset and I am doing a lookup activity which gives first row from dataset

Firstrow : {

'col1' : 'value1',
'col2' : ' value2',

}

I want to get all columns from output JSON (I,e col1, col2) so that I can do schema validation for table. I am wondering how get dynamic column information

Note : I cannot use Data flow & get metadata activity HubSpot datasets cannot support this.

Answer 1

Hello @Uday Juttu ,
Thanks for the question and using MS Q&A platform.

So you want to check all the column names for schema validation without using dataflow.

Much to my frustration, there is no function for returning all the keys/properties of a JSON. If there was, I could do so many things.

However there is another way. Instead of getting a list of all keys from the lookup activity, how about providing a list of all expected column names as a parameter. Then we can ForEach that list of expected names and try

@contains( activity('lookup').output.firstrow , item() )  

This would verify each item you provide does exist, but it doesn't verify that there are no extra columns. For that we need another test. Since json key-value pairs always have a ; between them, we can count them by splitting them and subtracting 1 from the length of the resulting array.

@sub(  
  length(  
    split(  
      string(activity('lookup').output.firstRow)  
    , ':')  
  )  
,1)  

Of course we want to compare to the length of your list of expected columns, so wrap that in

equals( length( pipeline.parameters.list_to_compare , count_of_lookup_columns )  

Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators