Format relational data into Json using SQL

Question

I have a query result as under

What I am looking for is the below 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"
      }
    }
  ]
}

I tried with JSON AUTO and JSON PATH but no luck.

I have tried the below

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

The second question

Can I put the result set in the below schema

CREATE TABLE TestJson(AGREEMENTID varchar(200),JsonInfo NVARCHAR(MAX));

Where AgreementId will be in a seperate column and the rest information in JSonInfo.

Sergey Menshov · Accepted Answer · 2018-04-25 07:34:29Z

4

You can try below one. I used your table name with temp table (#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')

An answer for the second question

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

edited Apr 25, 2018 at 7:34

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

answered Apr 25, 2018 at 7:04

Dipak Delvadiya

2,1122 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sergey Menshov Over a year ago

And you can add FOR JSON PATH, ROOT('AFPInfo')

Dipak Delvadiya Over a year ago

@Leran2002, yeah for root node, Thanks to remind for it.

priyanka.sarkar Over a year ago

@Dipak, very nice solution. But can we put the result set in the below schema CREATE TABLE TestJson(AGREEMENTID varchar(200),JsonInfo NVARCHAR(MAX)); where AgreementId will be in a seperate column and the rest information in JSonInfo.

Sergey Menshov Over a year ago

I've updated @Dipak's answer for your second question.

priyanka.sarkar Over a year ago

@Dipak Delvadiya, I have a follow up question (can u pls help)stackoverflow.com/questions/50120885/…

Collectives™ on Stack Overflow

Format relational data into Json using SQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related