How to extract multiple values from a json response using extract variables policy

amit12pce
Participant II

My extract variable policy :

<ExtractVariables name="ParsehotelResponse"> 
  <Source>response</Source> 
  <VariablePrefix>response-parse</VariablePrefix> 
  <JSONPayload> 
    <Variable name="Hotel Name" type="Array"> 
      <JSONPath>$.results.name[*]</JSONPath> 
    </Variable> 
    <Variable name="Hotel Rating" type="Array"> 
      <JSONPath>$.results.rating[*]</JSONPath> 
    </Variable> 
    <Variable name="Hotel Address" type="Array"> 
      <JSONPath>$.results.formatted_address[*]</JSONPath> 
    </Variable> 
  </JSONPayload> 
</ExtractVariables>

JavaScript extract policy


response.content = '';
response.headers['Content-Type'] = 'application/json';
// Create a brand-new JSON object for the response and fill it up
var r = response.content.asJSON;
r.Status = context.getVariable('GeoResponse.status');
r.Origin = context.getVariable('GeoResponse.origin');
r.Destination = context.getVariable('GeoResponse.destination');
r.Distance = context.getVariable('GeoResponse.distance');
r.Duration = context.getVariable('GeoResponse.duration');
var hotel = new Object();
hotel.Hotel_Name = context.getVariable('response-parse.Hotel Name{}');
hotel.Hotel_Rating = context.getVariable('response-parse.Hotel Rating{}');
hotel.Hotel_Address = context.getVariable('response-parse.Hotel Address{}');
r.suggestedhotel = hotel

But the hotel details come as null for me. Please help me if i am missing something here .

Sample Response:

{
   "html_attributions" : [],
   "next_page_token" : "CpQCAwEAAF9reYCm5ImOeGu-IF5AQ5BstS1NRn3jAV2v5_ebqQWF9_NaBKOyfFwO8nfT-t51aokWycGed2_1QqOOy2VjaK5dwQy_03wKgsbnZLH7l65OEkhx4gSZd2x8SMVE529jOJ7rt8W7vUxpZbG9SzxcBUSWh0OsTnqbV9etLy7MwElzrEhCrVEW1NJIPvNXQfk68oBA4chIqvniAIioSjCUvKAaAGBDb2kYEoEJdMAu3faMncanxpMVrIQLhBtmM7G7LdmYeqjaQ-ciy-KaJ54UsYbo_dqXNjXukTGuaVz5ayHmasRMgk8-bbvZ5EsZAmRQ5wwfVue-ElTJidDhyfdvcqv00Ga0t2QpHNKjezwSNRmrEhDmE364qxAfLHJepA7ujC6LGhRO1omOQYHjKcj40cTBPEe8n45pFA",
   "results" : [
      {
         "business_status" : "OPERATIONAL",
         "formatted_address" : "35-25 Farrington St, Flushing, NY 11354, United States",
         "geometry" : {
            "location" : {
               "lat" : 40.763661,
               "lng" : -73.83129099999999
            },
            "viewport" : {
               "northeast" : {
                  "lat" : 40.76499557989273,
                  "lng" : -73.83000692010728
               },
               "southwest" : {
                  "lat" : 40.76229592010728,
                  "lng" : -73.83270657989273
               }
            }
         },
         "icon" : "https://maps.gstatic.com/mapfiles/place_api/icons/restaurant-71.png",
         "id" : "fdcc641fca120971511b4f7d037b7bfd544f436b",
         "name" : "Magna Restaurant",
         "opening_hours" : {
            "open_now" : false
         },
         "photos" : [
            {
               "height" : 640,
               "html_attributions" : [
                  "\u003ca href=\"https://maps.google.com/maps/contrib/105271389879554975061\"\u003eMagna Restaurant\u003c/a\u003e"
               ],
               "photo_reference" : "CmRaAAAAnWRyVVzJyCKpoFT0nN-bHNE4voHojmOeQKu7Jk3MSFXe5NMwCE6a-m04JVTK5C_tfIJzPRhg7LN2X9v_HrN-3vbn5XDA82VKYrnekKOc6E9ul4wQTivCKcIANMg9G9sDEhDtQeC7lyzAE0dyoZk7_oZjGhRmOy7uVoXhsWTwbAH7a86eP1jOhA",
               "width" : 480
            }
         ],
         "place_id" : "ChIJT5fPRAVgwokRApCD3ei8UFQ",
         "plus_code" : {
            "compound_code" : "Q579+FF Flushing, Queens, NY, United States",
            "global_code" : "87G8Q579+FF"
         },
         "price_level" : 2,
         "rating" : 4.4,
         "reference" : "ChIJT5fPRAVgwokRApCD3ei8UFQ",
         "types" : [ "restaurant", "food", "point_of_interest", "establishment" ],
         "user_ratings_total" : 170
      },
      ...many more...
   ],
   "status" : "OK"
}


