CREATE VIRTUAL COLUMN

Introduced or updated: v1.2.339

ENTERPRISE EDITION FEATURE

VIRTUAL COLUMN is an Enterprise Edition feature. Contact Databend Support for a license.

Creates virtual columns for a table. Please note that virtual columns exclusively support the FUSE Engine, are designed for exclusive compatibility with the Variant data type. Refer to Accessing Elements in JSON for column definition.

Please note that after creating virtual columns for a table that already contains Variant data, it is necessary to refresh the virtual columns using the REFRESH VIRTUAL COLUMN command.

Syntax

CREATE [ OR REPLACE ] VIRTUAL COLUMN [ IF NOT EXISTS ] ( <virtual_column_1>, <virtual_column_2>, ... ) FOR <table>

Examples

This example creates virtual columns for a table named 'test':

-- Create a table named 'test' with columns 'id' and 'val' of type Variant.
CREATE TABLE test(id int, val variant);

-- Insert a sample record into the 'test' table with Variant data.
INSERT INTO
  test
VALUES
  (
    1,
    '{"id":1,"name":"databend","tags":["powerful","fast"],"pricings":[{"type":"Standard","price":"Pay as you go"},{"type":"Enterprise","price":"Custom"}]}'
  );

-- Create virtual columns for specific elements in the 'val' column.
CREATE VIRTUAL COLUMN (
  val ['name'],                 -- Extract the 'name' field.
  val ['tags'] [0],             -- Extract the first element in the 'tags' array.
  val ['pricings'] [0] ['type'] -- Extract the 'type' field from the first pricing in the 'pricings' array.
) FOR test;

REFRESH VIRTUAL COLUMN FOR test;