text类型字段转换成列表的SQL实现

1、场景描述

有一个表XT_FpRecord，其中字段FpInfo为text类型，数据JSON格式化如下：
{
“invoiceList”: [{
“invoiceInfo”: {
“bandModel”: “”,
“blueInvoiceCode”: “”,
“blueInvoiceNo”: “”,
“buyerAccount”: “”,
“buyerAddressPhone”: “”,
“buyerName”: “深圳有限公司",
“buyerTaxNo”: "916A”,
“cancellationMark”: “0”,
“carNumber”: “”,
“carrierName”: “”,
“carrierTaxNo”: “”,
“certificateOfImport”: “”,
“checkCode”: “09808295522983027572”,
“checkCount”: “1”,
“commodityInspectionNo”: “”,
“consignorName”: “”,
“consignorTaxNo”: “”,
“draweeName”: “”,
“draweeTaxNo”: “”,
“drawer”: “”,
“engineNo”: “”,
“idNo”: “”,
“invoiceAmount”: “65.22”,
“invoiceCode”: “0412",
“invoiceDate”: “20240314”,
“invoiceNo”: "51",
“invoiceType”: “14”,
“limitedPeopleCount”: “”,
“machineNo”: “499098021197”,
“payee”: “”,
“produceArea”: “”,
“qualifiedNo”: “”,
“receiveName”: “”,
“receiveTaxNo”: “”,
“remark”: “汇总开具”,
“resultCode”: “0001”,
“resultTip”: “查验成功发票一致”,
“reviewer”: “”,
“salerPhone”: “”,
“salerAddress”: “”,
“salerAddressPhone”: "广州市大道2号0769-66",
“salerBankAccount”: "建设银行广州华景支行446",
“salerBankName”: “”,
“salerName”: "广东广深有限公司",
“salerAccount”: “”,
“salerTaxNo”: "91*******9K”,
“taxAmount”: “1.96”,
“taxAuthorityCode”: “”,
“taxAuthorityName”: “”,
“taxDiskNumber”: “”,
“taxPaymentCertificateNo”: “”,
“taxRate”: “”,
“throughAddress”: “”,
“tonnage”: “”,
“totalAmount”: “67.18”,
“trafficFeeFlag”: “”,
“transportGoodsInfo”: “”,
“vehicleIdentificationNo”: “”,
“vehicleTonnage”: “”,
“vehicleType”: “”,
“zeroTaxRateFlag”: “”,
“businessUnit”: “”,
“businessUnitAddress”: “”,
“businessUnitBankAndAccount”: “”,
“businessUnitPhone”: “”,
“businessUnitTaxNo”: “”,
“buyerPhone”: “”,
“buyerUnitCodeOrIdNo”: “”,
“buyerUnitOrIndividual”: “”,
“buyerUnitOrIndividualAddress”: “”,
“carPrice”: “”,
“lemonMarket”: “”,
“lemonMarketAddress”: “”,
“lemonMarketBankAndAccount”: “”,
“lemonMarketPhone”: “”,
“lemonMarketTaxNo”: “”,
“licensePlate”: “”,
“registrationNo”: “”,
“sellerPhone”: “”,
“sellerUnitCodeOrIdNo”: “”,
“sellerUnitOrIndividual”: “”,
“sellerUnitOrIndividualAddress”: “”,
“transferredVehicleOffice”: “”,
“dzdzVer”: “”,
“ofdUrl”: “”,
“invoiceFileUrl”: “”,
“detailList”: [{
“detailAmount”: “65.22”,
“detailNo”: “1”,
“expenseItem”: “”,
“goodsName”: “经营租赁通行费”,
“num”: “20240205”,
“plateNo”: “粤FFFFFF2”,
“specificationModel”: “粤GGGGGG2”,
“taxAmount”: “1.96”,
“taxDetailAmount”: “”,
“taxRate”: “3”,
“taxUnitPrice”: “”,
“trafficDateEnd”: “20240205”,
“trafficDateStart”: “20240205”,
“type”: “客车”,
“unit”: “客车”,
“unitPrice”: “20240205”
}]
}
}]
}
要输出如下格式：

2、SQL语句

WITH FPINFO AS (
select *
from OPENJSON((SELECT
CAST(FpInfo AS VARCHAR(MAX)) AS Json_FpInfo
FROM
XT_FpRecord where id=70964886))
WITH(
buyerName nvarchar(200) ‘$.invoiceList[0].invoiceInfo.buyerName', buyerTaxNo nvarchar(200) '$.invoiceList[0].invoiceInfo.buyerTaxNo’,
invoiceCode nvarchar(200) ‘$.invoiceList[0].invoiceInfo.invoiceCode', invoiceDate nvarchar(200) '$.invoiceList[0].invoiceInfo.invoiceDate’,
invoiceNo nvarchar(200) ‘$.invoiceList[0].invoiceInfo.invoiceNo', invoiceType nvarchar(200) '$.invoiceList[0].invoiceInfo.invoiceType’,
salerName nvarchar(200) ‘$.invoiceList[0].invoiceInfo.salerName', salerTaxNo nvarchar(200) '$.invoiceList[0].invoiceInfo.salerTaxNo’,
detailNo int ‘$.invoiceList[0].invoiceInfo.detailList[0].detailNo', [detailList] NVARCHAR(MAX) '$.invoiceList[0].invoiceInfo.detailList’ AS JSON
)
)
SELECT A.ID,A.[Fpdm],A.[Fphm],A.[Fplx],B.*
FROM XT_FpRecord A CROSS APPLY FPINFO AS B
where A.id=70964886