Computed Columns

Off-Topic
1

Would be nice if computed columns were treated like a programming language.

Right now, they are mapped to a specific path and column. But I have a specific use case, where a JSON column has meta data for a video, image and pdf file. Each JSON structure is completely different. So a computed column cannot be used.

Would be nice if I was able to say.

For computed column Width: If column == “image”, path == …
For computed column Width: If column == “video”, path == …
For computed column Width: If column == “pdf”, path == …

2

Computed columns can contain any valid SQL expression, including conditionals. Here’s an example that I think would work for your use case:

create table test(val json, 
                  Width as DECODE(val::$type, 'image', val::%imageWidth, 'video', val::%videoWidth, 'pdf',val::%pdfWidth) persisted int);

insert into test values('{"type":"image", "imageWidth": "1"}'),
                       ('{"type":"video", "videoWidth": "2"}');

> select val::$type, Width from test; 
+-------------------------------------------------------------+
| val::$type                   | Width                        |
+-------------------------------------------------------------+
| image                        | 1                            |
| video                        | 2                            |
+-------------------------------------------------------------+

In addition, we’re also considering adding support for UDF’s inside computed columns which would allow you to do more complex logic.

3

Thanks for taking a look at this so soon.

Thanks for the example, but I think I will wait for the UDF. So far, I’m peeling away the data points from JSON and updating the individual columns that I’m going to search on.

It works for now. Will refactor when the UDF is out because I have some complex use cases.

Many thanks