Solved Solved
0 14 16.9K
1 ACCEPTED SOLUTION

amit12pce
Participant II

at last fixed it removed the extract variable and done the same in js as below.

var results_array = r.results;
var new_array = [];
var obj = {};
for (var i=0; i<results_array.length; i++) {
    obj = {
        "name":results_array[i].name,
        "rating":results_array[i].rating,
        "address":results_array[i].formatted_address
    }
    new_array.push(obj)
    
    // Clear the object
    obj = {}
}


r.suggested_restaurant = new_array;








// Delete irrelevant response
delete r.html_attributions;
delete r.next_page_token;
delete r.results

View solution in original post

14 REPLIES 14

You said "the response comes null".

From which? From the extractVariables? From the JavaScript?

What are you expecting? What is your goal?

Maybe in JS try something like this:

var json = JSON.parse(context.getVariable('GeoCodingResponse.content'));
var extracted = json.results.map(function(r) {
  return { name:r.name, address: r.formatted_address, rating:r.rating };
});


print(JSON.stringify(extracted, null, 2)); // will show in Trace
context.setVariable('extracted', JSON.stringify(extracted, null, 2)));

Hi Dino,

Thanks you for your response.

I had extracted response in 2 extract variable policy , then using java script to combine both the extracted values for the response.

Are you saying that you have solved your problem?

You're all good now?

haven't tried yet will try and let you know Dino

the above has'nt resolved my case , give the response above , i need to extract it using extract variable policy then modify the response in JS

OK I'm not clear what you're asking at this point.

i need to extract it using extract variable policy then modify the response in JS

Can you be more specific regarding the meaning of "extract it" and "modify it". I'm better at answering specific questions.

given the above sample response i want to show the response as below ;

