Export empty JSON with headers & footers

Question

Post reply

Export empty JSON with headers & footers

stephen.plant

SSC Eights!

Points: 811
More actions
September 28, 2021 at 1:13 pm

#3933928
Hello,
I have inherited a process that utilises an SSIS package and related stored procs that creates a JSON file.
The file we create looks like -
A number of objects in an array in a nested hierarchy.
If we have no data from our process at present we create an empty file.
What I need to do is generate a JSON with the the contents -
An empty array under the above structure.
Having only picked this up yesterday I have managed to export -
Utilising -
```
IF NOT EXISTS (SELECT * FROM @Devices)
BEGIN
SET @JSON = (
SELECT
exportedData = ISNULL((SELECT deviceData FROM @EmptyJSON FOR JSON PATH), '[]')
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
SELECT @JSON AS JSON
END
```
I am struggling to get the nested requirements as mentioned earlier.
All help greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

ZZartin SSC-Dedicated Points: 30898 More actions · Answer 1

Well you could just check the number results in the return and if it's zero hardcode the desired output.

Json is tricky like that because functionally both of those represent the same data.

Phil Parkin SSC Guru Points: 246716 More actions · Answer 2

ZZartin wrote:

Well you could just check the number results in the return and if it's zero hardcode the desired output.
Json is tricky like that because functionally both of those represent the same data.

I agree. Save yourself the pain and hard-code the entire string.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Steven558 Grasshopper Points: 11 More actions · Answer 3

ZZartin wrote:

Well you could just check the number Upsers results in the return and if it's zero hardcode the desired output.
Json is tricky like that because functionally both of those represent the same data.

Probably not - blob objects can't be updated although you can combine old and new blobs in a specified order. Note the msSaveOrOpenBlob method is deprecated and may stop working at any time, and hence is really only suitable for supporting legacy systems.

This reply was modified 2 years, 7 months ago by Steven558.

Phil Parkin SSC Guru Points: 246716 More actions · Answer 4

Steven558 wrote:

ZZartin wrote:
Well you could just check the number results in the return and if it's zero hardcode the desired output.
Json is tricky like that because functionally both of those represent the same data.
Probably not - blob objects can't be updated although you can combine old and new blobs in a specified order. Note the msSaveOrOpenBlob method is deprecated and may stop working at any time, and hence is really only suitable for supporting legacy systems.

I see no mention of BLOBs earlier in this thread, so please clarify its relevance here.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.