Joining 2 arrays and returning the difference

[Mangorama]

Hello,
I'm posting here because I'm hoping to get some help with my query. I'm running into a problem where I have 2 similarly formatted arrays with the same index but when I try to join them in a JSON SQL query on that index it doesn't return anything. I tried looking up this issue and saw that a couple people have run into it but I never saw a solution that worked. Down below is a picture of the query I'm using

The end goal of this is to join the arrays and be able to return only the oeEEIDarray entries that arent in the nyfEEIDarray.

The only issue seems to be the JOIN function on these arrays as I can query them both separately with WHERE clauses.

[ScottR]

Welcome to the forum

Have you tried using lodash _.join() in combination with _.pick() in a transformer?

[Mangorama]

Thank you for the welcome, and no I haven't. I've not the most familiar with lodash's capabilities, what would be a high level overview of how to leverage those 2 functions?

[ScottR]

Try using concat instead actually
as an example in a JS Query If I run the following I will get back an array containing 1,2,3,4,5

const array1 = [1,2,3];
const array2 = [2,3,4,5];
return _.uniq(_.concat(array1, array2))

Let me know if there is anything else you need

[Mangorama]

Okay, so I just tried that and instead of returning a list of unique entries returns array with both sets of data. I'm using similar data for both arrays so this shouldn't be the case . Is it not working as intended b/c my arrays are of key:value pairs?

Example:

oeEEIDarray = [ {employeeId: "____"} , {employeeId: "____" }, ... ]

[ScottR]

const array1 = [{"employee_id":1},{"employee_id":2},{"employee_id":3}];
const array2 = [{"employee_id":2},{"employee_id":3},{"employee_id":4}];
return _.uniqBy(_.concat(array1, array2), "employee_id")

[Mangorama]

That filters in all the unique values, and it's definitely a step in the right direction but would there be a way to return items from only one of these arrays?

To use your code above as an example: Would there be a way to return just [{"employee_id":1}]?

The reasoning behind this is bc array1 is an earlier set of data and array2 is more recent. I'm trying to see which "employee_id" values are no longer being received. Let me know if I can explain anything better.

This would solve my whole conundrum haha. Thank you!

edit: I'm going to try using this in the JSON SQL query, don't know why I didn't realize that's what this is for initially

[ScottR]

I think it all depends on what the data looks like, if you're looking for differences between the two that is different than joining them and only returning unique values....
I am still not clear on what you need to accomplish....

[Mangorama]

You have the data structure correct in your 2nd example
To use your example above, I have data that looks like this:

const array1 = [{"employee_id":1},{"employee_id":2},{"employee_id":3}];
const array2 = [{"employee_id":2},{"employee_id":3},{"employee_id":4}];

And I want to get the entries unique to just array1, so just uniquearray = [{"employee_id":1}]. In my original post I was doing a left join b/c I wanted to see:

WHERE array2.employee_id IS NULL

I hope any of that made sense haha ,but please let me know if you want me to elaborate more.

[ScottR]

So you want to get unique employee IDs in array1 but not array2? If so

const array1 = _.uniqBy([{"employee_id":1},{"employee_id":1},{"employee_id":3}], "employee_id");
const array2 = [{"employee_id":2},{"employee_id":3},{"employee_id":4}];
return _.concat(array1, array2)

[Mangorama]

Sorry, let me try to phrase better. So going back to:

const array1 = [{"employee_id":1},{"employee_id":2},{"employee_id":3}];
const array2 = [{"employee_id":2},{"employee_id":3},{"employee_id":4}];

I want to get [{"employee_id":1}] because it's only in array1 and not array2. That's why originally I was trying

SELECT array1.employee_id 
FROM array1 
LEFT JOIN array2 ON array1.employee_id = array2.employee_id 
WHERE array2.employee_id IS NULL

When I tried to preview the results of that JSON SQL query I got message: "Cannot read properties of undefined (reading 'length')"

[pyrrho]

That's a weird error to get when you don't seem to be referencing the length property of anything in your query

Have you tried your query with a NOT EXISTS clause rather than using the join?

SELECT employee_id FROM array1 WHERE 
NOT EXISTS 
(SELECT employee_id FROM array2 WHERE array1.employee_id = array2.employee_id)

[Mangorama]

Isn't it??? And I just went and tried that and this time got a different error when I tried to preview:

message:"Data source number 0 in undefined"

Which I don't understand because every value is defined in the arrays. Here's my exact query for more context.

[ScottR]

Still working...

[pyrrho]

@Mangorama

const array1 = [{"employee_id":1},{"employee_id":2},{"employee_id":3}];
const array2 = [{"employee_id":2},{"employee_id":3},{"employee_id":4}];

I took these into a new JS Query (query5) and returned [array1, array2] for this, but it seems to have worked:

select a.employee_id from {{query5.data[0]}} a WHERE
NOT EXISTS
(select b.employee_id from {{query5.data[1]}} b WHERE a.employee_id = b.employee_id)

[ScottR]

function findUniqueItem(arr1, arr2) {
  return arr1.filter(item => !arr2.includes(item));
}

const firstArray = [1, 2, 3, 4, 5];
const secondArray = [2, 4, 6];

const uniqueItem = findUniqueItem(firstArray, secondArray);
return uniqueItem

[Mangorama]

I tried nesting the arrays inside a new array like you did, adjusted your aliases, ran the code and received another error.

Here's the nesting query for more context:

const oearray = oeEEIDarray.data;
const nyarray = nyfEEIDarray.data;
const nestedarrays = [oearray, nyarray];

return nestedarrays

I'm really confused as to why this isn't working, esp b/c you got it to work for yourself.

[pyrrho]

Can you remove the "AS" modifier from your query? I don't think you need that unless you are renaming fields.

select * from {{array.data}} a instead of select * from {{array.data}} as a

[Mangorama]

Affirmative, removed "as" and got the same error.... Just realized i didn't rename 'b' in the NOT EXISTS clause . Correcting now

[pyrrho]

For testing, can you replace the oeEEIDarray.data and nyfEEIDarray.data calls in your nesting query to just contain a sample dataset?

I feel like there is maybe an extra layer to your data that we aren't seeing -- if you can get my example to work with the simple array sets we can compare the structures and see if they are different.