suggested hotel [

{ name: "xyz",

address:"12 blk byhh "

rating:"4.9"

}

{ name: "abc",

address:"465 hjk jh "

rating:"4.3"

}

.....................

also when i execute the above extract variable policy to extract data from response and JavaScript policy to modify the final response.

extract variable policy :

<ExtractVariablesname="ParsehotelResponse"><Source>response</Source><VariablePrefix>response-parse</VariablePrefix><JSONPayload><Variablename="Hotel Name"type="Array"><JSONPath>$.results.name[*]</JSONPath></Variable><Variablename="Hotel Rating"type="Array"><JSONPath>$.results.rating[*]</JSONPath></Variable><Variablename="Hotel Address"type="Array"><JSONPath>$.results.formatted_address[*]</JSONPath></Variable></JSONPayload></ExtractVariables>

js to get final response .

response.content ='';
response.headers['Content-Type']='application/json';// Create a brand-new JSON object for the response and fill it upvar r = response.content.asJSON;
r.Status= context.getVariable('GeoResponse.status');
r.Origin= context.getVariable('GeoResponse.origin');
r.Destination= context.getVariable('GeoResponse.destination');
r.Distance= context.getVariable('GeoResponse.distance');
r.Duration= context.getVariable('GeoResponse.duration');var hotel =newObject();
hotel.Hotel_Name= context.getVariable('response-parse.Hotel Name{}');
hotel.Hotel_Rating= context.getVariable('response-parse.Hotel Rating{}');
hotel.Hotel_Address= context.getVariable('response-parse.Hotel Address{}');
r.suggestedhotel = hotel

all the hotel names comes in one tag same as other .

sample response which i got below is not what i am expecting.

"suggestedhotel":{"Hotel_Name":["Los Tacos No.1","Gotham","Le Coucou","Upland","Le Bernardin","Olio e Piú","The NoMad Restaurant","Kesté Pizza & Vino","Minetta Tavern","LOS TACOS No. 1","Rezdôra","Gramercy Tavern","Estela","Sauce","Buddakan","Racines NY","Oceana","Buenos Aires","Craft","Ocean Prime"],"Hotel_Rating":[4.8,4.5,4.6,4.5,4.7,4.5,4.7,4.6,4.5,4.7,4.5,4.7,4.6,4.4,4.5,4.6,4.5,4.6,4.5,4.6],"Hotel_Address":["229 W 43rd St, New York, NY 10036, United States","12 E 12th St, New York, NY 10003, United States","138 Lafayette St, New York, NY 10013, United States","345 Park Ave S, New York, NY 10010, United States","155 W 51st St, New York, NY 10019, United States","3 Greenwich Ave, New York, NY 10014, United States","1170 Broadway, New York, NY 10001, United States","271 Bleecker St, New York, NY 10014, United States","113 MacDougal St, New York, NY 10012, United States","75 9th Ave, New York, NY 10011, United States","27 E 20th St, New York, NY 10003, United States","42 E 20th St, New York, NY 10003, United States","47 E Houston St 1st floor, New York, NY 10012, United States","78 Rivington St, New York, NY 10002, United States","75 9th Ave, New York, NY 10011, United States","94 Chambers St, New York, NY 10007, United States","120 W 49th St, New York, NY 10020, United States","513 E 6th St, New York, NY 10009, United States","43 E 19th St, New York, NY 10003, United States","123 W 52nd St, New York, NY 10019, United States"]}}

expected response

suggested hotel [

{ name: "xyz",

address:"12 blk byhh "

rating:"4.9"

}

{ name: "abc",

address:"465 hjk jh "

rating:"4.3"

}

.....................

Your code is doing exactly what you're telling it to do. You're not creating an array at the suggested hotel level but instead treating it as a single object and setting name/etc on this single object.

Dino has already given you a very elegant solution to what you're trying to do. I think sometimes, such as when working with json, it can be easier to manipulate it directly in javascript rather than trying to combine extract value policy with javascript..

Hi Dane thanks for your view

I tried to use Dino's solution as below.

var json = JSON.parse(context.getVariable('response.content'));

var extracted = json.results.map(function(r) { return { name:r.name, address: r.formatted_address, rating:r.rating }; });

print(JSON.stringify(extracted, null, 2)); // will show in Trace context.setVariable('extracted', JSON.stringify(extracted, null, 2));

but getting 500 error as response.content is null.


trace.pdf attached

I'm not sure why you're getting a 500. I suggest take a look at trace, and you should be able to confirm the output as what you're expecting.

But you can always set the response.content variable in your javascript

hi Dane,

please help to suggest attached the trace file also

amit12pce
Participant II

at last fixed it removed the extract variable and done the same in js as below.

var results_array = r.results;
var new_array = [];
var obj = {};
for (var i=0; i<results_array.length; i++) {
    obj = {
        "name":results_array[i].name,
        "rating":results_array[i].rating,
        "address":results_array[i].formatted_address
    }
    new_array.push(obj)
    
    // Clear the object
    obj = {}
}


r.suggested_restaurant = new_array;








// Delete irrelevant response
delete r.html_attributions;
delete r.next_page_token;
delete r.results