17-json.md

---
title: JSON Type
---

## Syntax

1. Tag of type JSON

   ```sql
   create STable s1 (ts timestamp, v1 int) tags (info json);

   create table s1_1 using s1 tags ('{"k1": "v1"}');
   ```

2. "->" Operator of JSON

   ```sql
   select * from s1 where info->'k1' = 'v1';

   select info->'k1' from s1;
   ```

3. "contains" Operator of JSON

   ```sql
   select * from s1 where info contains 'k2';

   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.

   ```sql
   select * from s1 where info->'k1' match 'v*';

   select * from s1 where info->'k1' like 'v%' and info contains 'k2';

   select * from s1 where info is null;

   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

   ```sql
   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.

   The name of a JSON tag can be altered. 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.

```sql;
select jtag->'key' from (select jtag from STable);
select jtag->'key' from (select jtag from STable) where jtag->'key'>0;
```