Search code examples
javaarraysh2

How to manipulate array values in h2?


How can I retract and update values in an array in h2?

Since I'm trying to manage a Set of distinct values in an array I don't know the ordinality/position of values in the array and can therefore only operate by value and not use the position of values in the array - if possible?

(I can live with duplicate values in the array since I load queried array values into Sets that will automatically filter out duplicate values.)

I can add a value with concatenation:

DROP TABLE A IF EXISTS;
CREATE TABLE A(id INT PRIMARY KEY AUTO_INCREMENT, ints INT ARRAY);
INSERT INTO A(ints) VALUES (ARRAY[1, 2]);
SELECT ints FROM A WHERE id = 1;
// [1, 2]

// Concatenate
UPDATE A SET ints = ints || 3;
SELECT ints FROM A WHERE id = 1;
// [1, 2, 3]

But how can I retract a specific value?

// How to retract 2?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [1, 3]

// Or, how to retract 1 and 2?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [3]

And how can I update one value to another?

// How to replace 1 with 10?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [10, 2, 3]

// Or, how to replace 1 with 10 and 3 with 30?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [10, 2, 30]

I have tried variations on unnesting the values and filter them to exclude (retract) certain values, then aggregate them back along the following (wrong) lines without success:

UPDATE A 
SET ints = ARRAY_AGG(
   (SELECT * FROM UNNEST(SELECT ints FROM A) WHERE c1 != 2)
) 
WHERE id = 1;

UPDATE: I can get the desired filtered values:

SELECT * FROM UNNEST(SELECT ints FROM A) where c1 != 2;
// C1
// 1
// 3

But then I can't create an array from those values (that I can set ints to):

VALUES ARRAY(
   SELECT * FROM UNNEST(SELECT ints FROM A) where c1 != 2
);
// C1
// [null, null]

Solution

  • How to retract 2?

    UPDATE A
      SET ints = ARRAY(
      SELECT V
        FROM TABLE(V INT = (SELECT ints FROM A WHERE id = 1))
        WHERE V != 2
    );
    SELECT ints FROM A WHERE id = 1;
    // [1, 3]
    

    How to replace 1 with 10?

    UPDATE A SET ints = ARRAY[1, 2, 3];
    SELECT ints FROM A WHERE id = 1;
    UPDATE A
      SET ints = ARRAY(
      SELECT CASE WHEN(V = 1, 10, V)
        FROM TABLE(V INT = (SELECT ints FROM A WHERE id = 1))
    );
    SELECT ints FROM A WHERE id = 1;
    // [10, 2, 3]