delete nested json from json file in adf

Question

delete nested json from json file in adf

Veera Mummidi 25

Hi Team, My json data is like below. I want to delete the strikeout nested objects (~~request_documents)~~ from json file in adf dataflow. {`` ``"accountNumber": "abcdefg",`` ``"comments": "hsdhfjkhasjkfhajh",`` ``"requestDetail": {`` ``"prospect": {}`` ``},`` ``"additionalQuestionRequire": {},`` ``"additionalQuestion": {`` ``"branchQuestion": {},`` ``"nauQuestion": {},`` ``"featureQuestion": {},`` ``"faQuestion": {},`` ``"nonFaQuestion": {}`` ``},`` ~~"request_documents": {~~ ~~"client_instructions": [~~ ~~null~~ ~~]~~ ~~}~~,`` ``"deliveryMethod": "xgjhsgakdk",`` ``"predefinedMemo": "ghjghjfhgcc43fh65",`` ``"selectProductTypeOfAccount": "jkghiuy58gh",`` ``"specify": ""`` ``}

QuantumCache 20,681 Reputation points Moderator

2024-02-29T21:36:19.72+00:00

Veera Mummidi You are trying to create an updated JSON structure, right? Did you try any Dataflow transformations? any? Please add info about what did you tried so far? so that we can better understand and help you further. Thanks for your understanding!
Veera Mummidi 25 Reputation points

2024-03-01T22:20:59.51+00:00

Hi Satish, Yes i tried dataflow using derived column but i am not able to remove the nested json from column.
QuantumCache 20,681 Reputation points Moderator

2024-03-01T22:31:49.68+00:00

@Veera Mummidi Could you please add screenshot of your dataflow which you have tried?

So that we better understand the current scenario to help you further!
Veera Mummidi 25 Reputation points

2024-03-01T22:42:49.07+00:00

Hi Satish,

I am not able to send the screenshot. My requirement is loading from json to sql table,it has 5 columns, i have flatten the json into columns and load the data into 4 columns and 5th column i have to pass the below json object as nvarchar in dataflow. in the below json object i have to remove the "request_documents" nested object and load it into the table.

{`` ``"accountNumber": "abcdefg",`` ``"comments": "hsdhfjkhasjkfhajh",`` ``"requestDetail": {`` ``"prospect": {}`` ``},`` ``"additionalQuestionRequire": {},`` ``"additionalQuestion": {`` ``"branchQuestion": {},`` ``"nauQuestion": {},`` ``"featureQuestion": {},`` ``"faQuestion": {},`` ``"nonFaQuestion": {}`` ``},`` ~~"request_documents": {~~ ~~"client_instructions": [~~ ~~null~~ ~~]~~ ~~}~~,`` ``"deliveryMethod": "xgjhsgakdk",`` ``"predefinedMemo": "ghjghjfhgcc43fh65",`` ``"selectProductTypeOfAccount": "jkghiuy58gh",`` ``"specify": ""`` ``}
Veera Mummidi 25 Reputation points

2024-03-01T22:44:23.5466667+00:00

Hi Satish,

I am not able to send the screenshot. My requirement is loading from json to sql table,it has 5 columns, i have flatten the json into columns and load the data into 4 columns and 5th column i have to pass the below json object as nvarchar in dataflow. in the below json object i have to remove the "request_documents" nested object and load it into the table.

{`` ``"accountNumber": "abcdefg",`` ``"comments": "hsdhfjkhasjkfhajh",`` ``"requestDetail": {`` ``"prospect": {}`` ``},`` ``"additionalQuestionRequire": {},`` ``"additionalQuestion": {`` ``"branchQuestion": {},`` ``"nauQuestion": {},`` ``"featureQuestion": {},`` ``"faQuestion": {},`` ``"nonFaQuestion": {}`` ``},`` ~~"request_documents": {~~ ~~"client_instructions": [~~ ~~null~~ ~~]~~ ~~}~~,`` ``"deliveryMethod": "xgjhsgakdk",`` ``"predefinedMemo": "ghjghjfhgcc43fh65",`` ``"selectProductTypeOfAccount": "jkghiuy58gh",`` ``"specify": ""`` ``}
QuantumCache 20,681 Reputation points Moderator

2024-03-01T22:59:03.21+00:00

Hi @Veera Mummidi Could you please attach the expected JSON output? I know that you have added info on the source JSON, but for clarity please add Source (Input) JSON and Expected (Result) JSON