Generic Database query transform to list of objects

Community Show & Tell
,
1

Sometimes it's just easier to work with a list of objects instead of an object of lists. Javascript map/filter/reduce and other iterator functions especially are nicer to use in that format, but unfortunately Retool's direct DB queries return a single object with a bunch of lists.

Database Query Result (object of list):

{
  col1: ['a','b','c'],
  col2: [1, 2, 3],
// ...
}

Easier to use in javascript (list of objects):

[
  {
    col1: 'a',
    col2: 1
  },
  {
    col1: 'b',
    col2: 2,
  },
  // ...
]

The following snippet will take in the result from Retool database queries which are returned as a single object with each fields being a list of the same length and transform it into a list of objects with one single value apiece.

function dbQueryToObjectList(query) {
  const transformed = [];
  const keys = Object.keys(query);
  const rows = query[keys[0]].length

  for (let i = 0; i < rows; i++) {
    const entry = {};
    for (const key of keys) {
      entry[key] = query[key][i];
    }
    transformed.push(entry)
  }

  return transformed;
}

Here's what it looks like when added as a transformer to an SQL query:

image

Hope it helps!

2

I love your solution!

I also think you might have just implemented one of ReTool's helper methods

1 Like
3

Appears so. That was buried in the docs so I missed it, but it seems pretty critical--thanks for pointing it out. Surprised they don't have a toggle in the DB query menu to use it as sort of a "pre-transformer" seeing as it's gotta be a pretty common thing to do...

4

That would be a really handy toggle switch!

1 Like