Rest API Source + Database Source in a Single Table

[henrymeiklejohn]

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?

[BattleCattleDevelopment]

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;

[henrymeiklejohn]

• 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:
   

   

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

   

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

Thanks

[jg80]

Did you try to query JSON with SQL?

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.

[henrymeiklejohn]

I hadn't worked on this for a while but just figured out a simple solution today, essentially what @jg80 mentioned:

• SQL Resource Query> add a transformation of "return formatDataAsArray(data)
  *Rest API Query
  *Created a Join with a Query JSON with SQL resource.