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: 30920 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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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 3 years, 1 month ago by Steven558.

Phil Parkin SSC Guru Points: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin