Replace nested json key in Retool app table with value that matches that key from another mongodb collection in Retool

Queries and Resources
1

HI,

I have two mongodb collection queries in Retool: a "transactions" collection that contains donation transactions for various organizations, and an "organizations" collection that contains organization specific data for those organizations receiving donations. The transactions collection contains "designation" keys (such as "A", "B", "C", etc) that pertain to "designation names" key-value pairs in the "organizations" collection ( such as [ {"A": "General Fund", "B": "Roof Support", etc...}]. I have been able to parse the nested transactions designation column json to get the designation key in a retool app table custom column using:

{{ JSON.parse(currentSourceRow.designations)[0].des }}

Now I would like to get the actual designation name from the organizations designation collection to replace the key in the transactions table (basically as in the sample "transactionsDetails" view shown as the last collection below):

image
image1052×779 78.1 KB

SAMPLE MONGODB COLLECTIONS:

"Organizations" collection:

[
{
_id: ObjectId("6424fe074ebcba0650a0f1f7"),
key: 'clj',
name: 'nonprofit clj',
designations: {
A: 'General Support',
M: 'MM Food Pantry',
C: 'Childrens Donations',
E: 'Honduras one',
D: 'Honduras two',
H: 'Roof Fund',
L: 'Gratitude',
N: 'Womens support',
G: 'Thailand Suppirt'
},
credentials: '{"merchantId":"xvxvvcv00001","merchantFakeLogin":"APIsdfsdf000001"}',
gateway: 'paygate',
sV: '1'
},
{
_id: ObjectId("642501ea4ebcba0650a0f1f8"),
key: 'demo',
name: 'Demo Nonprofit',
designations: {
A: 'General Donations',
B: 'Missions',
C: 'Area of Greatest Need'
},
credentials: '{"merchantId":"000034341","merchantFakeLogin":"API0435430001"}',
gateway: 'paygate',
sV: '1'
},
{
_id: ObjectId("642501ea4ebcba0650a0f1f9"),
key: 'well',
name: 'The Well',
designations: {
A: 'General Missions',
B: 'Asia Missions',
C: 'Building Fund',
D: 'Youth Funds',
E: 'College Programs',
F: 'Other',
G: 'Tree Removal Emergency Fund'
},
credentials: '{"merchantId":"00000340001","merchantFakeLogin":"API00045401"}',
gateway: 'paygate',
sV: '1'
},
{
_id: ObjectId("642501ea4ebcba0650a0f1fa"),
key: 'hecy',
name: 'Higher Education Services',
designations: {
A: 'General Fund',
B: 'Alumni Persistence Fund',
C: 'Area of Greatest Need'
},
gateway: 'paygate',
credentials: '{"merchantId":"0003400001","merchantLogin":"API000004501"}',
sV: '1'
}
]

"transactions" collection:

[
{
"_id": "6419e04bf4e8a858418a791a",
"userId": "guest",
"firstName": "Jake",
"lastName": "G",
"email": "",
"amount": "1.31",
"freq": "none",
"scheduled": false,
"emailSent": false,
"timestamp": "2023-03-21T16:50:19.566Z",
"accType": "VISA",
"accNumTrunc": "1593",
"orgKey": "hecy",
"orgName": "Boston Higher",
"designations": [
{
"id": 0,
"amt": 1.31,
"des": "A"
}
],
"sV": "2",
"confirmationId": "23032111501826241"
},
{
"_id": "6419e4fb056a285a147aa1dd",
"userId": "guest",
"firstName": "Joaquin",
"lastName": "Garghdo",
"email": "",
"amount": "100",
"freq": "none",
"scheduled": false,
"emailSent": false,
"timestamp": "2023-03-21T17:10:18.844Z",
"accType": "MSTR",
"accNumTrunc": "5454",
"orgKey": "clj",
"orgName": "nonprofit clj",
"designations": [
{
"id": 0,
"amt": 100,
"des": "A"
}
],
"note": "",
"sV": "2",
"confirmationId": "23032112101833311TEST"
},
{
"_id": "6419e520056a285a147aa1de",
"userId": "guest",
"firstName": "James",
"lastName": "McCern",
"email": "",
"amount": "10.1",
"freq": "none",
"scheduled": false,
"emailSent": false,
"timestamp": "2023-03-21T17:11:32.956Z",
"accType": "MSTR",
"accNumTrunc": "5228",
"orgKey": "hecy",
"orgName": "Higher Education Services",
"designations": [
{
"id": 0,
"amt": 10,
"des": "B"
}
],
"sV": "2",
"confirmationId": "23032112105521283"
}
]

"transactionsDetails" view:

[
{
"_id": "6419e04bf4e8a858418a791a",
"userId": "guest",
"firstName": "Jake",
"lastName": "G",
"email": "",
"amount": "1.31",
"freq": "none",
"scheduled": false,
"emailSent": false,
"timestamp": "2023-03-21T16:50:19.566Z",
"accType": "VISA",
"accNumTrunc": "1593",
"orgKey": "hecy",
"orgName": "Boston Higher",
"designations": [
{
"id": 0,
"amt": 1.31,
"des": "General Fund"
}
],
"sV": "2",
"confirmationId": "23032111501826241"
},
{
"_id": "6419e4fb056a285a147aa1dd",
"userId": "guest",
"firstName": "Joaquin",
"lastName": "Garghdo",
"email": "",
"amount": "100",
"freq": "none",
"scheduled": false,
"emailSent": false,
"timestamp": "2023-03-21T17:10:18.844Z",
"accType": "MSTR",
"accNumTrunc": "5454",
"orgKey": "clj",
"orgName": "nonprofit clj",
"designations": [
{
"id": 0,
"amt": 100,
"des": "General Support"
}
],
"note": "",
"sV": "2",
"confirmationId": "23032112101833311TEST"
},
{
"_id": "6419e520056a285a147aa1de",
"userId": "guest",
"firstName": "James",
"lastName": "McCern",
"email": "",
"amount": "10.1",
"freq": "none",
"scheduled": false,
"emailSent": false,
"timestamp": "2023-03-21T17:11:32.956Z",
"accType": "MSTR",
"accNumTrunc": "5228",
"orgKey": "hecy",
"orgName": "Higher Education Services",
"designations": [
{
"id": 0,
"amt": 10,
"des": "Alumni Persistence Fund"
}
],
"sV": "2",
"confirmationId": "23032112105521283"
}
]

2

Hi @om341,

You might want to look into a lookup in mongodb to see if you can do this at the query level, but you can do it in JS as well, either via a transformer or directly in the row as you're doing now. You might need to tweak this a bit for your exact structure, but I think something like this should work.

I'm not entirely sure on the need for JSON.parse here, but I'll leave it in.

// Use an IIFY (immediately invoked function expression) because it's cleaner and allows multi-lines
{{ (() => {
  let desCode = JSON.parse(currentSourceRow.designations)[0].des // A
  let orgKey = currentSourceRow.orgKey // hecy

  let orgCollectionData = orgCollection.data // Adjust to whatever it actually is
  // This will search for the org record matching the org key, look at the desinations property, and pull out the value mapped to the orgKey
  let orgDesName= orgCollectionData.find( org => org.key === orgKey ).designations[desCode] // General Fund
  return orgDesName

})()
}}

Let me know if that works of if you run into any problems.

3

One other approach would be to leave the two objects as sources and use Query JSON with SQL to join them on the common ID.

4

Thank you MikeCB! This worked directly in the row after making some minor structural tweaks (comments inline below) as you noted:

{{ (() => {
//JSON.parse was used because the nested "designations" element was stored in Mongodb as a string instead of a JSON object
let desCode = JSON.parse(currentSourceRow.designations)[0].des // A

let orgKey = currentSourceRow.orgKey // hecy
let orgCollectionData = Organizations.data // "Organizations" is the name of the collection
// This will search for the org record matching the org key, look at the desinations property, and pull out the value mapped to the orgKey
let orgDesName= orgCollectionData.find( org => org.key === orgKey ).designations[desCode] // General Fund

return orgDesName
})()
}}