How to merge multiple arrays in one?

Help JSON
1

If i have table with json field like
1 … [‘label_1’, ‘label_2’]
2 … [‘label_3’, ‘label_4’]
How can i merge them to get result as [‘label_1’, ‘label_2’, ‘label_3’, ‘label_4’] ?

2

Here’s one way to do it:

create table t(id int, j json);

insert t values
  (1 , '["label_1", "label_2"]'), 
  (2 , '["label_3", "label_4"]');

with r as (
  select id, x.table_col as c from t join table(json_to_array(j)) as x
)
select json_agg(c) from r;

Result:

+-------------------------------------------+
| json_agg(c)                               |
+-------------------------------------------+
| ["label_1","label_2","label_3","label_4"] |
+-------------------------------------------+
2 Likes
3

Thanks, how can i use this query in functions or aggregate function?? As i know i can’t call query in functions, only in procedures, but in procedures i cant return values? in general, I need to write an aggregation function that will return the result of this query and which I can call in sqlalchemy

4

You can return a value from an SP using RETURN and you can output a rowset from an SP with ECHO SELECT. If using ECHO SELECT, you can issue a CALL spName(…) from your app, and it will return a rowset, and your app can consume it.

You could also create a UDAF that combines the arrays in one step if that’s easier for you. You could use that in any SELECT.

1 Like