Re: JSON Questions

...that's the JSON_ARRAYAGG(...) function, normally that's what you want if you are going to send or store this data as a JSON object. In others words, you want to aggregate a series of DB rows into an array, which is part of a single JSON object, otherwise there's not much point in having an array. Normally you'd do a GROUP BY and/or WHERE clause to include only the items in the array you need per object.

If you really want one row per DB row then just change the JSON_ARRAYAGG to JSON_ARRAY, but then you'll just get a bunch of JSON objects with on array element in, which is probably not what you need.

Tim.

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

Thank you. This worked. Why does it create one long string instead of a
row for each name?

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 lcadtalib.namintprp fetch first 100 rows only
On Tue, Jan 7, 2020 at 11:32 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

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

--
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%7C7c06f5a0a2234a12d5a808d79390baae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140122116637151&amp;sdata=HxOlLS6HAm%2FRcGV32uXXnm0gyAWQEZljE4FsvUvSzzk%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%7C7c06f5a0a2234a12d5a808d79390baae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140122116637151&amp;sdata=Q0oyQImKjYOMhuj20iFKdeAG6sdBtujM9QrqgXBuYlc%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%7C7c06f5a0a2234a12d5a808d79390baae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140122116637151&amp;sdata=nz2aQvBoiBtTi%2B6j1kYZ7OcLB7AlocKALarR5GOmaW8%3D&amp;reserved=0