Skip to main content

JSON PATH

Databend supports Semi-structured data type and allow retrieving the inner elements by JSON path operators:

Syntax

Colon Notation

Colon notation : is used to retrieving element of object by name: <column>:<level1_name>:<level2_name>.

Dot Notation

Dot notation . is used to retrieving element of object by name: <column>:<level1_name>.<level2_name>.

note

Please note that dot notation cannot be used as first-level name notation to avoid confusion with dot notation between table and column.

Bracket Notation

Bracket notation [] is used to retrieving element of array by index: <column>[<level1_index>][<level2_index>] or element of object by single quoted name: <column>['<level1_name>']['<level2_name>'].

tip

These notations can be mixed in use.

Examples

CREATE TABLE test(var Variant, arr Variant);
INSERT INTO test VALUES (parse_json('{"a":{"b":1,"c":[1,2]}}'), parse_json('[["a","b"],{"k":"a"}]')),
(parse_json('{"a":{"b":2,"c":[3,4]}}'), parse_json('[["c","d"],{"k":"b"}]'));

-- Colon Notation
SELECT var:a:b FROM test;
+---------+
| var:a:b |
+---------+
| 1 |
| 2 |
+---------+

-- Dot Notation
SELECT var:a.c FROM test;
+---------+
| var:a.c |
+---------+
| [1,2] |
| [3,4] |
+---------+

-- Bracket Notation
SELECT var['a']['c'], arr[0][1] FROM test;
+---------------+-----------+
| var['a']['c'] | arr[0][1] |
+---------------+-----------+
| [1,2] | "b" |
| [3,4] | "d" |
+---------------+-----------+

-- Mixed Notations
SELECT var['a']:b, var:a['c'][0], arr[1].k FROM test;
+------------+---------------+----------+
| var['a']:b | var:a['c'][0] | arr[1].k |
+------------+---------------+----------+
| 1 | 1 | "a" |
| 2 | 3 | "b" |
+------------+---------------+----------+
Explore Databend Cloud for FREE
Low-cost
Fast Analytics
Easy Data Ingestion
Elastic Scaling
Try it today