Re: JSON Questions

...ah, if using the colon format then the keyname must be a string, so single quotes, not double as in my example. Apologies.

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

Thank you Tim. The select doesn't like the colon after Deceasedds, I'm
trying to figure out the syntax.

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

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.

--
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://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7C51fedeeb30f04ccaec3b08d7938dd920%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140109742763112&amp;sdata=dfSH4Itu6XSJAXjsGuGEz0WdgQMv1pENOII3et2RS5c%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C51fedeeb30f04ccaec3b08d7938dd920%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140109742763112&amp;sdata=onNc8wYRlwpWOjBL9aX8yBKA433T8tC%2F4HATTVmvbi4%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://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7C51fedeeb30f04ccaec3b08d7938dd920%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140109742763112&amp;sdata=afosd5FiF3ij2io7qipdY8z8ctubbUekufCtxap0TDw%3D&amp;reserved=0