Converting JSON Array to String

Question

Converting JSON Array to String

Prince Rastogi Admin Account 21

We are using Azure App Insights with continuous export to Azure Blob Storage. so we have json blob in blob storage account. We need this data to be loaded incrementally to on-premise SQL Server and we are using Azure Data Factory pipeline for this.

We are adding custom data to App Insight during data collection. which is turning into JSON array inside blob storage. there are multiple folders inside exported data from App Insight to blob storage like Events, Exceptions etc.

We are using copy pipeline to download data from blob storage to on-premise SQL Server. Initially we setup copy pipeline for Events data and it was automatically able to convert JSON array from blob to VARCHAR(MAX) on on-premise SQL DB.

But when I am trying to setup the same pipeline for Exceptions data then I am getting below error. I already tried enable skip incompatible row to skip the issue but didn't worked. I also tried to tick checkbox for [Map complex values to string] but didn't worked as well.

ErrorCode=JsonUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JArray with value [{"env":"x"},{"brand":"y"},{"domain":"z"},{"source":"s"}] is not supported yet. Please either remove the targeted column 'abc' or enable skip incompatible row to skip the issue rows.,Source=Microsoft.DataTransfer.Common,'

My goal here is to convert this JSON array into string. Don't want to use Collection reference because i need full JSON Array as single string.

HarithaMaddi-MSFT 10,151 Reputation points

2020-10-29T07:30:06.207+00:00

Hi @Prince Rastogi Admin Account ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

I looked at the previous issues from customers on similar errors and found that enabling this flag resolved the issue. Please suggest if that helps in resolving the issue. Also, if issue persists, please share the sample JSON file, pipeline run id and data factory name with us for deeper investigation.

Thanks for your patience!
Prince Rastogi Admin Account 21 Reputation points

2020-10-29T12:25:49.507+00:00

As I already mention this "I also tried to tick checkbox for [Map complex values to string] but didn't worked as well" in above question.
HarithaMaddi-MSFT 10,151 Reputation points

2020-10-29T13:27:12.42+00:00

Apologies @Prince Rastogi Admin Account that it got missed. As requested above, can you please share the additional details that would help us to investigate the issue further.
Prince Rastogi Admin Account 21 Reputation points

2020-10-29T16:47:15.843+00:00

blob file has been attached here. Original file extension was .blob but to upload here i just used .txt.

36044-612d70e2-06e3-499a-82b2-42b41089f379-20201008-0153.txt
HarithaMaddi-MSFT 10,151 Reputation points

2020-10-30T09:25:39.77+00:00
Thanks @Prince Rastogi Admin Account for sharing the file, that helped. As per my understanding, copy activity is not able to read this JSON file as it has multiple arrays. One workaround I find useful in such scenarios is as below that helps in loading any kind of JSONs into a SQL.

Please suggest if this approach can be used and let us know for further queries, we will be glad to assist.

Pipeline Design:

SQL Code:

create table dbo.Logs ( _id bigint primary key IDENTITY(1,1), log nvarchar(max) ); --Strored procedure SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spUpsertLogs] @logs nvarchar(max) AS BEGIN INSERT INTO dbo.Logs (log) values(@logs) END select * from dbo.Logs

36229-workaroundjson.txt
Prince Rastogi Admin Account 21 Reputation points

2020-11-01T01:54:47.347+00:00

Thank you @HarithaMaddi-MSFT !

Looks like I was not able to explain clearly. sorry for that.

Copy Data activity is able to parse almost all nodes from JSON except $['context']['custom']['dimensions']. This $['context']['custom']['dimensions'] is like a JSON array in source file which we need as string or need a way to convert that as string.

PS: Each file on source can have multiple JSON documents separated by new line.
HarithaMaddi-MSFT 10,151 Reputation points

2020-11-03T07:02:34.913+00:00

Thanks @Prince Rastogi Admin Account for sharing the details. Apologies for the delay in responding. Yes, I am able to observe the same while using copy activity and hence suggested to go via lookup and stored procedure as it is compatible with all JSON formats.

Please suggest if this workaround works!
HarithaMaddi-MSFT 10,151 Reputation points

2020-11-04T17:21:58.717+00:00

Hi @Prince Rastogi Admin Account ,

We have not received a response from you. Please suggest if the above workaround works in implementing the requirement. Let us know for further queries and we will be glad to assist.
HarithaMaddi-MSFT 10,151 Reputation points

2020-11-05T08:34:34.027+00:00

Hi @Prince Rastogi Admin Account ,

We still have not heard back from you. Following up to check if the above workaround works in implementing the requirement. Please let us know for further queries and we will be glad to assist.
Artur Tolstenco 6 Reputation points

2020-11-05T18:13:39.587+00:00

anyone got this work?
HarithaMaddi-MSFT 10,151 Reputation points

2020-11-09T07:50:20.147+00:00

Hi @Prince Rastogi Admin Account ,

The above workaround was suggested as it is currently not possible to read data with multiple arrays into a single string value in SQL. Please suggest for further questions on the workaround.

Looking forward for your response!
HarithaMaddi-MSFT 10,151 Reputation points

2020-11-10T12:40:58.197+00:00

Hi @Prince Rastogi Admin Account ,

We have not heard any response from you. Please suggest for further questions on the workaround and we will be glad to assist.
Prince Kumar Rastogi 31 Reputation points

2020-12-30T17:58:02.15+00:00

Hi @HarithaMaddi-MSFT ,

Sorry for reply late and Thank you for your help! I was able to fix this by first creating destination blank table and created a dedicated column to store JSON array in that table. After this I was able to download that data: