function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Brian SheaBrian Shea 

Trouble mapping the results of a SOQL query into JSON

I've been trying to serialize the results of a SOQL query into JSON. But I've run into a problem because one of the items in the SOQL result set is generated by a function rather than a field. And I'm having trouble mapping the non-field into the JSON.

Here's what I'm doing:

String q = 'SELECT Physical_Address_City__c, Contact_Person__c, DISTANCE(Geolocation__c, GEOLOCATION(:lat, :lng), \'mi\') FROM Location__c;
List<Location__c> locationList = Database.query(q);
List<Location> locationRecords = new List<Location>();
 for(Location__c loc : locationList) {
            locationRecords.add(new Location(loc));
}
String locationJSON = JSON.serialize(locationRecords);

RestContext.response.addHeader('Content-Type', 'application/json');
RestContext.response.responseBody = Blob.valueOf(locationJSON);

Where the Location class is as follows:
Public class Location {
	    
        public String City;
        public Strong ContactPerson;
        public Decimal Distance;

	    Public Location(Location__c loc) {
	        this.City = loc.Physical_Address_City__c;
            this.ContactPerson = loc.Contact_Person__c;
            this.Distance = loc.expr0;
	    }
}
 

But I'm getting the following error:
Error: Compile Error: Invalid field expr0 for SObject Location__c at line 22 column 33

I also tried aliasing the Distance results to DISTANCE in my SELECT statement and reference DISTANCE

this.Distance = loc.DISTANCE;
But that didn't work either. Same error message.


The mapping is expecting a field, but I'm passing it the results of a function. Any recommendations for how to work around this?

Thank you!

Best Answer chosen by Brian Shea
UC InnovationUC Innovation
Try replacing the following statement in your Location class constructor:

this.Distance = loc.expr0;

with:

this.Distance = (Decimal)loc.get('expr0');

All Answers

Alex SelwynAlex Selwyn
I believe DISTANCE and GEOLOCATION function can be used only in WHERE clause.

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_geolocate.htm

You need to use the Location class to calculate the distance in your loop.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_class_system_Location.htm

Hope that helps. Cheers!
Brian SheaBrian Shea

HI Alex,
Thanks for responding! I thought so too but just found out that as of the Spring '16 release DISTANCE() can be used in the SELECT clause.

http://salesforce.stackexchange.com/questions/37046/can-geolocation-fields-be-used-to-calculate-and-display-distance

UC InnovationUC Innovation
Try replacing the following statement in your Location class constructor:

this.Distance = loc.expr0;

with:

this.Distance = (Decimal)loc.get('expr0');
This was selected as the best answer
Brian SheaBrian Shea
@UC Innovation
Thank you; that worked!