N/A json in connector to variable

[AntoninoProva]

Hi,
I would need to take only two fields from this json file, this is the json output from a connector
{
"REST1": {
"response": "Paese,Valuta,Codice ISO,Codice UIC,Euro,Dollaro USA,Convenzione di cambio contro Dollaro,Convenzione di cambio contro Euro,Data di riferimento (CET)AFGHANISTAN,Afghani,AFN,115,84.8367,79.0429,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ALBANIA,Lek,ALL,047,106.34,99.08,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ALGERIA,Dinaro Algerino,DZD,106,147.1313,137.0831,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ANGOLA,Readjustado Kwanza,AOA,087,891.687,830.790,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ANTIGUA E BARBUDA,Dollaro Caraibi Est,XCD,137,2.8979,2.70,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ANTILLE OLANDESI,Fiorino Antille Olandesi,ANG,132,1.9212,1.79,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ARABIA SAUDITA,Riyal Saudita,SAR,075,4.0249,3.75,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ARGENTINA,Peso Argentina,ARS,216,375.6301,349.9768,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ARMENIA,Dram Armenia,AMD,246,413.76,385.50,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13ARUBA,Fiorino Aruba,AWG,211,1.9212,1.79,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13AUSTRALIA,Dollaro Australiano,AUD,109,1.6762,0.6403,Quantita' di Dollari per 1 unita' di valuta estera,Quantita' di valuta estera per 1 Euro,2023-09-13AZERBAIGIAN,Manat Azerbaigian (nuovo),AZN,271,1.8246,1.7000,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BAHAMAS,Dollaro Bahama,BSD,135,1.0733,1,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BAHRAIN,Dinaro Bahrain,BHD,136,0.404,0.376,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BANGLADESH,Taka,BDT,174,117.7947,109.7500,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BARBADOS,Dollaro Barbados,BBD,195,2.146600,2,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BELIZE,Dollaro Belize,BZD,152,2.1466,2,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BENIN,Franco CFA,XOF,209,655.957,611.159,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BERMUDA,Dollaro Bermuda,BMD,138,1.0733,1,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13BHUTAN,Ngultrum,BTN,180,89.0805,82.9968,Quantita' di valuta estera per 1 Dollaro,Quantita' di valuta estera per 1 Euro,2023-09-13..........


how can I do?

 

[DaveWagoner]

Scripting and the mysteries of using Groovy to parse out JSON are a perfect use case for ChatGPT. I've saved so much time in the last few weeks presenting my issues to chatGPT compared to googling solutions (to me, chatGPT is "super google" with code issues). HOWEVER, it looks like you are getting a NASTY csv string inside of a single JSON field. It still might be worth a try to toss your input file to ChatGPT, and work with it to figure out a script that will get what you want. Here's one where I had chatGPT troubleshoot a script. We went back and forth a few times, but at the end it gave me good results that work, with the added bonus that it explains the code.

It is important when using chatGPT for things like parsing through your content that you "anonymize" the content, so that you're not feeding it proprietary or confidential information. I'm sure that would violate some policies at your company, so you want to put the time in to anonymize your information content.

 

[Kevin Long]

I think you have a couple of options. 1) As part of the API you may be able to specify the format of the results. The service you are calling appears to be returning csv, and the the orchestrator sticks into a json format. Check the API to see if you can specify getting actual JSON back. If you get JSON back, then it should be reasonably straight forward to map it. 2) If you start limited to csv, then I would parse the csv and turn it into a JSON object in the manipulate objects section of your Connector. Are you using JRuby or Groovy for scripting?

 

[cribeiro]

Hi, I guess you can map the output as you need it. An idea is to save this json file locally, open w/ Notepad++, download the plugin for Json, then open the json tree viewer and make sure on the array (line) you have the fields you want to read/use. Json arrays always starts from 0 sometimes it is 0.rest1.field1 or it could be rest1.0.field1, this is how you need to setup the output in the orchestration and name the variable as you want to use in the orchestration.

 

[AntoninoProva]

I think you have a couple of options. 1) As part of the API you may be able to specify the format of the results. The service you are calling appears to be returning csv, and the the orchestrator sticks into a json format. Check the API to see if you can specify getting actual JSON back. If you get JSON back, then it should be reasonably straight forward to map it. 2) If you start limited to csv, then I would parse the csv and turn it into a JSON object in the manipulate objects section of your Connector. Are you using JRuby or Groovy for scripting?

