.Json sink has column name and \ in the file

Question

.Json sink has column name and \ in the file

VishalV 6

hello
I am using a dataflow activity in ADF to search and replace value - .json data as per this suggestion https://learn.microsoft.com/en-us/answers/questions/779149/data-flow-search-and-replace-value-json-data.html

I was able to replace the string but in my .json sink, i have the column name and \ .

Subashri Vasudevan 11,226 Reputation points

2022-09-28T03:52:29.487+00:00

Hi,

Request you to post more details about the input json and expected output with the screenshot of settings if possible.

Thanks
VishalV 6 Reputation points

2022-09-28T20:04:44.657+00:00

I have a json file like below where I want to rename all occurrences of ABC to XYZ.
{
"name": "Ranjana Sinha",
"schools": [{"schoolName": "ABC Institute",
"schoolAddress": "123, ABC Road"}]
}

I am using a csv dataset as source in Dataflow then in a derived column I am replacing ABC with XYZ using below expression.
replace(Column_1,'ABC ','XYZ').

I am using a Json dataset as my sink where I will write the contents of Column_1 from derived output.

In my target json file I have like this - {"Column_1":"{\"name\":\"Ranjana Sinha\",\"schools\"

I want to get rid of the column name "Column_1" and the "\" from my target json file.
Majji, Pathrudu (Baker Hughes Contractor) 0 Reputation points

2023-05-28T18:43:38.6033333+00:00

Hi @VishalV , How did you resolve this issue ?

1 answer

Your answer

Subashri Vasudevan 11,226 Reputation points

2022-09-28T03:52:29.487+00:00

Hi,

Request you to post more details about the input json and expected output with the screenshot of settings if possible.

Thanks
VishalV 6 Reputation points

2022-09-28T20:04:44.657+00:00

I have a json file like below where I want to rename all occurrences of ABC to XYZ.
{
"name": "Ranjana Sinha",
"schools": [{"schoolName": "ABC Institute",
"schoolAddress": "123, ABC Road"}]
}

I am using a csv dataset as source in Dataflow then in a derived column I am replacing ABC with XYZ using below expression.
replace(Column_1,'ABC ','XYZ').

I am using a Json dataset as my sink where I will write the contents of Column_1 from derived output.

In my target json file I have like this - {"Column_1":"{\"name\":\"Ranjana Sinha\",\"schools\"

I want to get rid of the column name "Column_1" and the "\" from my target json file.
Majji, Pathrudu (Baker Hughes Contractor) 0 Reputation points

2023-05-28T18:43:38.6033333+00:00

Hi @VishalV , How did you resolve this issue ?

Answer 1

Hi @VishalV ,
Welcome to Microsoft Q&A platform and thanks for posting your question here.
As per my understanding , you want to replace one string with another using data transformation in mapping Dataflow . Please let me know if my understanding is incorrect.

You can try the following approach :

Upload the .json file into ADLS and create a dataset with .json format and use the same in source transformation. Make sure to select Array of documents option in JSON settings of Source options tab.
Use Flatten transformation to convert items of array into individual rows. Select schools[] array in unroll by option and select the input columns
Use derived column transformation to replace one string with another for all the relevant columns.
Add surrogate key column to add a dummy incremental column
Use Aggregate transformation to again group the columns into array by using collect function and grouping using identity column.
Use select transformation to remove the unwanted identity column
Use sink transformation to load the data again into .json file. In the sink setting, Select 'Output to single file' in FileName Option and set Single partition when pop up arrives. Provide outputFileName and run the pipeline.

Please refer to the following video:

Hope this will help. Please let us know if any further queries.

------------------------------

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

Share via

.Json sink has column name and \ in the file

1 answer

Your answer