json - SQL Server 提示无效的 json

Question

我正在使用 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 text is not properly formatted. Unexpected character '"' is found at position 382

这是一个被两个单引号包围的双引号。

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

当我将 json 字符串从 StatusMessage 列复制到 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:[],lineage:{},dropped:0,drifted:1,newer:6,total:7,updated:0},WriteToTargetSqlTable:{computed:[],lineage:{__DataFactoryPipelineName:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__DataFactoryPipelineName]}]},__DataFactoryPipelineRunId:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__DataFactoryPipelineRunId]}]},id:{mapped:true,from:[{source:ReadFromCSVInBlobStorage,columns:[id]}]},__InsertDateTimeUTC:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__InsertDateTimeUTC]}]},__DataFactoryName:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__DataFactoryName]}]},__FileName:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__FileName]}]},__StorageAccountName:{mapped:false,from:[{source:EnrichWithDataFactoryMetadata,columns:[__StorageAccountName]}]}},dropped:0,drifted:1,newer:0,total:7,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,count:4,partitionCounts:[4],cached:false},ReadFromCSVInBlobStorage:{type:source,count:4,partitionCounts:[4],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 中的错误？我希望我做了一些愚蠢的事情，我所需要的只是第二双眼睛

Answer 1

如果序列

 '"'

是字符串的一部分，不应该是

'\"'

否则它会被解释为字符串的结尾，这确实是无效的 JSON。

所以，这意味着

\"DataFactoryPipelineActivityOutput\":\"@{replace(string(activity('Copy Generic CSV Source to Generic SQL Sink').output), '\"', '')}\",      \"DataFactoryPipelineRunID\":\"@{pipeline().RunID}\",

代替

\"DataFactoryPipelineActivityOutput\":\"@{replace(string(activity('Copy Generic CSV Source to Generic SQL Sink').output), '"', '')}\",      \"DataFactoryPipelineRunID\":\"@{pipeline().RunID}\",