See which values in one array are not present in another array with a different key structure

Hi Retool community,

I have a use case where I have 2 JSON to SQL queries and am trying to see which values are not shared by both the queries. The problem is the key structure is different in both queries so I cant seem to compare them. I have some screenshots below.

Query 1 json key structure:

Query 2 json structure:

Does anyone know how I can compare these queries and parse out the values that are not shared between the queries?

Thanks all!

You can create JS query that looks like this:

// Extract the values from query1
const query1Values = => obj.carrier_opt);

// Extract the values from query2
const query2Values = query2.flatMap(obj => obj.externalRefs);

// Find the values that are present in query1 but not in query2
const uniqueValuesOfQuery1 = query1Values.filter(value => !query2Values.includes(value));

const uniqueValuesOfQuery2 = query2Values.filter(value => !query1Values.includes(value));

const result = [...uniqueValuesOfQuery1, ...uniqueValuesOfQuery2]

return result;

P.S. Kudos to ChatGPT. I literary fed it your question. The hardest part was to parse text from screenshots where iPhone's text recognition came to help

Hi @preshetin, thanks for the guidance and ChatGPT is great, solution works great! However when I try to put my js query results in a table I get "no data", the table is blank but shows 3 results, but I am not seeing them, I snipped the screenshot below. What am I missing?

Hi @AccoladeRetool - why do you want to show this list of strings in a table? This feels a bit redundant for displaying just a list of strings.

If you still want to display it in a table please describe what rows and columns this table should have.

The alternative to a table could be a list view. It can accept data structure like an array of strings as data source:

Hi @preshetin, thank you, actually what I would like to do is join this result to an existing Query JSON with SQL query result and for each row that is shared, highlight the color of the row to a different color on the table component. But when I try to query this js query result with sql I get the results as key letters like below
Is there an easy way to join these together and change table row colors with the shared values?

Hi Jeff, this joining & displaying colored result task goes beyond the initial topic. I suggest you create a separate topic for this. I will look into it when I have time.

If you have budget feel free to send me a private message. I will be happy to help!

As per current topic in case one of my posts solved you initial request feel free to mark it as a Solution so that other people can benefit

Hi @preshetin Appreciate your help on this, I have marked the initial topic as resolved. Thank you for taking a look at the joining and displaying colored result if you have the bandwidth. I will post as a new topic.

1 Like