Salesforce, a customer relationship management (CRM) platform with comprehensive data management tools and reporting capabilities, provides robust solutions for businesses. In this tutorial, we will walk you through how to get grouped JSON Data from Salesforce Reports by creating a simple report in Salesforce data and generate JSON output with group by.

This step-by-step guide will show you how to convert your Salesforce data into structured JSON formats for easier integration and analysis — perfect for anyone in Salesforce: administrators, developers, or data visualization specialists.

How to Get JSON Data from Salesforce Reports?

Writing JSON outputs from Salesforce data helps integrate easily into other applications for better enhancement, data visualization, and reporting.

The following aspect is a good point of interest for clustering the data fields as it provides a better perspective to organize the information required for analyzing trends and patterns. In this quick Salesforce tutorial, we cover everything from establishing API access through sending queries, all the way to extracting grouped JSON data.

Prerequisites

Make sure you have the following before you start:

Salesforce Reports and SOQL

There are two main ways of querying data in Salesforce:

  • Reports: A no-code reporting interface. Nevertheless, there are some restrictions such as you cannot export grouped data directly from reports in JSON format.
  • SOQL (Salesforce Object Query Language): A robust query language that allows highly accurate data retrieval, enables grouping and aggregation of records, and supports JSON output via APIs.

SOQL, along with Salesforce APIs, is the recommended path for advanced data manipulation and JSON generation.

Using SOQL to Group Data

SOQL supports GROUP BY clauses, which let you aggregate data on specific fields. This is essential for organizing the data before converting it into JSON format. Below is an example of a SOQL query that groups opportunities by StageName:

SELECT StageName, COUNT(Id) 
FROM Opportunity 
GROUP BY StageName

This query fetches the count of opportunities for each stage, which can then be turned into JSON.

How to Get JSON Response Using Salesforce APIs

Salesforce APIs, like the REST API, allow you to run SOQL queries and get the results back in JSON format. These APIs enable you to automate the extraction of data and easily connect Salesforce data with other systems.

Step-by-Step Tutorial

