c# - 如何使用 C# 将复杂的 JSON 数组响应转换为简单的 Json

Question

我收到这种 JSON 格式的响应

{
"soap:Envelope":
{
    "@xmlns:soap":"http://schemas.xmlsoap.org/soap/envelope/","soap:Body":
    {
        "ns2:processPayment":
        {
            "@xmlns:ns2":"http://ws.cwt.ru/","Status":"SUCCESS","StatusCode":"000","StatusMessage":"SUCCESS","Payments":
            {
                "Payment":
                {
                    "InPaymentParameters":
                    {
                        "entry":
                        [
                            {"key":"FEE_AMOUNT","value":"100"},                             
                            {"key":"SOURCE_AMOUNT","value":"80000"},{"key":"SOURCE_ACCOUNT_NUMBER","value":"888117823"},
                            {"key":"DESTINATION_BANK_CB_ID","value":"BANK OF AMERICA"},
                            {"key":"DESCRIPTION","value":"chama"},
                            {"key":"merchantId","value":"1321"},{"key":"MERCHANT_ACQUIRER_CONTRACT_ID","value":"1"},
                            {"key":"DESTINATION_ACCOUNT","value":"01116132194100"},
                            {"key":"MERCHANT_ID","value":"admin"},
                            {"key":"REMOTE_TRANSACTION_ID","value":"000000086814933"},
                            {"key":"DESTINATION_CONNECTION_ID","value":"243"},
                            {"key":"FINANCE_OPERATION_TYPE","value":"WITHDRAWAL"},
                            {"key":"ISO8583_CARD_ACCEPTOR_ID","value":"000000000105817"},
                            {"key":"SOURCE_CONNECTION_ID","value":"243"},
                            {"key":"MESSAGE_ID","value":"139096"},
                            {"key":"ISO8583_CARD_ACCEPTOR_TERMINAL_ID","value":"POS00002"},
                            {"key":"OPERATION_ID","value":"756033604"},
                            {"key":"OPERATION_STATUS_MESSAGE"},
                            {"key":"OPERATION_STATUS","value":"SUCCESS"},
                            {"key":"SERVICE_NAME"},
                            {"key":"AUTHORIZATION_PASS","value":"admin"},
                            {"key":"AUTHORIZATION_LOGIN","value":"admin"},
                            {"key":"ISO8583_APPROVAL_CODE","value":"122127"},
                            {"key":"SOURCE_CARD_PAN"},
                            {"key":"SERVICE_TYPE","value":"FINANCE"},
                            {"key":"TRANSACTION_ID","value":"184139327"}
                        ]
                    },"OutPaymentParameters":null,"ServiceFields":null
                }
            }
        }
    }
}} 

并希望把它放在简单的格式，比如

{ "Status":"SUCCESS","StatusCode":"000","FEE_AMOUNT":"100", "SOURCE_AMOUNT":80000, "DESTINATION_BANK_CB_ID":"BANK OF AMERICA" }

使用 C#。请问如何实现？所有回复都遵循相同的格式。任何见解都将受到高度赞赏。

标签： c#json

Answer 1

不在 C# 中，但我可以提供一种解决方案或至少一种方法，将此 JSON 响应转换为 SQL Server 中的第一个表格格式，然后再次转换为所需的 JSON 格式，如下面的 SQL 代码所示

;with cte as
(

select
--  j2.[@xmlns:soap],
--  j4.[@xmlns:ns2],
    j4.[Status],
    j4.[StatusCode],
--  j4.[StatusMessage],
    j8.[key],
    j8.[value]
from openjson(@json) with (
    [soap:Envelope] nvarchar(max) as json
) as j1
cross apply openjson(j1.[soap:Envelope]) with (
    [@xmlns:soap] nvarchar(max) ,
    [soap:Body] nvarchar(max) as json
) as j2
cross apply openjson(j2.[soap:Body]) with (
    [ns2:processPayment] nvarchar(max) as json
) as j3
cross apply openjson(j3.[ns2:processPayment]) with (
    [@xmlns:ns2] nvarchar(max),
    [Status] nvarchar(max),
    [StatusCode] nvarchar(max),
    [StatusMessage] nvarchar(max),
    Payments nvarchar(max) as json
) as j4
cross apply openjson(j4.[Payments]) with (
    [Payment] nvarchar(max) as json
) as j5
cross apply openjson(j5.[Payment]) with (
    InPaymentParameters nvarchar(max) as json,
    OutPaymentParameters nvarchar(max) as json,
    ServiceFields nvarchar(max) as json
) as j6
cross apply openjson(j6.InPaymentParameters) with (
    [entry] nvarchar(max) as json
) as j7
cross apply openjson(j7.[entry]) with (
    [key] nvarchar(max),
    [value] nvarchar(max)
) as j8
where 
    [key] in ('FEE_AMOUNT','SOURCE_AMOUNT','DESTINATION_BANK_CB_ID')
)
select 
    [Status], 
    [StatusCode], 
    max(FEE_AMOUNT) as FEE_AMOUNT,
    max(SOURCE_AMOUNT) as SOURCE_AMOUNT,
    max(DESTINATION_BANK_CB_ID) as DESTINATION_BANK_CB_ID
from (
select
    [Status],
    [StatusCode],
    case when [key] = 'FEE_AMOUNT' then [value] end as FEE_AMOUNT,
    case when [key] = 'SOURCE_AMOUNT' then [value] end as SOURCE_AMOUNT,
    case when [key] = 'DESTINATION_BANK_CB_ID' then [value] end as DESTINATION_BANK_CB_ID
from cte
) t
group by [Status], [StatusCode]
for json auto

这种在 CROSS APPLY 连接中级联 OpenJSON 调用的方式有助于将复杂的 JSON 结构转换为表格式