我正在使用Azure数据工厂和Azure SQL数据库编写ETL工具。数据工厂捕获映射数据流的输出，并将其作为字符串插入到SQL Server表（Audit.OperationsEventLog）的StatusMessage列中。 StatusMessage列为varchar（8000），用于存储格式化为有效json的数据。

SELECT *
FROM Audit.OperationsEventLog lg
CROSS APPLY OPENJSON(lg.StatusMessage) dt

当我使用上面的查询从表中查询json字符串时，它抱怨

  

JSON文本格式不正确。意外字符“”是   在位置382找到

这是一个双引号，周围是两个单引号。

我已经使用JSONLint（http://jsonlint.com）来验证json字符串是否正确编码。

当我将StatusMessage列中的json字符串复制到varchar（8000）变量中时，我可以使用OPENJSON解析该字符串。

DeclARE @testjson varchar(8000) = '
{   "EventType": "DataFactoryPipelineRunactivity","DataFactoryName":"fa603ea7-f1bd-48c0-a690-73b92d12176c","DataFactoryPipelineName":"Import Blob Storage account Key CSV file into generic SQL table using Data Flow activity Logging to Target SQL Server","DataFactoryPipelineactivityName":"Copy Generic CSV Source to Generic SQL Sink","DataFactoryPipelineactivityOutput":"{runStatus:{computeAcquisitionDuration:316446,dsl: source() ~> ReadFromCSVInBlobStorage  ReadFromCSVInBlobStorage derive() ~> EnrichWithDataFactoryMetadata  EnrichWithDataFactoryMetadata sink() ~> WriteToTargetSqlTable,profile:{ReadFromCSVInBlobStorage:{computed:[],lineage:{},dropped:0,drifted:1,newer:1,total:1,updated:0},EnrichWithDataFactoryMetadata:{computed:[],newer:6,total:7,WriteToTargetSqlTable:{computed:[],lineage:{__DataFactoryPipelineName:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__DataFactoryPipelineName]}]},__DataFactoryPipelineRunId:{mapped:false,columns:[__DataFactoryPipelineRunId]}]},id:{mapped:true,from:[{source:ReadFromCSVInBlobStorage,columns:[id]}]},__InsertDateTimeUTC:{mapped:false,columns:[__InsertDateTimeUTC]}]},__DataFactoryName:{mapped:false,columns:[__DataFactoryName]}]},__FileName:{mapped:false,columns:[__FileName]}]},__StorageaccountName:{mapped:false,columns:[__StorageaccountName]}]}},newer:0,updated:7}},metrics:{WriteToTargetSqlTable:{rowsWritten:4,sinkProcessingTime:1436,sources:{ReadFromCSVInBlobStorage:{rowsRead:4}},stages:[{stage:3,partitionTimes:[621],bytesWritten:0,bytesRead:24,streams:{WriteToTargetSqlTable:{type:sink,count:4,partitionCounts:[4],cached:false},EnrichWithDataFactoryMetadata:{type:derive,ReadFromCSVInBlobStorage:{type:source,cached:false}},target:WriteToTargetSqlTable,time:811}]}}},effectiveIntegrationRuntime:DefaultIntegrationRuntime (East US)}","DataFactoryPipelineRunID":"63759585-4acb-48af-8536-ae953efdbbb0","DataFactoryPipelineTriggerName":"Manual","DataFactoryPipelineTriggertype":"Manual","DataFactoryPipelineTriggertime":"2019-11-05T15:27:44.1568581Z","Parameters":{    
        "StorageaccountName":"fa603ea7","FileName":"0030_SourceData1.csv","TargetSQLServerName":"5a128a64-659d-4481-9440-4f377e30358c.database.windows.net","TargetSQLDatabaseName":"TargetDatabase","TargetSQLusername":"demoadmin"   
    },"InterimValues":{    
        "SchemaName":"utils","TableName":"vw_0030_SourceData1.csv-2019-11-05T15:27:57.643"   
    }  
}'

SELECT      *
FROM        OPENJSON(@testjson)

SELECT      *
FROM        OPENJSON(@testjson) data
            CROSS APPLY OPENJSON(data.value) moredata
WHERE       data.type = 5

问题被隔离到“ DataFactoryPipelineactivityOutput”。

数据工厂构建要插入到表的StatusMessage列中的json字符串。我将在StatusMessage字符串中删除所有出现的双引号。

{
    "EventDateTime":"@{utcNow()}","EventState":"Success","SourceName":"@{concat(pipeline().DataFactory,'/',pipeline().Pipeline,'/Copy Generic CSV Source to Generic SQL Sink')}","SourceType":"DataFactoryPipelineRunactivity","StatusMessage":"{
        \"EventType\": \"DataFactoryPipelineRunactivity\",\"DataFactoryName\":\"@{pipeline().DataFactory}\",\"DataFactoryPipelineName\":\"@{pipeline().Pipeline}\",\"DataFactoryPipelineactivityName\":\"Copy Generic CSV Source to Generic SQL Sink\",\"DataFactoryPipelineactivityOutput\":\"@{replace(string(activity('Copy Generic CSV Source to Generic SQL Sink').output),'"','')}\",\"DataFactoryPipelineRunID\":\"@{pipeline().RunID}\",\"DataFactoryPipelineTriggerName\":\"@{pipeline().TriggerName}\",\"DataFactoryPipelineTriggertype\":\"@{pipeline().Triggertype}\",\"DataFactoryPipelineTriggertime\":\"@{pipeline().Triggertime}\",\"Parameters\":{
            \"StorageaccountName\":\"@{pipeline().parameters.StorageaccountName}\",\"FileName\":\"@{pipeline().parameters.FileName}\",\"TargetSQLServerName\":\"@{pipeline().parameters.TargetSQLServerName}\",\"TargetSQLDatabaseName\":\"@{pipeline().parameters.TargetSQLDatabaseName}\",\"TargetSQLusername\":\"@{pipeline().parameters.TargetSQLusername}\"
        },\"InterimValues\":{
            \"SchemaName\":\"@{activity('Get Target View Schema and Name').output.firstRow.SchemaName}\",\"TableName\":\"@{activity('Get Target View Schema and Name').output.firstRow.ViewName}\"
        }
    }"
}

谁能看到我是否做错了什么，或者这是OPENJSON中的错误？我希望我做了一些愚蠢的事，而我需要的只是第二组眼睛