Parse JSON

Question

Parse JSON

Stênia Luiz Santos 5

Dear colleagues,

I need to get this result in columns for each of the indexes, but when I create the columns in this way I only get the result for the first index. Could you help me how to perform this transformation? Thank you very much. Derived_Column

Expression_Index1

Result_Index1

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-17T08:36:29.64+00:00

Hi Stenia Luiz Santos ,

Thnakyou for using Microsoft Q&A platform and thanks for posting your query here. I understand that you are trying to parse the json using mapping dataflow. However, it is showing same content for all the rows after parsing. Please let me know if that is not the correct understanding.

For better understanding and better support, I would like to ask for the source json data. You can attach the json as a .txt file here. Looking forward to your response. Thankyou
Stênia Luiz Santos 5 Reputation points

2023-07-17T14:55:12.48+00:00

Hi @AnnuKumari-MSFT

Follow the json attached.

Yes, I am using data flow and the image result is correct and shows CASHFLOW_SPREADSHEET[1]. The problem is that I need to be able to extract the result of the other indexes from [2] to [50]. Thank you.

data_11a74074-c7a0-4053-bd7c-9ac60936f0a5_3b007c3f-1ff1-44a0-80b5-da6e53123273.txt
Stênia Luiz Santos 5 Reputation points

2023-07-26T18:51:32.64+00:00

Hello @AnnuKumari-MSFT could you support me with this?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-08-10T15:36:44.4233333+00:00

Hi Stênia Luiz Santos ,
Apologies for delay in my response. When I try to preview the source data with json setting as document per line, I can see the data from all the rows.

Could you please share what is the expected end result? Also, It would be great if you could share what is derived column transformation and parse transformation configurations?

Thankyou

Stênia Luiz Santos 5

Hi @AnnuKumari-MSFT

Within this JSON, the column "CASHFLOW_SPREADSHEET" contains 50 data sets.

In my research, one alternative would be to add a derived column with the expression: replace(replace(CASHFLOW_SPREADSHEET,'[',''),']','') to separate each of the sets and then parse it to create the tables with the expression:

({Fluxo} as string,
		{Linha} as string,
		{Campo} as string,
		{Coluna 0} as string,
		{Coluna 1} as string,
		{Coluna 2} as string,
		{Coluna 3} as string,
		{Coluna 4} as string,
		{Coluna 5} as string,
		{Coluna 6} as string,
		{Coluna 7} as string,
		{Coluna 8} as string,
		{Coluna 9} as string,
		{Coluna 10} as string,
		{Coluna 11} as string,
		{Coluna 12} as string,
		{Coluna 13} as string,
		{Coluna 17} as string,
		{Coluna 18} as string,
		{Coluna 19} as string,
		{Coluna 20} as string,
		{Coluna 21} as string,
		{Coluna 22} as string,
		{Coluna 23} as string,
		{Coluna 24} as string,
		{Coluna 25} as string,
		{Coluna 26} as string,
		{Coluna 27} as string)

User's image

However, I can only extract one dataset and do not know how to extract the other 49. It is unclear whether this approach is the most suitable, as it only works for a single dataset.

Please let me know if you have any further questions. Thank you!

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-17T08:36:29.64+00:00

Hi Stenia Luiz Santos ,

Thnakyou for using Microsoft Q&A platform and thanks for posting your query here. I understand that you are trying to parse the json using mapping dataflow. However, it is showing same content for all the rows after parsing. Please let me know if that is not the correct understanding.

For better understanding and better support, I would like to ask for the source json data. You can attach the json as a .txt file here. Looking forward to your response. Thankyou
Stênia Luiz Santos 5 Reputation points

2023-07-17T14:55:12.48+00:00

Hi @AnnuKumari-MSFT

Follow the json attached.

Yes, I am using data flow and the image result is correct and shows CASHFLOW_SPREADSHEET[1]. The problem is that I need to be able to extract the result of the other indexes from [2] to [50]. Thank you.

data_11a74074-c7a0-4053-bd7c-9ac60936f0a5_3b007c3f-1ff1-44a0-80b5-da6e53123273.txt
Stênia Luiz Santos 5 Reputation points

2023-07-26T18:51:32.64+00:00

Hello @AnnuKumari-MSFT could you support me with this?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-08-10T15:36:44.4233333+00:00

Hi Stênia Luiz Santos ,
Apologies for delay in my response. When I try to preview the source data with json setting as document per line, I can see the data from all the rows.

Could you please share what is the expected end result? Also, It would be great if you could share what is derived column transformation and parse transformation configurations?

Thankyou
Stênia Luiz Santos 5 Reputation points

2023-08-10T19:02:54.87+00:00

Hi @AnnuKumari-MSFT

Within this JSON, the column "CASHFLOW_SPREADSHEET" contains 50 data sets.

In my research, one alternative would be to add a derived column with the expression: replace(replace(CASHFLOW_SPREADSHEET,'[',''),']','') to separate each of the sets and then parse it to create the tables with the expression:

({Fluxo} as string, {Linha} as string, {Campo} as string, {Coluna 0} as string, {Coluna 1} as string, {Coluna 2} as string, {Coluna 3} as string, {Coluna 4} as string, {Coluna 5} as string, {Coluna 6} as string, {Coluna 7} as string, {Coluna 8} as string, {Coluna 9} as string, {Coluna 10} as string, {Coluna 11} as string, {Coluna 12} as string, {Coluna 13} as string, {Coluna 17} as string, {Coluna 18} as string, {Coluna 19} as string, {Coluna 20} as string, {Coluna 21} as string, {Coluna 22} as string, {Coluna 23} as string, {Coluna 24} as string, {Coluna 25} as string, {Coluna 26} as string, {Coluna 27} as string)

However, I can only extract one dataset and do not know how to extract the other 49. It is unclear whether this approach is the most suitable, as it only works for a single dataset.

Please let me know if you have any further questions. Thank you!

Share via

Parse JSON

Your answer