Variant data type

Variant compared with JSON and JSONB

JSON, JSONB, and Variant all represent semi-structured data, but they differ in where the data lives, how it is encoded, and how queries are optimized. For details about JSON and JSONB, see JSON and JSONB.

Common operators

Variant supports the following operators. Their semantics match the JSON and JSONB operators.

Typed casts

Use the ::type syntax to cast a subfield directly to a native type, without first converting it to TEXT. The following target types are supported.

Column pruning

When a query touches only a few fields of a Variant object, the engine reads just the shredded physical subcolumns and skips the rest. For example, in a Variant column with 20 fields, a query on v->'age' reads only the typed_value column of age, which costs about as much I/O as reading a single plain INT column.

Row group data skipping

Shredded subcolumns automatically carry Parquet row group statistics (min, max, and null_count). Before scanning, the query engine uses these statistics to skip row groups that cannot match the filter, which greatly reduces the amount of data read.

The following point query runs against a table with 500,000 rows and skips most of the data based on the row group statistics of a shredded subcolumn.

SELECT id, (v->'seq')::int8 AS seq
FROM ext_dlf.variant_acc.v_skip
WHERE (v->'seq')::int8 = 250000;

Run EXPLAIN ANALYZE to verify that data skipping takes effect.

->  Seq Scan on v_skip
      Filter: (((v -> 'seq'::text))::bigint = '250000'::bigint)
      RowGroupFilter: (((v -> 'seq'::text))::bigint = '250000'::bigint)
      [scan_rows=3868]

In this plan, RowGroupFilter shows that the filter is pushed down to row group pruning: only 3,868 of the 500,000 rows are actually scanned.

Read a Variant column

The following example reads a Variant column from a Paimon table through a DLF external database. For the full syntax, see CREATE EXTERNAL DATABASE. For the end-to-end procedure of accessing Paimon data through DLF, see Access Paimon data with DLF Catalog.

-- 1. Create an external database if you do not have one.
CREATE EXTERNAL DATABASE ext_dlf WITH
  catalog_type 'paimon'
  metastore_type 'dlf-rest'
  dlf_catalog '<YOUR_CATALOG_NAME>'
  comment 'DLF Paimon catalog';

-- 2. Read the Variant column.
SELECT id, v::text
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
ORDER BY id
LIMIT 10;

A sample result is as follows.

 id |                    v
----+-----------------------------------------
  0 | {"age": 21, "city": "Beijing"}
  1 | {"age": 27}
  2 | {"city": "Beijing", "other": "xxx"}
  3 | {"other": "yyy"}
  4 | {"age": 28, "nested": {"k": [1, 2, 3]}}
(5 rows)

Project multiple fields

You can extract several fields from a Variant object in one query and cast each field independently.

SELECT
  id,
  v::text AS raw,
  (v->'age')::int8 AS age,
  (v->'city')::text AS city
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
ORDER BY id;

A sample result is as follows.

 id |                   raw                   | age  |   city
----+-----------------------------------------+------+-----------
  0 | {"age": 21, "city": "Beijing"}          |   21 | "Beijing"
  1 | {"age": 27}                             |   27 |
  2 | {"city": "Beijing", "other": "xxx"}     |      | "Beijing"
  3 | {"other": "yyy"}                        |      |
  4 | {"age": 28, "nested": {"k": [1, 2, 3]}} |   28 |
(5 rows)

Access a nested path

Use the #> operator to access a nested object.

SELECT (v#>'{nested,k}')::text AS nested_array
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
WHERE id = 4;

The result is as follows.

 nested_array
--------------
 [1, 2, 3]
(1 row)

Access array elements

The following example accesses each element of a row whose Variant value is [10, 20, 30].

SELECT
  (v->0)::text AS idx0,
  (v->1)::text AS idx1,
  (v->2)::text AS idx2
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
WHERE id = 5;

The result is as follows.

 idx0 | idx1 | idx2
------+------+------
 10   | 20   | 30
(1 row)

Check whether a key exists

Use the ? operator to check whether a key exists.

SELECT id, v?'age' AS has_age
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
WHERE id IN (0, 3)
ORDER BY id;

The result is as follows.

 id | has_age
----+---------
  0 | t
  3 | f
(2 rows)

Sort and aggregate by a Variant field

After a typed cast, a Variant subfield can be used directly in ORDER BY and GROUP BY clauses.

-- Sort by the age field.
SELECT id
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
ORDER BY (v->'age')::int8 NULLS LAST
LIMIT 5;

-- Aggregate by the age field.
SELECT (v->'age')::int8 AS age, COUNT(*)
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
GROUP BY 1
ORDER BY 1 NULLS LAST;

Access a field that does not exist

The root value of a Variant column can be any JSON value, not only an object. For a non-object root value such as an array, string, number, boolean, or null, accessing a key with -> returns SQL NULL. In the following example, the Variant values of rows 5 to 9 are [10,20,30], "scalar string", 12345, true, and null.

SELECT
  id,
  v::text,
  (v->'nokey')::int8 AS n_int,
  (v->'nokey')::text AS n_text
FROM ext_dlf.<YOUR_SCHEMA>.<YOUR_TABLE>
WHERE id >= 5
ORDER BY id;

The result is as follows.

 id |        v        | n_int | n_text
----+-----------------+-------+--------
  5 | [10, 20, 30]    |       |
  6 | "scalar string" |       |
  7 | 12345           |       |
  8 | true            |       |
  9 | null            |       |
(5 rows)