function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
nununinununi 

Account Records to JSON format

Hello! I would like to display my account records on a VF page in the following JSON format. I need help with the class and the VF page. Thanks a bunch!
[
    {
        "AccountId": 1,
        "AccountName": "account1",
        "AddressBook": [
            {
                "Id": 1,
                "Name": "test",
                "Attn": "test",
    "StreetName": "test",
                "CityName": "test",
                "State": "TT",
                "PostCode": 89098,
                "Tel": "(000)- 000-0000",
                "Favorite": true,
                "AccountId": 1
            },
            {
                "Id": 2,
                "Name": "test2",
                "Attn": "test2",
    "StreetName": "test2",
                "CityName": "test2",
                "State": "TU",
                "PostCode": 3000,
                "Tel": "(000)- 000-0000",
                "Favorite": true,
                "AccountId": 1
            },
            
        ]
    },
    {
        "AccountId": 2,
        "AccountName": "Account2",
        "AddressBook": [
            {
                "Id": 1,
                "Name": "Test3",
                "Attn": "Test3",
    "StreetName": "Test3",
                "CityName": "Test3",
                "State": "TI",
                "PostCode": 3000,
                "Tel": "(000)- 000-0000",
                "Favorite": true,
                "AccountId": 1
            },
            {
                "Id": 2,
                "Name": "Test4",
                "Attn": "Test4",
    "StreetName": "Test4",
                "CityName": "Test4",
                "State": "TO",
                "PostCode": 75000,
                "Tel": "(000)- 000-0000",
                "Favorite": false,
                "AccountId": 1
            },
            
        ]
    },
    
]
Best Answer chosen by nununi
pconpcon
I'm sorry.  Since this is a many-to-one, you'll have to do it as a sub query [1] your query should look like:

List<Account> accounts = [
     select Name,
          (
               select Ship_To__c.Name,
                    Ship_To__c.Attention__c,
                    Ship_To__c.Street__c,
                    Ship_To__c.City__c,
                    Ship_To__c.State__c,
                    Ship_To__c.Zip__c,
                    Ship_To__c.Phone__c
               from Account.Ship_Tos__r
          )
     from Account
     limit 3
];


[http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_relationships.htm]

All Answers

pconpcon
Assuming that you just want to display the raw data (and your select makes it into a list with this formulat, you'll just need to create a VF page that has
<apex:outputText value="{!jsonData}"/>
and a controller that does
public String getJsonData() {
     List<Account> accounts = [ select ... from Account ... ];
     return JSON.serializePretty(accounts);
}
That should get you started.  If you have any specific questions, please let me know.


NOTE: This code was not tested so it may have typographical errors.  Also, it is just a snippet so the full controller and page will need to be written
nununinununi
Thanks @pcon! How do I include the AddressBook part in the SELECT query?
pconpcon
Is AddressBook a custom object?  If so, then you'll need to use the relationship to do  select Id, Address_Book__r.Id, Address_Book__r.Name etc.  However, it will NOT have the clean names you provided there.  It will have the __r and __c for the custom fields.  If your requirement is to have it have clean names, you will need to generate a custom Apex class in your controller and then assign the variables from the query to that object.  Then use JSON.serialize on it.
nununinununi
This is what I have:

public String getJsonData()
{
    List<Account> accounts = [SELECT ID, Name, Account.Ship_To__r.ID, Account.Ship_To__r.Name, Account.Ship_To__r.Attention__c, Account.Ship_To__r.Street__c, Account.Ship_To__r.City__c, Account.Ship_To__r.State__c, Account.Ship_To__r.Zip__c, Account.Ship_To__r.Phone__c, Account.Ship_To__r.Account__c FROM Account LIMIT 3];
    return JSON.serializePretty(accounts);
}

I get the error: Error: Compile Error: Didn't understand relationship 'Ship_To__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 5 column 30
pconpcon
You'll need to use the relationship name.  If you go to your Ship To object and look at the field that joins it to an account, you'll see "Child Relationship Name"  This is what you'll use (appened with an __r) in your query
nununinununi
Ok I did that, but got the same error. I removed the account. in front of the Ship_To relationship name but still no luck.
pconpcon
Is the child relationship name with the account on the Ship_To__c object "Ship_To"?  You can verify the exact name it is looking for in workbench [1] by choosing "Standard & Custom Objects" -> "Account" -> "Child Relationships" -> Ship_To__c then looking at relationshipName.  This will be the api name you need to use in your query.
nununinununi
It is Ship_Tos__r. I changed my query as follows:
List<Account> accounts = [SELECT ID, Name, Ship_Tos__r.ID,
                                               Ship_Tos__r.Name,
                                               Ship_Tos__r.Attention__c,
                                               Ship_Tos__r.Street__c,
                                               Ship_Tos__r.City__c,
                                               Ship_Tos__r.State__c,
                                               Ship_Tos__r.Zip__c,
                                               Ship_Tos__r.Phone__c,
                                               Ship_Tos__r.Account__c FROM Account LIMIT 3];
But I get the same error.
pconpcon
I'm sorry.  Since this is a many-to-one, you'll have to do it as a sub query [1] your query should look like:

List<Account> accounts = [
     select Name,
          (
               select Ship_To__c.Name,
                    Ship_To__c.Attention__c,
                    Ship_To__c.Street__c,
                    Ship_To__c.City__c,
                    Ship_To__c.State__c,
                    Ship_To__c.Zip__c,
                    Ship_To__c.Phone__c
               from Account.Ship_Tos__r
          )
     from Account
     limit 3
];


[http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_relationships.htm]
This was selected as the best answer