Re: JSON Questions

Where does your location data come from, the same file? You probably need 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,
json_object('Location':
'ExternalId': Locationid???,
'Level1': trim(...???),
'Level2': trim(...???),
'Level3': trim(...???)
)
)
)
)

from lcadtalib.namintprp

fetch first 100 rows only

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

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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=i8SYvFT3g2N7yW96%2BwlxV8huQrwgyCNOwe1fkwfC%2FL4%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=s0HAP0xyHgZ0VjJoGszfHRrlj36lq%2BL4l1rzBnqlGsc%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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=Pn4IV6Kkc6IK8mj%2F48MtJTGd0jM1wkHw%2Fw21DwPXN2U%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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=i8SYvFT3g2N7yW96%2BwlxV8huQrwgyCNOwe1fkwfC%2FL4%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=s0HAP0xyHgZ0VjJoGszfHRrlj36lq%2BL4l1rzBnqlGsc%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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=Pn4IV6Kkc6IK8mj%2F48MtJTGd0jM1wkHw%2Fw21DwPXN2U%3D&amp;reserved=0

--
Art Tostaine
--
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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078599999&amp;sdata=i8SYvFT3g2N7yW96%2BwlxV8huQrwgyCNOwe1fkwfC%2FL4%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078609954&amp;sdata=3vBt3%2BdfyNhksHEiFQwHL%2BN1iIROR6mto5I91iSWyyU%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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7C786ae863200445e77d6008d7939cd3c1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140174078609954&amp;sdata=xyF89ge80EUSlK0xMSn3I2KYRGNfeyaHTe7IGah9f%2FU%3D&amp;reserved=0