Salesforce API Access Step 1 — Set Up

  1. Create a Connected App:
    • Go to Setup > App Manager and click New Connected App.
    • Enter the mandatory information like Connected App Name, API Name, and Contact Email.
  2. Enable OAuth Settings:
    • Check Enable OAuth Settings.
    • Specify the Callback URL (e.g., https://localhost for testing).
    • Choose relevant OAuth Scopes (e.g., Full Access).
    Please note the Consumer Key and Consumer Secret, then save the Connected App.
  3. Generate Security Token:
    • Navigate to Settings > My Personal Information > Reset My Security Token.
    • Click Reset Security Token to receive it via email.

Step 2: Build Your SOQL Query

Choose the data you want to group and aggregate. For example, to count contacts and group them by AccountId:

SELECT AccountId, COUNT(Id)
FROM Contact
GROUP BY AccountId

Step 3: Run the Query and Get JSON

Using Salesforce Workbench

  1. Login to Workbench:
  2. Navigate to SOQL Query:
    • Go to Queries > SOQL Query.
  3. Enter Your Query:
    • Input your SOQL query in the query box.
  4. Select Response Format:
    • Select JSON as the response type.
  5. Execute the Query:
    • Press the Execute button to retrieve the grouped JSON data.

Example Output

{
  "totalSize": 3,
  "done": true,
  "records": [
    {
      "attributes": {
        "type": "AggregateResult"
      },
      "AccountId": "001xx000003DGbYAAW",
      "expr0": 15
    },
    {
      "attributes": {
        "type": "AggregateResult"
      },
      "AccountId": "001xx000003DGbZAAW",
      "expr0": 10
    },
    {
      "attributes": {
        "type": "AggregateResult"
      },
      "AccountId": "001xx000003DGbXAAW",
      "expr0": 20
    }
  ]
}

Step 4: Using the Received JSON Data

With the JSON output in hand, you can now parse it using JavaScript and Python or connect it to applications (e.g., Slackbot) for further processing.

Example in JavaScript

const jsonData = {
  "totalSize": 3,
  "done": true,
  "records": [
    {
      "attributes": { "type": "AggregateResult" },
      "AccountId": "001xx000003DGbYAAW",
      "expr0": 15
    },
    // Additional records...
  ]
};

// Parsing the JSON data
jsonData.records.forEach(record => {
  console.log(`Account ID: ${record.AccountId}, Contact Count: ${record.expr0}`);
});

Tools for Easy Extraction of Data

Using Salesforce Workbench

Salesforce Workbench is a powerful, web-based tool for interacting with Salesforce APIs without writing code. It allows you to execute SOQL queries and retrieve data in various formats, including JSON.

Advantages:

  • User-friendly interface.
  • No coding required.
  • Supports various Salesforce APIs.

Steps:

  1. Log in to Workbench.
  2. Navigate to Queries > SOQL Query.
  3. Enter and execute your query.
  4. Select JSON as the format of output.

Making API Requests with Postman

Postman is a versatile tool for making API requests, which is useful for automating data retrieval and integrating Salesforce data with other systems seamlessly.

Advantages:

  • Supports automation and scripting.
  • Easy to manage multiple API requests.
  • Useful for testing and debugging.

Steps:

  1. Authenticate with Salesforce:
    • Use OAuth 2.0 to obtain an access token.
  2. Create a GET Request:
    • Endpoint: https://yourInstance.salesforce.com/services/data/vXX.X/query?q=YOUR_SOQL_QUERY
  3. Set Headers:
    • Authorization: Bearer YOUR_ACCESS_TOKEN
    • Content-Type: application/json
  4. Send the Request:
    • Execute the request to retrieve JSON data.

Apex Code for Creating Custom JSON Outputs

For more advanced use cases, you can write Apex classes to execute SOQL queries and format the output as JSON.

Example Apex Class

public with sharing class GroupedDataController {
    @AuraEnabled(cacheable=true)
    public static String getContactsGrouped() {
        // Example: Grouping contacts by AccountId
        List<AggregateResult> groupedResults = [SELECT AccountId, COUNT(Id) cnt FROM Contact GROUP BY AccountId];
        List<Map<String, Object>> results = new List<Map<String, Object>>();

        for(AggregateResult ar : groupedResults){
            Map<String, Object> record = new Map<String, Object>();
            record.put('AccountId', ar.get('AccountId'));
            record.put('ContactCount', ar.get('cnt'));
            results.add(record);
        }

        return JSON.serialize(results);
    }
}

Usage:

  1. Deploy the Apex class to your Salesforce org.
  2. Call the getContactsGrouped method via Lightning Components, Visualforce pages, or external applications.

Best Practices

  1. Optimize SOQL Queries:
    • Use selective filters to minimize data retrieval.
    • Avoid unnecessary fields to reduce payload size.
  2. Handle Large Data Sets:
    • Implement pagination for queries returning extensive data.
    • Use batch processing for large-scale data operations.
  3. Secure API Access:
    • Use OAuth 2.0 for secure authentication.
    • Limit API access permissions to necessary scopes only.
  4. Error Handling:
    • Implement robust error handling in your applications to manage API failures gracefully.
  5. Data Privacy Compliance:
    • Ensure compliance with data protection regulations (e.g., GDPR) when handling sensitive data.

Conclusion

Salesforce data reports can be formatted as grouped JSON outputs, which improve integration and analytical capabilities with other systems — helping organizations leverage their investments in the CRM more effectively. It allows you to easily extract data in JSON format through SOQL queries and Salesforce APIs. Salesforce offers flexibility by using tools like Workbench and Postman or writing custom Apex code to obtain the data required for your reports.

Start converting Salesforce Reports to JSON output now for powerful data strategies!

Additional Resources