i use Groovy for scripting

 

[AntoninoProva]

I managed to get the output in json but I can't correctly declare the Orchestrator output variables (for example if I want to put everything in an array to insert them with module requests). This is a part of the ouput:
{
"REST1": {
"resultsInfo": {
"totalRecords": 173,
"timezoneReference": "Le date sono riferite al fuso orario dell'Europa Centrale",
"notice": "Quantita' di valuta estera per 1 Euro"
},
"latestRates": [
{
"country": "AFGHANISTAN",
"currency": "Afghani",
"isoCode": "AFN",
"uicCode": "115",
"eurRate": "84.2595",
"usdRate": "79.0575",
"usdExchangeConvention": "Quantita' di valuta estera per 1 Dollaro",
"usdExchangeConventionCode": "C",
"referenceDate": "2023-09-15"
},
{
"country": "ALBANIA",
"currency": "Lek",
"isoCode": "ALL",
"uicCode": "047",
"eurRate": "106.99",
"usdRate": "100.38",
"usdExchangeConvention": "Quantita' di valuta estera per 1 Dollaro",
"usdExchangeConventionCode": "C",
"referenceDate": "2023-09-15"
}

]
}
}

 

[DaveWagoner]

Return raw output from this rest connector step. That output becomes input to a subsequent groovy step. Use JSONSlurper and ChatGPT to arrive at what you need. For instance

import groovy.json.JsonSlurper

def jsonText = '''{
    "REST1": {
        "resultsInfo": {
            "totalRecords": 173,
            "timezoneReference": "Le date sono riferite al fuso orario dell'Europa Centrale",
            "notice": "Quantita' di valuta estera per 1 Euro"
        },
        "latestRates": [
            {
                "country": "AFGHANISTAN",
                "currency": "Afghani",
                "isoCode": "AFN",
                "uicCode": "115",
                "eurRate": "84.2595",
                "usdRate": "79.0575",
                "usdExchangeConvention": "Quantita' di valuta estera per 1 Dollaro",
                "usdExchangeConventionCode": "C",
                "referenceDate": "2023-09-15"
            },
            {
                "country": "ALBANIA",
                "currency": "Lek",
                "isoCode": "ALL",
                "uicCode": "047",
                "eurRate": "106.99",
                "usdRate": "100.38",
                "usdExchangeConvention": "Quantita' di valuta estera per 1 Dollaro",
                "usdExchangeConventionCode": "C",
                "referenceDate": "2023-09-15"
            }
        ]
    }
}'''

def json = new JsonSlurper().parseText(jsonText)

def flattenedArray = json.REST1.latestRates.collect {
    [
        isoCode: it.isoCode,
        eurRate: it.eurRate,
        usdRate: it.usdRate,
        referenceDate: it.referenceDate
    ]
}

println flattenedArray

Reference: https://chat.openai.com/share/72c863c8-3506-4798-a57c-8886a2779bec

 

[DaveWagoner]

Oh, and figuring out how to get an array from groovy output to the output of the groovy script component is a minor lesson in insanity I recommend learning this step the hard way, and then documenting the hell out of it so you don't lose the knowledge

 

[cribeiro]

You try to map the Rest Output like this:
Rest1.latestRates --- turn Array on
Rest1.latestRates. <<arrays >> isoCode >> variable as isoCode
Rest1.latestRates. <<arrays >> euRate >> variable as euRate
Rest1.latestRates. <<arrays >> usRate >> variable as usRate
+ fields in the array you want to use.

 

[AntoninoProva]

I used this groovy code to insert data into an array, but it doesn't show me the data I should put in variables. Am I doing something wrong?

 

[DaveWagoner]

You are working within JDE's implementation of groovy for a scripting component so you need to map in your json to the script not only at the orchestration level, but also within the script.

Let's call the input var to the scripting component "apiIn". With that in mind, your script should be modified as such:

String apiIn = (String)inputMap.get("apiIn");

And also add apiIn to your input var area in the groovy component so you can test. You should be able to paste the entire API return into the "Test Value" field, and then test your script as you tweak it.

I happened to be working on an API parser script so some details might be a bit different, but this is what it looks like:

 

[AntoninoProva]

