JSON Type¶
CREATE TABLE example (student JSON);
INSERT INTO example VALUES ('{ "id":1, "name": "Tommy", "class": "1-2" }');
INSERT INTO example VALUES ('{ "id":2, "name": "Jack", "class": "1-3" }');
INSERT INTO example VALUES ('{ "id":3, "name": "Rose", "class": "1-2" }');
INSERT INTO example VALUES ('{ "id":4, "name": "Tommy", "class": "1-3" }');
JSON Operators¶
Use ->'field' to access the top field. field is case-sensitive.
Filter with JSON Field¶
XDB> SELECT * FROM example WHERE student->'id'=1;
+---------------------------------------------+
| student |
+---------------------------------------------+
| { "id":1, "name": "Tommy", "class": "1-2" } |
+---------------------------------------------+
XDB> SELECT * FROM example WHERE student->'name'='Tommy';
+---------------------------------------------+
| student |
+---------------------------------------------+
| { "id":1, "name": "Tommy", "class": "1-2" } |
| { "id":4, "name": "Tommy", "class": "1-3" } |
+---------------------------------------------+
Select with JSON Field¶
The return type is VARCHAR.
XDB> SELECT student->'id',student->'name',student->'class' FROM example;
+---------------+-----------------+------------------+
| student->'id' | student->'name' | student->'class' |
+---------------+-----------------+------------------+
| 1 | Tommy | 1-2 |
| 2 | Jack | 1-3 |
| 3 | Rose | 1-2 |
| 4 | Tommy | 1-3 |
+---------------+-----------------+------------------+
XDB> SELECT student->'id' AS id,student->'name' AS name,student->'class' AS class FROM example ;
+----+-------+-------+
| id | name | class |
+----+-------+-------+
| 1 | Tommy | 1-2 |
| 2 | Jack | 1-3 |
| 3 | Rose | 1-2 |
| 4 | Tommy | 1-3 |
+----+-------+-------+
Index on JSON Field¶
You can accelerate querying JSON data by using secondary indexes directly without extra generated column like MySQL.