Select columns from another table given an array with the column names

Question

Let's suppose I have this table meta_table

+------------+------------------------+
| table_name |       col_names        |
+------------+------------------------+
| t1         | {ID, PRICE, OPERATION} |
| t2         | {TYPE, PRICE, ANGLE}   |
+------------+------------------------+

where col_names has an array structure.

t1 and t2 have the columns contained in the array + some other columns which don't appear in the array.

First question: How can I select from tables t1 and t2 only the columns contained in the array from the column col_names?

Second question: the main purpose is to count the number of missings for each column within the array for each table and return it in a new column with an array structure:

+------------+------------------------+-----------+
| table_name |       col_names        |  n_miss   |
+------------+------------------------+-----------+
| t1         | {ID, PRICE, OPERATION} | {0, 1, 0} |
| t2         | {TYPE, PRICE, ANGLE}   | {0, 0, 1} |
+------------+------------------------+-----------+

Any ideas on how to do it?

I'm using Clickhouse

Many thanks in advance!

It is not possible to do using SQL - you cannot form the dynamic queries. Use the server-side to solve this task. — vladimir
Thanks vladimir, as you said, I didn't find a way to do it so I ended up exporting the table and doing it in R — piblo95

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-11-07T13:18:35

This answers the original version of the question.

If I understand correctly, you can use array join. I am not very familiar with Clickhouse syntax, but I think this is what you want:

select a.*
from t1 array join
     t1.array as a;

Select columns from another table given an array with the column names

1 Answers