Column Data from my API has nested JSON values how do I transform these into seperate columns?

[David_Ellis]

Column Data from my API has nested JSON values how do I transform these into seperate columns?

I need to create new columns for each nested JSON data item that apperas in the Column "Custom params"

So added columns for Name, Phone, Email, phase, t1l2_gr2 etc is needed and row datae to be added.

Is this possible? If so what is the best way forward?

Help is much appreciated

[David_Ellis]

[Erin_Masatsugu]

Hi @David_Ellis ,

If you just want to display these updated columns in a table, you can use a Transformer to transform the results of your query -- ex. to iterate over the query results, and "flatten" customParams into new columns.

If you need to write this transformed data back to a datasource, you can configure an on success event handler on the query, which triggers another query to write the data back.

Hope that helps!

[David_Ellis]

Thanks Erin for the feedback. My problem is Im a begginer and not sure how to structure the JS code for this in the Transformer. Were do think I can enquire to find paid resources to help implement the code for this?

[Erin_Masatsugu]

Not a problem!

If your original payload query1.data looks like this (just guessing based on the screenshot)

[
  {
    id: 1,
    photo: null,
    created: "Dec-09-2024",
    customParams: [
      { name: "name", value: "Some Name" },
      { name: "phone", value: "555-5555" },
      { name: "email", value: "email@gmail.com" },
    ],
  },
  {
    id: 2,
    photo: "image.jpg",
    created: "Jan-10-2025",
    customParams: [
      { name: "name", value: "Another Name" },
      { name: "phone", value: "123-4567" },
    ],
  },
  ...
]

you can write a transformer like this:

return {{ query1.data }}.map((obj) => {
  if (Array.isArray(obj.customParams)) {
    const { customParams, ...rest } = obj;
    const flattened = Object.fromEntries(
      customParams.map((param) => [param.name, param.value])
    );
    return { ...rest, ...flattened };
  }
  return obj;
});

This should return a result like this:

[
    {
        "id": 1,
        "photo": null,
        "created": "Dec-09-2024",
        "name": "Some Name",
        "phone": "555-5555",
        "email": "email@gmail.com"
    },
    {
        "id": 2,
        "photo": "image.jpg",
        "created": "Jan-10-2025",
        "name": "Another Name",
        "phone": "123-4567"
    }
]

You can then hook up your table directly to that transformer.

[David_Ellis]

Thanks so much for the feedback and proposed solution!

I am a begginer with Retool, do I create a Transfomer before the query or in the query transformer window?

This is the feed naming, what will be the updated transfer naming?

  "result": "success",
  "contact_list": [
    {
      "channelType": 0,
      "displayName": null,
      "customParams": [
        {
          "name": "name",
          "value": "Bhengu 675765865756756"
        },
        {
          "name": "phone",
          "value": "56767567567567567"
        },
        {
          "name": "email",
          "value": "5676575675675675675"
        },
        {

![image|531x500](upload://f7zxMnF0n76WkWmVLJOYvh2Wyqd.png)

[David_Ellis]

[Tess]

Hi @David_Ellis,

Good question! For simplicity, I'd probably move this to a query transformer and then change {{query1.data}} to just data

As far as working with data, it's a little hard to tell what the full data structure is and which keys you need, but happy to pair on it during office hours