0

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?

bilalo
  • 129
  • 1
  • 1
  • 7
  • Do you want the manipulate the json and get the result in array `row` in JS. Right – DecPK Mar 30 '21 at 11:48
  • No, what i want is to display the addresses (id,city,primary) field into Google Data Studio. Each person have multiple addresses. And as you know each row in the getData() function represent a person. – bilalo Mar 30 '21 at 11:59
  • `what i want is to display the addresses (id,city,primary) field into Google Data Studio` doesn't actually tell us what you want please show an example of desired output. This is not the hard problem that you make it out to be. If your data is formatted properly when you parse it then it becomes an object and you access it like any other object. So the only thing you need to do is to give us an example of your desired output. Quit using general terms like model and just give us an example of what you want. – Cooper Mar 30 '21 at 16:20
  • Based on these json data i would display 5 columns in google data studio : name, age, id, city and primary. the problem is not in parsing and accessing the object data, on the contrary the problem is how to send the data parsed in appropriate format to Google Data studio. in Data studio each column **accept a single value**.So in our case the "person" has **one** name, **one** age but has **multiple** id, city and primary. I hope I explained the situation well. – bilalo Mar 30 '21 at 17:20

1 Answers1

2

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.

Diego Queiroz
  • 3,198
  • 1
  • 24
  • 36