Rest API Source + Database Source in a Single Table

I have a table where the primary data source is a Rest API.

I'm creating a table of users. The issue is, this service only stores a user_id but not values for contact details (first_name, last_name, email etc).

The first_name and last_name are stored in our database and can be linked to this user_id. I have queries already built that retrieve these.

Is there any way to merge a database query into this same table so that first_name and last_name can be displayed?

JavaScript query, map in your database info into the user_id data. Is it an SQL database?

Why duplicate this information though? Can you just add user_id into your database and pull back one dataset instead of two?

const apiUsers = YourAPIUserData.value
const dbUsers = YourDatabaseUsers.value

    const mergedUsers = apiUsers.map(apiUser => {
        const dbUser = dbUsers.find(user => user.user_id === apiUser.user_id);
        return { ...apiUser, ...dbUser };
    });

    return mergedUsers;
  • Yes this is a SQL database

  • The use case here is that the rest API has a key on the user object called connected_sources which is an array of user connected devices that changes over time. We probably could sync this to our DB, but it seems a bit more complicated than syncing a static attribute that is created only once. So I'd like to avoid that if possible to create this table.

  • I'm still a bit stuck so I'll give some more details:

  1. Here is the API call. I have 2 versions, get_users_vital_array and get_users_vital_object where the former has a transformation into an array. Not sure which I will end up using:


  2. Here is the SQL query

  3. So the join will occur between client_user_id from the API and id from the SQL query.

  4. Should I be creating this as a resource (like query4 in the images) or on the table data source like this:

  1. Here is the current query I'm trying. My hunch is that the portion I circled is the problem

  2. Related to above, do I need to transform my SQL response to JSON to get this to work?

Thanks

Did you try to query JSON with SQL?
image

The basic documentation is here. My experience has been that sometimes I need to manipulate the raw JSON from a query or API (usually a simple transformer to flatten an array, or something like that), but it should suffice for what you are trying to accomplish.