T-SQL format JSON output with schema

Transact-SQL
1

Hi there, everyone.

I'm busy with a process where we extract data from a SQL table to JSON format. My current struggle is how to get the schema to show properly. It should look something like this.

{
"company": "mrpg",
"journals": [
{
"MRPRedWorldId": "Id1",
"JournalName": "GRN",
"JournalDescription": "Description",
"lines": [
{

This is the code I'm using in a stored procedure:
SELECT
'mrpg' AS company,
MRPRedworldId = journals.GLExtractId,
JournalName = RTRIM(journals.JournalName),
JournalDescription = 'Description',
lines.LineNumber,
lines.CreditAmount,
lines.AccountDisplayValue,
lines.[Description],
lines.TransDate,
lines.CurrencyCode,
lines.DebitAmount,
lines.AccountType
FROM [working].[DBRSalesWorking] AS company
JOIN [working].[DBRSalesWorking] AS journals
ON company.DivisionId = journals.DivisionId
AND company.GLExtractId = journals.GLExtractId
JOIN [working].[DBRSalesWorking] AS lines
ON journals.DivisionId = lines.DivisionId
AND journals.GLExtractId = lines.GLExtractId
AND journals.LineNumber = lines.LineNumber
WHERE lines.GLExtractId = 24447
FOR JSON PATH, ROOT ('company')

The result is:

{"
company":[{
"company":"mrpg",
"MRPRedworldId":24447,
"JournalName":"GNJ",
"JournalDescription":"Description",
"LineNumber":1,
"CreditAmount":129992.43,
"AccountDisplayValue":"31005030-800000-10280",
"Description":"DBR 393 CASH TAKEN INC VAT EX DBR",
"TransDate":"20200110",
"CurrencyCode":"ZAR",
"DebitAmount":0.00,
"AccountType":"Ledger"}

How can I change the code so that the schema and everything else displays correctly?

2

Can you provide us with the CREATE TABLE and INSERT INTO scripts to create the tables and to put some sample data into that table. Based on the sample data you provided, give the result-set you expect.

3

Hey there. After MUCH wrestling and googling I've managed to get everything working, in the right format, etc. Thanks for taking the time to help me.

4

(I don't have the impression I did anything to help tbh)

Good to hear! :+1:
Could you post the result? You'll help people facing the same problem you had.

5

When working with JSON, I use OpenJSON to parse it into a table. It works beautifully. Here are some really good examples where you can demoralize a JSON with arrays. https://sqlhints.com/2015/11/22/openjson-function-in-sql-server-2016

1 Like