JSON Type

Syntax

  1. Tag of type JSON

    1. create STable s1 (ts timestamp, v1 int) tags (info json);
    3. create table s1_1 using s1 tags ('{"k1": "v1"}');

  2. “->” Operator of JSON

    1. select * from s1 where info->'k1' = 'v1';
    3. select info->'k1' from s1;

  3. “contains” Operator of JSON

    1. select * from s1 where info contains 'k2';
    3. select * from s1 where info contains 'k1';

Applicable Operations

  1. When a JSON data type is used in where, match/nmatch/between and/like/and/or/is null/is no null can be used but in can’t be used.

    1. select * from s1 where info->'k1' match 'v*';
    3. select * from s1 where info->'k1' like 'v%' and info contains 'k2';
    5. select * from s1 where info is null;
    7. select * from s1 where info->'k1' is not null;

  2. A tag of JSON type can be used in group by, order by, join, union all and sub query; for example group by json->'key'

  3. Distinct can be used with a tag of type JSON

    1. select distinct info->'k1' from s1;

  4. Tag Operations

    The value of a JSON tag can be altered. Please note that the full JSON will be overriden when doing this:

    1. alter table s1_1 set tag info = '{"k1": "v2"}';

    The name of a JSON tag can be altered:

    1. alter stable s1 change tag info info2 ;

    A tag of JSON type can’t be added or removed. The column length of a JSON tag can’t be changed.

Other Restrictions

  • JSON type can only be used for a tag. There can be only one tag of JSON type, and it’s exclusive to any other types of tags.

  • The maximum length of keys in JSON is 256 bytes, and key must be printable ASCII characters. The maximum total length of a JSON is 4,096 bytes.

  • JSON format：

    • The input string for JSON can be empty, i.e. “”, “\t”, or NULL, but it can’t be non-NULL string, bool or array.
    • object can be {}, and the entire JSON is empty if so. Key can be “”, and it’s ignored if so.
    • value can be int, double, string, bool or NULL, and it can’t be an array. Nesting is not allowed which means that the value of a key can’t be JSON.
    • If one key occurs twice in JSON, only the first one is valid.
    • Escape characters are not allowed in JSON.

  • NULL is returned when querying a key that doesn’t exist in JSON.

  • If a tag of JSON is the result of inner query, it can’t be parsed and queried in the outer query.

For example, the SQL statements below are not supported.

  1. select jtag->'key' from (select jtag from STable);
  2. select jtag->'key' from (select jtag from STable) where jtag->'key'>0;