Programming Languages on Power

 View Only

 JSON_QUERY Formatting Second Parameter (Json Path)

Doug Freeman's profile image
Doug Freeman posted 07/29/26 10:01 AM

I'm retrieving a ship file from Fedex and I need to retrieve the file data after "encodedLabel". I've posted part of the ship file below. I believe I can use JSON_QUERY to accomplish this but I do not know how to format the second parameter to do this.

I can retrieve the 'transactionId' as noted below and that is where my success ends. I'm guessing I can do this using JSON_QUERY but how would the Json Path parameter be formatted? This is in an SQLRPGLE. Fields FEDEXRESP and FEDEXPDF are 'sqltype(clob:2000000)'. I've never used JSON_QUERY before. Thanks in advance.

Doug Freeman

0204.00    Exec Sql
0205.00        set :fedexresp = get_clob_from_file('ibmifs/upu178shiprm.txt');
0206.00    Exec Sql
0207.00        set :fedexpdf = json_query(:fedexresp,'$.transactionId');

Fedex Response File

{"transactionId":"cabe21a2-8eb3-4359-9be7-ae841fbcac85","output":{"transactionShipments":[{"alerts":[{"code":"SHIPMENT.SHIPDATESTAMP.INVALID","message":"Requested shipDatestamp is prior to current date.","alertType":"WARNING","parameterList":[]}],"masterTrackingNumber":"794845084209","serviceType":"STANDARD_OVERNIGHT","shipDatestamp":"2026-07-27","serviceName":"FedEx Standard Overnight®","pieceResponses":[{"masterTrackingNumber":"794845084209","deliveryDatestamp":"2026-07-28","trackingNumber":"794845084209","additionalChargesDiscount":0.0,"netRateAmount":0.0,"netChargeAmount":0.0,"netDiscountAmount":0.0,"packageDocuments":[{"contentType":"LABEL","copiesToPrint":1,"encodedLabel":"JVBERi0xLjQKMSAwIG9iago8PAovVHlwZSAvQ2F0YWxvZwovUGFnZXMgMyAwIFIKPj4KZW5kb2JqCjIgMCBvYmoKPDwKL1R5cGUgL091dGxpbmVzCi9Db3VudCAwCj4+CmVuZG9iagozIDAgb2JqCjw8Ci9UeXBlIC9QYWdlcwovQ291bnQgMQovS2lkcyBbMTggMCBSXQo+PgplbmRvYmoKNCAwIG9iagpbL1BERiAvVGV4dCAvSW1hZ2VCIC9JbWFnZUMgL0ltYWdlSV0KZW5kb2JqCjUgMCBvYmoKPDwKL1R5cGUgL0ZvbnQKL1N1YnR5cGUgL1R5cGUxCi9CYXNlRm9udCAvSGVsdmV0aWNhCi9FbmNvZGluZyAvTWFjUm9tYW5FbmNvZGluZwo+PgplbmRvYmoKNiAwIG9iago8PAovVHlwZSAvRm9udAovU3VidHlwZSAvVHlwZTEKL0Jhc2VGb250IC9IZWx2ZXRpY2EtQm9sZAovRW5jb2RpbmcgL01hY1JvbWFuRW5jb2RpbmcKPj4KZW5kb2JqCjcgMCBvYmoKPDwKL1R5cGUgL0ZvbnQKL1N1YnR5cGUgL1R5cGUxCi9CYXNlRm9udCAvSGVsdmV0aWNhLU9ibGlxdWUKL0VuY29kaW5nIC9NYWNSb21hbkVuY29kaW5nCj4.............
.
.
.
wMDAwIG4gCjAwMDAwMDA4MjkgMDAwMDAgbiAKMDAwMDAwMDkzNCAwMDAwMCBuIAowMDAwMDAxMDQzIDAwMDAwIG4gCjAwMDAwMDExNDQgMDAwMDAgbiAKMDAwMDAwMTI0NCAwMDAwMCBuIAowMDAwMDAxMzQ2IDAwMDAwIG4gCjAwMDAwMDE0NTIgMDAwMDAgbiAKMDAwMDAwMTYyMiAwMDAwMCBuIAowMDAwMDAyMDg4IDAwMDAwIG4gCjAwMDAwMDYzNjMgMDAwMDAgbiAKMDAwMDAwNzAxMCAwMDAwMCBuIAowMDAwMDA3MjcxIDAwMDAwIG4gCjAwMDAwMDg5NDYgMDAwMDAgbiAKMDAwMDAxMzc2MCAwMDAwMCBuIAp0cmFpbGVyCjw8Ci9JbmZvIDE3IDAgUgovU2l6ZSAyNQovUm9vdCAxIDAgUgo+PgpzdGFydHhyZWYKMzgyNjEKJSVFT0YK","docType":"PDF"}],"currency":"USD","customerReferences":[],"codcollectionAmount":0.0,"baseRateAmount":152.49}],"shipmentAdvisoryDetails":{},"completedShipmentDetail":{"usDomestic":true,"carrierCode":"FDXE","masterTrackingId":{"trackingIdType":"FEDEX","formId":"0201","trackingNumber":"794845084209"},"serviceDescription................................

Daniel Gross's profile image
Daniel Gross IBM Champions posted 07/29/26 02:35 PM

Hi Doug,

if I read the JSON document correctly, it's not very practical to read the contents of the document with JSON_QUERY.

Your problem are the many JSON arrays with []-notation. This means, the elements between the square brackets are repeatable. It's not guaranteed that you only receive ONE label back from UPS - according to the JSON structure, you can receive multiple labels/documents in the response.

So I would try to process the whole document with JSON_TABLE instead. Here I have a small example for you:

select *
from json_table(
    '{"transactionId":"cabe21a2-8eb3-4359-9be7-ae841fbcac85","output"...',
    'lax $' --> JSON "root"
    columns (
        transaction_id varchar(50) path 'lax $.transactionId',
        nested path 'lax $.output.transactionShipments[*]'
        columns (
            master_tracking_number varchar(50) path 'lax $.masterTrackingNumber',
            service_type varchar(50) path 'lax $.serviceType',
            nested path 'lax $.pieceResponses[*]'
            columns (
                tracking_number varchar(50) path 'lax $.trackingNumber',
                nested path 'lax $.packageDocuments[*]' --> here are the labels
                columns (
                    encoded_label varchar(32000) path 'lax $.encodedLabel', --> probably base64 encoded
                    doc_type varchar(50) path 'lax $.docType',
                    copies_to_print integer path 'lax $.copiesToPrint'
                )
            )
        )
    )
);

I hope the code is somehow clear - the first parameter of JSON_TABLE has to be your JSON document. And you can use the SELECT in a SQL cursor and read all lines with FETCH.

If you insist in reading the label content with JSON_QUERY, the JSON path to the first(!) label of the first(!) parcel should be:

    'lax $.output.transactionShipments[0].pieceResponses[0].packageDocuments[0].encodedLabel'  

HTH and kind regards,

Daniel

Related Content