You are working within JDE's implementation of groovy for a scripting component so you need to map in your json to the script not only at the orchestration level, but also within the script.

Let's call the input var to the scripting component "apiIn". With that in mind, your script should be modified as such:

String apiIn = (String)inputMap.get("apiIn");

And also add apiIn to your input var area in the groovy component so you can test. You should be able to paste the entire API return into the "Test Value" field, and then test your script as you tweak it.

I happened to be working on an API parser script so some details might be a bit different, but this is what it looks like:

View attachment 19804

I tried to follow what you told me but it keeps giving me error.

 

[DaveWagoner]

Return raw output from this rest connector step. That output becomes input to a subsequent groovy step. Use JSONSlurper and ChatGPT to arrive at what you need. For instance

It looks like you're in modify body of your connector? I really suggest you leave the connector body alone and do this stuff in its own groovy step.

 

[AntoninoProva]

I entered the code here, where should I enter it?

 

[DaveWagoner]

This is the pattern I recommend due to visibility and ease of testing. The REST call first, return raw data to the next step, where you do your manipulation of the rest output.



On the rest call transformations screen, select "return raw output"


On the Groovy Script transformations screen, you should see the raw output from REST step to pass in.



The groovy script in a script component allows for rapid testing and tweaking without needing to call the orch every time. It shoudl speed up your process quite a bit. You copy the output of the API return into the "test value" of your input var, and can click Test all you want as you change your script. Because this is a script component you need to declare your input and output variables. The script has the "template" declarations already in it, commented out, when you create it.

 

[Kevin Long]

I managed to get the output in json but I can't correctly declare the Orchestrator output variables (for example if I want to put everything in an array to insert them with module requests). This is a part of the ouput:
{
"REST1": {
"resultsInfo": {
"totalRecords": 173,
"timezoneReference": "Le date sono riferite al fuso orario dell'Europa Centrale",
"notice": "Quantita' di valuta estera per 1 Euro"
},
"latestRates": [
{
"country": "AFGHANISTAN",
"currency": "Afghani",
"isoCode": "AFN",
"uicCode": "115",
"eurRate": "84.2595",
"usdRate": "79.0575",
"usdExchangeConvention": "Quantita' di valuta estera per 1 Dollaro",
"usdExchangeConventionCode": "C",
"referenceDate": "2023-09-15"
},
{
"country": "ALBANIA",
"currency": "Lek",
"isoCode": "ALL",
"uicCode": "047",
"eurRate": "106.99",
"usdRate": "100.38",
"usdExchangeConvention": "Quantita' di valuta estera per 1 Dollaro",
"usdExchangeConventionCode": "C",
"referenceDate": "2023-09-15"
}

]
}
}

If you are on 9.2.5.2 or higher, you can map the array output directly out of your connector. No coding required. Please see the following from the Orchestrator manual.
https://docs.oracle.com/en/applicat...rom-connectors-release-9-2-5-2.html#u10036567

 

[Kevin Long]

This is the pattern I recommend due to visibility and ease of testing. The REST call first, return raw data to the next step, where you do your manipulation of the rest output.

View attachment 19812

On the rest call transformations screen, select "return raw output"
View attachment 19813

On the Groovy Script transformations screen, you should see the raw output from REST step to pass in.

View attachment 19814

The groovy script in a script component allows for rapid testing and tweaking without needing to call the orch every time. It shoudl speed up your process quite a bit. You copy the output of the API return into the "test value" of your input var, and can click Test all you want as you change your script. Because this is a script component you need to declare your input and output variables. The script has the "template" declarations already in it, commented out, when you create it.

View attachment 19816

I certainly understand the approach and why you are suggesting it. This will work and is definitely easier to debug. However, the one caution I would have is on performance. The code in the connector is already there and running each time the connector is called. If you need to process the output from a REST API call, the manipulate output section would be the better place to do it from a performance perspective. By handing off the JSON to a custom request, you end up doing the work twice. Parse the JSON response and convert it back to JSON in the manipulate response section, and then again in the Custom Request.

In this example, I think the difference would be negligible, but if I were calling an API repeatedly (hundreds or thousands of times) it would certainly be a concern.

I’m only pointing this out because I do hear people often asking about Orchestrator performance, and this is an example of a trade-off between ease of development and optimizing performance.