Check if two arrays are exactly the same in BigQuery merge statement

Question

I have two tables in BigQuery that I am trying to merge. For the purpose of explanation, let us name the two tables as A and B. So, we merge B into A. Also, I have a primary key called id based on which I am performing the merge. Now, both of them have a column (let us name it as X for explanation purposes) which is of the type ARRAY. My main intention is to replace the array data in A with that of B if the arrays are not equal in both the table. How can I do that. I did find posts on SO and other sites but none of them are working in my usecase.

A                       B
----------              ----------
id | x                  id | x
----------              ----------
1  | [1,2]              1  | [1,2]
----------              ----------
2  | [3]                2  | [4, 5]

The result of the merge should be

A 
----------
id | x
----------
1  | [1,2]
----------
2  | [4,5]

How can I achieve the above result. Any leads will be very helpful. Also, if there are some other posts that address the above scenario directly, please point me to them

Edits:

I tried the following:

merge A as main_table
using B as updated_table
on main_table.id = updated_taable.id
when matched and main_table.x != updated_table.x then update set main_table.x = updated_table.x
when not matched then
    insert(id, x) values (updated_table.id, updated_table.x)
;

Hope, this helps.

Answer 1

I cannot direclty use a compare operator over array right. My use case is that only update values when they are not equal. So, i cannot use something like != directly. This is the main problem

You can use to_json_string function to compare two arrays "directly"

to_json_string(main_table.x) != to_json_string(updated_table.x)