Re: Help with JSON_TABLE

[a4g atl <a4ginatl2@xxxxxxxxx>]

Thank you Birgitta.

On Fri, Sep 9, 2022 at 2:47 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> Not sure what exactly you want to get.
> Within the JSON data I've neither seen PartNumber, nor AvailableQuantity,
> nor Price, nor Message1, nor Message2
> You want to get something that is not available, so what do you expect to
> get returned?
>
> If you want to decompose the JSON data, you can do it as follows:
> select  *
> from    JSON_TABLE('{ "CustomerAccountNumber": 1000171,
>                         "AuthKey": "fjdsklfjdkslfdj",
>                         "Parts": ["924-125"] }',
>                   '$'
>                    Columns("CustomerAccountNumber" Integer,
>                            "AuthKey"               VarChar(20),
>                            "Parts"                 VarChar(20)   Path
> '$.Parts[0]'
>                            ));
>
> It seems to me you want to get the Array Element Data only.
> If so you get them as follows:
> select  *
> from    JSON_TABLE('{ "CustomerAccountNumber": 1000171,
>                         "AuthKey": "fjdsklfjdkslfdj",
>                         "Parts": ["924-125"] }',
>                   '$'
>                    Columns("Parts"  VarChar(20)   Path '$.Parts[*]'));
>
>
> Here is also a link to the JSON Documentation/Examples on Db2 for i:
> https://www.ibm.com/docs/en/i/7.5?topic=data-using-json-table
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
> Modernization – Education – Consulting on IBM i
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of a4g
> atl
> Sent: Donnerstag, 8. September 2022 23:13
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: Help with JSON_TABLE
>
> First time trying to do this and I have not found a good guide to explain
> how this works.
>
> I am trying to get this working. I am running it in ACS's SQL. It runs and
> produces a blank screen. I do not see any errors in the joblog.
>
> select  q.*
>
> --into    :result
>
> from    JSON_TABLE('{ "CustomerAccountNumber": 1000171,
>
>                         "AuthKey": "fjdsklfjdkslfdj",
>
>                         "Parts": ["924-125"] }',
>
>                       'lax $.Parts[*]'
>
>                       Columns(
>
>                              "PartNumber"        char(20),
>
>                              "AvailableQuantity" char(20),
>
>                              "Price"             char(20),
>
>                              "Message1"          varchar(1000),
>
>                              "Message2"          varchar(1000)
>
>                            )) as Q
>
> ;
>
>
>
> Questions:
>
> How does it know what website to contact?
>
>
> Is it writing to a file or returning a string?
>
>
> If we request multiple parts, can I do a select into an array?
>
>
> TIA, Darryl FReinkel.
> --
> 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@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> 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@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com