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&amp;data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623630015&amp;sdata=w493K%2FxmBlLXapJFIGU8eVRXsyFA6%2FWYdsVZbAW6B7g%3D&amp;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&amp;data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623630015&amp;sdata=os1vWD%2FSabYPHQwXLWuQD39v0BDBVvlGf7nboy%2BPQ7M%3D&amp;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&amp;data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623630015&amp;sdata=D1kBfhQb%2FVv7Mi%2BjFCYqwv%2BM3FSbBxpbYV67%2BaWo44k%3D&amp;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&amp;data=02%7C01%7C%7Cab8abf3f95374f426c1f08d7938a53b8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140094623640032&amp;sdata=Cg%2BwMAQSr%2B%2FIOMlhi%2BKeD946AZXg8FscFQ8nbCVzHpM%3D&amp;reserved=0

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.