JSON AUTO, WITHOUT_ARRAY_WRAPPER not returning all the columns

Question

Post reply

JSON AUTO, WITHOUT_ARRAY_WRAPPER not returning all the columns

sirkinghorse

SSC-Addicted

Points: 457
More actions
September 17, 2018 at 7:24 am

#372792

Hi
I have this View with these fieldsSELECT FisrtName ,LastName ,IDNumber ,Address1 ,Address2 ,Address3 ,FileType FROM myView
When I execute this view I get all the 7 fields back,
However when I execute this JSON query below, there are records that are coming back with only 3 fields back: LastName, Address3 and FileType, not all 7 fields, but there are also records where I get all 7 fields. Anybody knows why that's the case?
SELECT CA.PartnerBankingAdditionalAttributes FROM RltView.ActiveAndInactiveMembersPaymentDetails_V OV WITH (NOLOCK) CROSS APPLY ( SELECT FisrtName ,LastName ,IDNumber ,Address1 ,Address2 ,Address3 ,FileType FROM (VALUES(1))DT(x) FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER ) CA (PartnerBankingAdditionalAttributes) WHERE OV.IsActive = 1 AND isPaid = 'Y'
September 21, 2018 at 4:08 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Are any of those columns having NULL values?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

mediacommentry Old Hand Points: 348 More actions · Answer 2

sgmunson - Monday, September 17, 2018 7:42 AM
Are any of those columns having NULL values?

I would to believe so

sirkinghorse SSC-Addicted Points: 457 More actions · Answer 3

sgmunson - Monday, September 17, 2018 7:42 AM
Are any of those columns having NULL values?

Yes some are.

sgmunson SSC Guru Points: 110639 More actions · Answer 4

Have you tried adding the INCLUDE_NULL_VALUES option ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sirkinghorse SSC-Addicted Points: 457 More actions · Answer 5

sgmunson - Tuesday, September 18, 2018 6:21 AM
Have you tried adding the INCLUDE_NULL_VALUES option ?

No, I'll google it and see how to do it, thank you.

sgmunson SSC Guru Points: 110639 More actions · Answer 6

Try here: https://docs.microsoft.com/en-us/sql/relational-databases/json/include-null-values-in-json-include-null-values-option?view=sql-server-2017

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

andycadley SSCertifiable Points: 5296 More actions · Answer 7

It's worth noting that, because of the dynamic nature of JavaScript, it's typical to assume a "missing" field and a null field are equivalent and most systems would expect there to be no discernible difference (and in that case it is usually preferable to omit fields because it produces less verbose JSON).