How to work with column containg array in H2 database to get n 1 row

Question

I am new to H2 database. I have 3 tables in database for example,

table t1 :

ID	desc	something	condition_id
1	EN 142868 151508	XYZ	[4686, 4687]

table condition

CONDITION_ID	desc	COLUMN3
4686	desc1	something1
4687	desc2	something2

And I need output like this

ID	desc	t1something	joined Column
1	EN 142868 151508	XYZ	desc1 something1, desc2 somethin2

How to do this, I tried with array_contains, but its giving 2 rows as a result like' t1ID t1desc t1something joined Column 1 EN 142868 151508 XYZ desc1 something1 1 EN 142868 151508 XYZ desc2 somethin2

Please help. Thank you in advance

Answer 1

You need to group rows from table T1 and aggregate rows from table CONDITION:

CREATE TABLE T1(ID INT, DESC VARCHAR, SOMETHING VARCHAR, CONDITION_ID INT ARRAY)
AS VALUES (1, 'EN 142868 151508', 'XYZ', ARRAY[4686, 4687]);

CREATE TABLE CONDITION(CONDITION_ID INT, DESC VARCHAR, COLUMN3 VARCHAR)
AS VALUES (4686, 'desc1', 'something1'), (4687, 'desc2', 'something2');

SELECT T1.ID, T1.DESC, T1.SOMETHING,
    LISTAGG(CONDITION.DESC || ' ' || CONDITION.COLUMN3, ', ') JOINED_COLUMN
FROM T1 LEFT JOIN CONDITION ON ARRAY_CONTAINS(T1.CONDITION_ID, CONDITION.CONDITION_ID)
GROUP BY T1.ID, T1.DESC, T1.SOMETHING;
> ID | DESC             | SOMETHING | JOINED_COLUMN
> -- | ---------------- | --------- | ----------------------------------
> 1  | EN 142868 151508 | XYZ       | desc1 something1, desc2 something2

If you want to exclude rows from T1 without corresponding entries in CONDITION, use inner JOIN instead of LEFT JOIN.

You may also want to add WITHIN GROUP (ODRER BY some_columns) clause to LISTAGG aggregate function if you need some exact order of entries:

LISTAGG(CONDITION.DESC || ' ' || CONDITION.COLUMN3, ', ')
WITHIN GROUP (ORDER BY CONDITION.CONDITION_ID)
JOINED_COLUMN

Alternatively, you can use a subquery:

SELECT T1.ID, T1.DESC, T1.SOMETHING,
  (
    SELECT LISTAGG(CONDITION.DESC || ' ' || CONDITION.COLUMN3, ', ')
    FROM CONDITION
    WHERE ARRAY_CONTAINS(T1.CONDITION_ID, CONDITION.CONDITION_ID)
  ) JOINED_COLUMN
FROM T1;