JSON Header structure

Question

JSON Header structure

Old Hand

Points: 349

February 14, 2020 at 7:06 am

Hi there, everyone. I'm using a stored procedure to export data to a JSON file. The problem I'm having is it should have a header and then other data. But when I run the stored procedure the format is not correct. It should look something like:

RequiredJSONFormat

My SQL select code is:

SELECT CAST ((
SELECT
company.[Company]
,CONVERT(VARCHAR(10), journals.GLExtractId) + '-' + journals.DataAreaID AS MRPRedworldID
,RTRIM(journals.[journalname]) AS JournalName
,JournalDescription = 'Description'
,lines.[account]
,lines.[accountdisplayvalue]
,lines.[accounttype]
,lines.[assessablevaluetransactioncurrency]
,lines.[cashdiscount]
,lines.[cashdiscountamount]
,lines.[cashdiscountdate]
,lines.[chinesevoucher]
,lines.[chinesevouchertype]
,lines.[creditamount]
,lines.[currencycode]
,lines.[debitamount]
,lines.[defaultdimension]
,lines.[defaultdimensiondisplayvalue]
,lines.[description]
,lines.[discountpercentage]
,lines.[document]
,lines.[documentdate]
,lines.[duedate]
,lines.[exchangerate]
,lines.[exchangeratesecondary]
,lines.[invoice]
,lines.[invoicedate]
,lines.[invoiceidentification]
,lines.[invoicereceiptdate]
,lines.[isposted]
,lines.[iswithholdingcalculationenabled]
,lines.[itemsalestaxgroup]
,lines.[itemwithholdingtaxgroupcode]
,lines.[journalbatchnumber]
,lines.[linenumber]
,lines.[maximumretailprice]
,lines.[offsetaccount]
,lines.[offsetaccountdisplayvalue]
,lines.[offsetaccounttype]
,lines.[offsetdefaultdimension]
,lines.[offsetdefaultdimensiondisplayvalue]
,lines.[offsettext]
,lines.[paymentid]
,lines.[paymentmethod]
,lines.[paymentreference]
,lines.[postinglayer]
,lines.[postingprofile]
,lines.[provisionalassessment]
,lines.[quantity]
,lines.[reportingcurrencyexchrate]
,lines.[reportingcurrencyexchratesecondary]
,lines.[reversedate]
,lines.[reverseentry]
,lines.[salestaxcode]
,lines.[salestaxgroup]
,lines.[taxexemptnumber]
,lines.[taxinvoicereceiptdate]
,lines.[taxwithholdcode]
,lines.[tcsgroup]
,lines.[tdsgroup]
,lines.[text]
,lines.[transdate]
,lines.[voucher]
FROM [working].[DBRSalesDataWorking] AS company
JOIN [working].[DBRSalesDataWorking] AS journals
ON company.DivisionId = journals.DivisionId
AND company.GLExtractId = journals.GLExtractId
AND company.linenumber = journals.linenumber
AND company.DataAreaID = @DataAreaId
JOIN [working].[DBRSalesDataWorking] AS lines
ON journals.DivisionId = lines.DivisionId
AND journals.GLExtractId = lines.GLExtractId
AND journals.linenumber = lines.linenumber
AND journals.DataAreaID = @DataAreaId
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES) AS VARCHAR(MAX)) AS JSONData;

Problem with this is the code comes out as follows:

WrongJSONFormat

The problem lies with these lines:

,CONVERT(VARCHAR(10), journals.GLExtractId) + '-' + journals.DataAreaID AS MRPRedworldID
,RTRIM(journals.[journalname]) AS JournalName
,JournalDescription = 'Description'

IT seems if you don't use the fields from the selected table it doesn't come out right. It doesn't like the concatenation or the trimming.

Does anyone have any suggestions as to what I can do to rectify this? I've been wracking my brain and Googling like a mad person.

This topic was modified 4 years, 2 months ago by darkangelBDF.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2068 More actions · Answer 1

The file with the required json format doesn't appear to be working. It's hard to know without seeing it. Generally speaking concatenation and trimming shouldn't cause any issues. Maybe this example helps? Also, why the casting and conversions to varchar from nvarchar? If applied to user-entered data from the web then this could fail often. Json and unicode go together 🙂

select
  'CM16' Company,
  'GNJ' JournalName,
  (select
     'xyz123' Account,
     'vip ultra' AccountType for json path) lines
for json path, without_array_wrapper, include_null_values;

results:

{
 "Company":"CM16",
 "JournalName":"GNJ",
 "lines":[{"Account":"xyz123","AccountType":"vip ultra"}]
}

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 2

EDIT - disregard this post. Your first image showed up and appears to be answered by scdecade.

Your first image is missing so I am not sure what value you are expecting, plus we have no idea what your data looks like so it is difficult to tell you what is wrong with it.

My first GUESS was that you are seeing "JournalDescription" having a value ("SLS 20992" in your screenshot) and are expecting that to have a value of "Description"? But your description says it "doesn't like the concatenation or the trimming", yet I see that JournalName has no trailing whitespace (thus it was trimmed) and your MRPRedworldID value is concatenated successfully.

Could you post some sample data for us to work with so we can reproduce the issue and also the output you are expecting? To me, your screenshot seems to be doing concatenation and trimming as expected.

Something to note - you are using 2 different syntax styles for column alias. For readability (and consistency) sake, I do recommend using a single alias style. My preference is the "AS <alias>" style over the "<alias> =" style.

This reply was modified 4 years, 2 months ago by Mr. Brian Gale.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.