Looping an array of records by rows and columns

💬 App Building
1

Very new to Retool and learning lots simply by using AI and of course reading items raised in this discussion forum. I have got stumped in my quest to put an app together and would like some assistance from the community if possible.

image
image1316×110 20.5 KB

I am wanting to loop each record and extract:
Name, v_1 & rk_1
Name, v_2 & rk_2
etc
Name, v_8 & rk_8
so as to form a bulk update for a PostGre database table. v_1 to rk_3 always contains an integer. However, the other cols may be blank in which case the cols don’t get selected for insertion.
I would appreciate any advice or links to posts that might help me.
Kind regards
Brian

1 Like
4

Glad to hear that you're exploring Retool, @NZ556, even if it comes with a bit of a learning curve. :slightly_smiling_face:

I'll probably need a bit more context to be of any real assistance, but will give it a shot! The biggest question I have is how you want to handle the null values - should a record be generated or not? Assuming not, the following JS script should generate an array of { name: ..., v: ..., k: ... } objects:

const rows = [];
for (const record of arrayA) {
  for (let i = 1; i <= 8; i++) {
    const v = record[`v_${i}`];
    const rk = record[`rk_${i}`];
    if (v != null && rk != null) {
      rows.push({ name: record.Name, v, rk });
    }
  }
}

// do something with rows

I hope that helps! Let me know if I've misunderstood anything or if you have any additional questions.

1 Like
5

Hi @NZ556,

I understand what you're trying to accomplish.

One approach that works well in Retool is to first transform your "wide" data structure into a normalized (flat) array that PostgreSQL can easily process for bulk inserts. This avoids running individual insert statements and is generally much more efficient for larger datasets.

For example, if your source data looks like this:

return [
  {
    id: 1,
    Name: "Alice",
    v_1: 10,
    rk_1: 1,
    v_2: 20,
    rk_2: 2,
    v_3: 30,
    rk_3: 3,
    v_4: null,
    rk_4: null,
    v_5: null,
    rk_5: null,
    v_6: null,
    rk_6: null,
    v_7: null,
    rk_7: null,
    v_8: null,
    rk_8: null,
  },
  {
    id: 2,
    Name: "Bob",
    v_1: 15,
    rk_1: 4,
    v_2: null,
    rk_2: null,
    v_3: 25,
    rk_3: 5,
    v_4: 35,
    rk_4: 6,
    v_5: null,
    rk_5: null,
    v_6: null,
    rk_6: null,
    v_7: null,
    rk_7: null,
    v_8: null,
    rk_8: null,
  },
];

You can transform it into a normalized structure like:

[
  {
    "id": 1,
    "name": "Alice",
    "slot": 1,
    "value": 10,
    "rank": 1
  },
  {
    "id": 1,
    "name": "Alice",
    "slot": 2,
    "value": 20,
    "rank": 2
  },
  {
    "id": 1,
    "name": "Alice",
    "slot": 3,
    "value": 30,
    "rank": 3
  },
  {
    "id": 2,
    "name": "Bob",
    "slot": 1,
    "value": 15,
    "rank": 4
  },
  {
    "id": 2,
    "name": "Bob",
    "slot": 3,
    "value": 25,
    "rank": 5
  },
  {
    "id": 2,
    "name": "Bob",
    "slot": 4,
    "value": 35,
    "rank": 6
  }
]

Retool Transformer / JS Query

You can use a Transformer or JS Query to perform the conversion:

const result = [];

({{ transformer34.value }} || []).forEach((row) => {
  for (let i = 1; i <= 8; i++) {
    const value = row[`v_${i}`];
    const rank = row[`rk_${i}`];

    if (
      value !== null &&
      value !== undefined &&
      rank !== null &&
      rank !== undefined
    ) {
      result.push({
        id: row.id,
        name: row.Name,
        slot: i,
        value,
        rank,
      });
    }
  }
});

return result;

What this does

For example, if a row contains:

Name = Alice
v_1 = 10
rk_1 = 1
v_2 = 20
rk_2 = 2
v_3 = null
rk_3 = null

The loop checks each v_x / rk_x pair:

  • When valid values are present, it creates a new record.
  • When values are null or missing, it skips them automatically.

This results in a clean, normalized dataset that is much easier to insert into PostgreSQL.

PostgreSQL Bulk Insert

Once the data is transformed, you can use jsonb_to_recordset() for a single bulk insert:

INSERT INTO rankings (
    source_id,
    name,
    slot,
    value,
    rank
)
SELECT
    source_id,
    name,
    slot,
    value,
    rank
FROM jsonb_to_recordset(
    {{ JSON.stringify(transformer35.value) }}::jsonb
) AS t(
    source_id integer,
    name text,
    slot integer,
    value integer,
    rank integer
);

Using this approach, PostgreSQL handles the entire dataset in one operation, which is significantly more efficient than executing individual insert statements for each row.

Hope this helps! Let me know if your source data structure differs slightly and I can adjust the transformer accordingly.

6

Have you had a chance to revisit this, @NZ556? Or have any additional questions?

1 Like