Re: JSON Questions -- MIDRANGE-L

Ok that makes sense. Thank you.

I need to insert ANOTHER Object for location. I copied what you had done
but I'm getting a syntax error.

This is the sample, I need to insert it after birthdate:
"Location": { // Note that this is an object, not an
array.
"ExternalId": 2, // Your LocationExternalId - Required if a
location is provided (but that is not required necessarily).
"Level1": "",
"Level2": "",
"Level3": "",
}

I tried this but I'm getting an error that ) is not valid. I've been
looking at it for awhile.
select json_object('':
json_arrayagg(
json_object(
'ExternalId': Nameid,
'Prefix': trim(PRFXDESC),
'FirstName': trim(firstname),
'MiddleName': trim(middlenme),
'LastName': trim(lastname),
'Suffix': trim(SUFFIXDES),
'BirthDate': trim(BIRTHDATE),
json_object('Location':
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:56 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

...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&data=02%7C01%7C%7C7c06f5a0a2234a12d5a808d79390baae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140122116637151&sdata=HxOlLS6HAm%2FRcGV32uXXnm0gyAWQEZljE4FsvUvSzzk%3D&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&data=02%7C01%7C%7C7c06f5a0a2234a12d5a808d79390baae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140122116637151&sdata=Q0oyQImKjYOMhuj20iFKdeAG6sdBtujM9QrqgXBuYlc%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://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7C7c06f5a0a2234a12d5a808d79390baae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140122116637151&sdata=nz2aQvBoiBtTi%2B6j1kYZ7OcLB7AlocKALarR5GOmaW8%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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com