Re: JSON Questions -- MIDRANGE-L

I don't have access to a machine right now with SQL/JSON support so this might not work as is, but I think you will need a JSON object inside and outside the array and an array aggregate function, something like this:

select json_object(
"deceaseds":
json_arrayagg(
json_object(
"ExternalId": Nameid,
"Prefix": trim(PRFXDESC),
"FirstName": trim(firstname),
"MiddleName": trim(middlenme),
"LastName": trim(lastname),
"Suffix": trim(SUFFIXDES),
"BirthDate": trim(BIRTHDATE),
)
)
)
from namintprp

Tim.

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Art Tostaine, Jr. <atostaine@xxxxxxxxx>
Sent: 07 January 2020 16:57
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: JSON Questions

This is the start of my SQL statement. Do I put this inside JSON_OBJECT?

select json_array
(
Nameid,
trim(PRFXDESC),
trim(firstname),
trim(middlenme),
trim(lastname),
trim(SUFFIXDES),
trim(BIRTHDATE),
trim(datedeath),
trim(DISPDATE),
trim(INTRMSTAT),
)
from namintprp

On Tue, Jan 7, 2020 at 10:47 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

Yes, as long as it's wrappered in an object, in fact top level arrays have
a weird security issue so ought to be avoided
https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fstackoverflow.com%2Fquestions%2F3503102%2Fwhat-are-top-level-json-arrays-and-why-are-they-a-security-risk&data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623630015&sdata=w493K%2FxmBlLXapJFIGU8eVRXsyFA6%2FWYdsVZbAW6B7g%3D&reserved=0
:

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623630015&sdata=os1vWD%2FSabYPHQwXLWuQD39v0BDBVvlGf7nboy%2BPQ7M%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623630015&sdata=D1kBfhQb%2FVv7Mi%2BjFCYqwv%2BM3FSbBxpbYV67%2BaWo44k%3D&reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623640032&sdata=Cg%2BwMAQSr%2B%2FIOMlhi%2BKeD946AZXg8FscFQ8nbCVzHpM%3D&reserved=0