Re: JSON Questions -- MIDRANGE-L

...sorry, realised there was a mistake in that, it should be thins:

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,
'Location': json_object( -- <-- fixed here
'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 Tim Fathers <X700-IX2J@xxxxxxxxxxx>
Sent: 07 January 2020 22:12
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903276971&sdata=1iNiPIFY4IzeazDOcPlqeHpHygum6G9n9o5zspE6hfE%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903276971&sdata=ej98IqRMeM4adN54t1MqBFDinUwbBDQn88iEMDQB89Y%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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903286982&sdata=u99qeCBWtgn23XyByaGkI54m2VPxI1weOBdfXjoxtt4%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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903286982&sdata=c8%2Fpjg3Xmb1sDtx%2FkKLzjDC2yq%2BSunlnCsWXhmuFVJg%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903286982&sdata=zuYkMRtJ8GIByvSjsxpnNs0ytEZF4P%2BRN7na%2B5SVmTQ%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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903286982&sdata=u99qeCBWtgn23XyByaGkI54m2VPxI1weOBdfXjoxtt4%3D&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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903286982&sdata=c8%2Fpjg3Xmb1sDtx%2FkKLzjDC2yq%2BSunlnCsWXhmuFVJg%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903296987&sdata=d480qo3KevmOBFmPY%2FU72TEIEur%2FkcMKntyrTHO6UBE%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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903296987&sdata=pTCPSwdTV5dhYWuWFy3l%2FwbTLhw3XX%2FWqbBEtOAhUnk%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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903296987&sdata=WUuMqdYxvyVpSze4R1OkK0y9Fo%2BjcQXYKfc7YCQM%2BFM%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903296987&sdata=d480qo3KevmOBFmPY%2FU72TEIEur%2FkcMKntyrTHO6UBE%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://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&data=02%7C01%7C%7C83546e34bdb3433952f008d793b665c5%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637140283903296987&sdata=pTCPSwdTV5dhYWuWFy3l%2FwbTLhw3XX%2FWqbBEtOAhUnk%3D&reserved=0