SQL Server抱怨无效的json [英] SQL Server complains about invalid json

问题描述

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

I am writing an ETL tool using Azure Data Factory and Azure SQL Database. The Data Factory captures the output of a Mapping Data Flow and inserts it into the StatusMessage column of a SQL Server table (Audit.OperationsEventLog) as a string. The StatusMessage column is varchar(8000) and is intended to store data formatted as valid json.

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

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

When I query the json string from the table using the query above, it complains

JSON文本格式不正确.意外的字符"是 在位置382找到

JSON text is not properly formatted. Unexpected character '"' is found at position 382

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

That's a double-quote surrounded by two single-quotes.

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

I have used JSONLint (http://jsonlint.com) to validate that the json string is encoded properly.

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

When I copy the json string from the column StatusMessage into a varchar(8000) variable, I'm able to parse the string using 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".

The problem is isolated to "DataFactoryPipelineActivityOutput".

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

The data factory builds the json string to be inserted into the StatusMessage column of the table. I strip out any occurrences of double-quotes within the StatusMessage string.

{
    "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中的错误吗?我希望我做了一些愚蠢的事，而我所需要的只是第二套眼睛

Can anyone see if I'm doing something wrong, or is this a bug in OPENJSON? I'm hoping that I did something stupid and all I need is a second set of eyes

推荐答案

如果序列

 '"'

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

is part of a string, shouldn't that be

'\"'

否则，它将被解释为字符串的结尾，并且确实是无效的JSON.

otherwise it's interpreted as the end of string, and that would indeed be invalid 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}\",

这篇关于SQL Server抱怨无效的json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！