JSON Header structure

  • darkangelBDF

    SSC Veteran

    Points: 275

    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 3 months, 2 weeks ago by  darkangelBDF.
  • scdecade

    SSC Eights!

    Points: 802

    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"}]
    }

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22759

    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.

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

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