How to model the rows in getData() since i have nested JSON data?

Question

I want to display these fields :name, age, addresses_id, addresses_city, addresses_primary for each person into data studio.

My JSON data

{
    "data": [
        {
            "name": "Lio",
            "age": 30,
            "addresses": [
                {
                    "id": 7834,
                    "city": "ML",
                    "primary": 1
                },
                {
                    "id": 5034,
                    "city": "MM",
                    "primary": 1
                }
             ]
         },
         
         {
            "name": "Kali",
            "age": 41,
            "addresses": [
                {
                    "id": 3334,
                    "city": "WK",
                    "primary": 1
                },
                {
                    "id": 1730,
                    "city": "DC",
                    "primary": 1
                }
             ]
         },
         
           ...
      ]
}

there is no problem if i don't render the addresses field

return {
    schema: requestedFields.build(),
    rows: rows
  };
  
  //rows:
 /* 
 "rows": [
    {
      "values": ["Lio", 30]
    },
    {
      "values": ["Kali", 41]
    },
    ...   
]
*/

The problem is

I'm not able to model the nested JSON data in Google Data Studio. I have the problem exactly in the "addresses" field. Could anyone tell me what format should be for the rows in this case?

Answer 1

As you already know, for each name of your dataset, you clearly have more than one row (one person has multiple addresses). Data Studio only accepts a single data for each field, since arrays are not supported at all. So you need to work on this.

There are some ways to solve this, but always keep in mind that:

• getSchema() should return all available fields for your connector (the order doesn't really matter, since Data Studio always sort alphabetically the available fields)
• getData() should return a list of values. But here the order is relevant: it should be the same as the parameter passed to getData() (which means the results should be dynamic, sometimes you'll return all values, sometimes not, and the order may change).

Solution 1: Return multiple rows per record

Since you can produce multiple rows for each name, just do it.

To achieve this, your field definition (=getSchema()) should include fields address_id, address_city and address_primary (you can also add address_order if you need to know the position of the address in the list).

Supposing getData() is called with all fields in the same order they were discribed, rows array should look like this:

"rows": [
  {
    "values": ["Lio", 30, "7834", "ML", 1]
  },
  {
    "values": ["Lio", 30, "5034", "MM", 1]
  },
  {
    "values": ["Kali", 41, "3334", "WK", 1]
  },
  {
    "values": ["Kali", 41, "1730", "DC", 1]
  },
  ...
]

IMO, this is the best solution for your data.

Solution 2: Return one address only, ignoring others

If you prefer one row per person, you can get one of the addresses and display only it (usually the main/primary address, or the first one).

To achieve this, your field definition (=getSchema()) should include fields address_id, address_city and address_primary.

Supposing getData() is called with all fields in the same order they were discribed, rows array should look like this:

"rows": [
  {
    "values": ["Lio", 30, "7834", "ML", 1]
  },
  {
    "values": ["Kali", 41, "3334", "WK", 1]
  },
  ...
]

Solution 3: Return all addresses, serialized in a field

This is helpful if you really need all information but do not want a complex scheme.

Just create a field called addresses in your field definition (=getSchema()) and write the JSON there as a string (or any other format you want).

Supposing getData() is called with all fields in the same order they were discribed, rows array should look like this:

"rows": [
  {
    "values": ["Lio", 30, "[{\"id\": 7834, \"city\": "ML", \"primary\": 1}, {\"id\": 5034, \"city\": \"MM\", \"primary\": 1}]"]
  },
  {
    "values": ["Kali", 41, "[{\"id\": 3334, \"city\": \"WK\", \"primary\": 1}, {\"id\": 1730, \"city\": \"DC\", \"primary\": 1}]"]
  },
  ...
]

This solution may appear senseless, but it is possible to interact with this data later in DataStudio using REGEX if really needed.

Solution 4: Create a different field for each address

If you're sure all records has a maximum number of addresses (in you example, both names have 2 addresses, for example), you can create multiple fields.

Your field definition (=getSchema()) should include fields address_id1, address_city1, address_primary1, address_id2, ... address_primaryN.

I wouldn't explain how rows should look like in this situation, but it is not hard to guess with the other examples.