json - 使用 SQL 将关系数据格式化为 Json
问题描述
我有一个查询结果如下
我正在寻找的是下面的json
{
"AFPInfo": [
{
"AgreementId": "100",
"LoanAccounts": {
"ProductGroup": "e",
"Product": "CCOO3",
"State": "karnataka"
},
"FeedbackInfo": {
"DispositionCode": "PTP",
"FeedbackDate": "24/7/2017"
},
"PaymentInfo": {
"Receipt No": "12345",
"ReceiptDate": "26/7/2017",
"Amount": "2000"
}
},
{
"AgreementId": "11960600000203",
"LoanAccounts": {
"ProductGroup": "e",
"Product": "CCOO3",
"State": "karnataka"
},
"FeedbackInfo": {
"DispositionCode": "PTP",
"FeedbackDate": "24/7/2017"
},
"PaymentInfo": {
"Receipt No": "12345",
"ReceiptDate": "26/7/2017",
"Amount": "2000"
}
}
]
}
我尝试使用JSON AUTO和JSON PATH但没有运气。
我已经尝试过以下
CREATE TABLE [dbo].[tmpjson](
[AGREEMENTID] [nvarchar](200) NULL,
[ProductGroup] [nvarchar](max) NULL,
[PRODUCT] [nvarchar](max) NULL,
[STATE] [nvarchar](max) NULL,
[DispositionCode] [nvarchar](max) NULL,
[FeedbackDate] [datetime2](7) NULL,
[Receipt Date] [nvarchar](100) NULL,
[Amount] [decimal](18, 2) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'100', N'e', N'VW', N'karnataka', NULL, NULL, N'2319049960', CAST(6780.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'11960600000203', NULL, N'LA503', N'MADHYA PRADESH', NULL, NULL, N'1113123587', CAST(100.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'12930600001066', NULL, N'LA152', N'MADHYA PRADESH', N'PTP', CAST(N'2017-10-04T17:00:09.7748745' AS DateTime2), N'11101934179', CAST(10.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'24950600005282', NULL, N'LA158', N'WEST BENGAL', N'PTP', CAST(N'2017-10-09T22:00:48.6158707' AS DateTime2), N'9215640457', CAST(6494455.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040400001330', NULL, N'OD003', N'GOA', N'PR', CAST(N'2017-10-13T12:07:44.2317198' AS DateTime2), N'2000000170', CAST(200.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040600005404', NULL, NULL, NULL, NULL, CAST(N'2017-10-04T13:02:04.4763393' AS DateTime2), N'2820548234', CAST(1.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040600006704', NULL, N'LA153', N'KERALA', N'PTP', CAST(N'2017-10-09T18:40:48.7068467' AS DateTime2), N'42027871', CAST(5800.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040600007139', NULL, N'LA153', N'ANDHRA PRADESH', NULL, NULL, N'10183750534', CAST(222.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040600008572', NULL, N'LA153', N'RAJASTHAN', NULL, NULL, N'10121815403', CAST(5000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040600008961', NULL, N'LA153', N'WEST BENGAL', N'PURES', CAST(N'2017-10-04T18:43:13.1801773' AS DateTime2), N'6161955816', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpjson] ([AGREEMENTID], [ProductGroup], [PRODUCT], [STATE], [DispositionCode], [FeedbackDate], [Receipt Date], [Amount]) VALUES (N'29040600010895', NULL, N'LA124', N'.', N'BPTP', CAST(N'2017-09-28T21:53:04.4219814' AS DateTime2), N'4172139436', CAST(1001.00 AS Decimal(18, 2)))
GO
select
-- Loan account Information
AGREEMENTID,
ProductGroup,
PRODUCT,
STATE,
-- Feedback Info
DispositionCode,
FeedbackDate,
-- Payment Info
[Receipt No]
[Receipt Date],
Amount
from tmpjson
FOR JSON PATH -- FOR JSON AUTO
第二个问题
我可以将结果集放在下面的架构中吗
CREATE TABLE TestJson(AGREEMENTID varchar(200),JsonInfo NVARCHAR(MAX));
WhereAgreementId
将在单独的列中,其余信息在JSonInfo
.
解决方案
你可以试试下面的一个。我将您的表名与临时表(#tmpjson)一起使用。
SELECT
-- Loan account Information
AGREEMENTID,
ProductGroup AS 'LoanAccounts.ProductGroup',
PRODUCT AS 'LoanAccounts.Product',
STATE AS 'LoanAccounts.State' ,
-- Feedback Info
DispositionCode AS 'FeedbackInfo.DispositionCode',
FeedbackDate AS 'FeedbackInfo.FeedbackDate',
--[Receipt No] AS 'PaymentInfo.ReceiptNo'
[Receipt Date] AS 'PaymentInfo.ReceiptDate',
Amount AS 'PaymentInfo.Amount'
FROM #tmpjson FOR JSON PATH, ROOT('AFPInfo')
第二个问题的答案
INSERT TestJson(AGREEMENTID,JsonInfo)
SELECT
a.AGREEMENTID,
CAST(
(
SELECT
-- Loan account Information
AGREEMENTID,
ProductGroup AS 'LoanAccounts.ProductGroup',
PRODUCT AS 'LoanAccounts.Product',
STATE AS 'LoanAccounts.State' ,
-- Feedback Info
DispositionCode AS 'FeedbackInfo.DispositionCode',
FeedbackDate AS 'FeedbackInfo.FeedbackDate',
--[Receipt No] AS 'PaymentInfo.ReceiptNo'
[Receipt Date] AS 'PaymentInfo.ReceiptDate',
Amount AS 'PaymentInfo.Amount'
FROM tmpjson j
WHERE j.AGREEMENTID=a.AGREEMENTID
FOR JSON PATH, ROOT('AFPInfo')
) AS nvarchar(MAX)) JsonInfo
FROM
(
SELECT DISTINCT AGREEMENTID
FROM tmpjson
) a
推荐阅读
- ios - SwiftUI - 子视图的可变@FocusState?
- javascript - 历史钩子 React 发出警告。这是一个无操作,但它表明您的应用程序中存在内存泄漏
- java - 在 Hazelcast(嵌入式)执行器服务线程运行时防止 JVM 关闭
- python - 清除循环中的 Python 图
- python - ArUco 标记检测 ID 无
- javascript - 跟踪按下了哪个按钮?
- mysql - 为什么我可以在 MySQL 的日期时间字段中插入字符串
- javascript - Webpack - React,如何从脚本标签传递数据
- html - css 文件不能与 html 文件一起使用(即使 css 文件已与 html 文件链接)?
- javascript - 显示 README.md 文件内容