How to break nested JSON response into new columns in the table?

Queries and Resources
1

Hello Community,

I am new to Retool, and learning as I make progress. I connected a GraphQL API that returns a response like the one below

{
  "data": {
    "search": {
      "total": 874,
      "business": [
        {
          "name": "Blue Fox Cafe",
          "location": {
            "address1": "919 Fort Street",
            "address2": "Suite 101",
            "address3": "",
            "city": "Victoria",
            "state": "BC",
            "postal_code": "V8V 3K3",
            "country": "CA"
          },
          "url": "https://www.yelp.com/biz/blue-fox-cafe-victoria?adjust_creative=tTi2ykxmpxGNlVIrxtNbWA&utm_campaign=yelp_api_v3&utm_medium=api_v3_graphql&utm_source=tTi2ykxmpxGNlVIrxtNbWA",
          "display_phone": "+1 250-380-1683",
          "categories": [
            {
              "title": "Breakfast & Brunch"
            }
          ]
        }
      ]
    }
  }
}

What I see in the output table is below

Screen Shot 2022-10-17 at 12.52.58 PM
Screen Shot 2022-10-17 at 12.52.58 PM3142×680 212 KB

What I want it that based on the response, the location data, I create column for each address1, address2, address2, state, city and fill the data in the table in those respective columns.

I am not sure how to do that. Can someone guide me, please?

Thank you

UPDATE 1
I tried creating a transformer with following code

// type your code here
// example: return formatDataAsArray(data).filter(row => row.quantity > 20)
const getEntries = (o) => 
  Object.entries(o).flatMap(([k, v]) => 
    Object(v) === v  ? getEntries(v) : [ [`${k}`, v] ]
  )


const businesses = data.search.business.map(b => Object.fromEntries(getEntries(data.search.business)))
const newData = Object.assign({}, data, {search: {business: businesses}})

//console.log(data)
//console.log(newData)

return newData

After this, all rows have same data, so I am sure, I am doing something wrong here

Screen Shot 2022-10-17 at 1.25.45 PM
Screen Shot 2022-10-17 at 1.25.45 PM3208×612 98.5 KB

2

Hi @harit, Have you tried the "Custom Columns" with the current row property?

3

@TabraizAhmed , Thanks for your response. I was able to do this using following transformer

// type your code here
// example: return formatDataAsArray(data).filter(row => row.quantity > 20)
const flattenBusiness = (b) => {
  let newB = {}
  newB.name = b.name
  newB.url = b.url
  newB.display_phone = b.display_phone
  newB.address1 = b.location.address1
  newB.address2 = b.location.address2
  newB.address3 = b.location.address3
  newB.city = b.location.city
  newB.state = b.location.state
  newB.postal_code = b.location.postal_code
  newB.country = b.location.country
  newB.categories = b.categories.map(c => c.title).join(',')
  return newB
}

const business = data.search.business.map(b => flattenBusiness(b))
const newData = ({}, {search: {total: data.search.total, business: business}})

// console.log(data)
// console.log(newData)


return newData
4

I have a simular request;

{
"cards": [
{
"id": "f4bd-d925018d4e29-a355-874b-c7f2-af42",
"type": "virtual",
"status": "terminated",
"active": false,
"card_details": {
"usage_limit": 10,
"amount_limit": 1000,
"terminate_date": "2023-03-03T23:59:59",
"mid_blacklist": null,
"mid_whitelist": null,
"expiration_date": "2023-03-03T23:59:59",
"limit_window": "LIFETIME"
},
"created_date": "31-01-2023",
"updated_date": "31-01-2023"
},

Screenshot 2023-02-06 at 10.00.46 am
Screenshot 2023-02-06 at 10.00.46 am2232×490 75.1 KB

Transformer
const flattenCards = (b) => {
let newB = {}
newB.id = b.id
newB.status = b.status
newB.usage_limit = b.card_details.usage_limit
newB.amount_limit = b.card_details.amount_limit
newB.termination_date = b.card_details.termination_date
newB.mid_blacklist = b.card_details.mid_blacklist
newB.mid_whitelist = b.card_details.mid_whitelist
newB.expiration_date = b.card_details.expiration_date
newB.limit_window = b.card_details.limit_window
return newB
}

const cards = data.search.cards.map(b => flattenCards(b))
const newData = ({}, {search: {total: data.search.total, cards: cards}})

where am i going wrong;
Error:Cannot read properties of undefined (reading 'cards')

5

Hey @tferguson!

Can you try data.cards.map(...) instead of data.search.cards.map(...)?

6

const cards = data.cards.map(b => flattenCards(b))
const newData = ({}, {search: {total: data.search.total, cards: cards}})

Get same result, no error

7

That could be because the code is still attempting to access the search property on data in the second line. What would you like the result to look like ultimately? I'm curious if just the following could work:

const cards = data.cards.map(b => flattenCards(b))
return cards;
8

that didnt work, i eventualy tried every option, this worked.

const cards = data.cards.map(b => flattenCards(b))
const newData = ({}, {cards: data.cards, cards: cards})

return newData