{"action":"created","comment":{"url":"https://api.github.com/repos/pingcap/docs/pulls/comments/139612877","pull_request_review_id":63565852,"id":139612877,"diff_hunk":"@@ -0,0 +1,117 @@\n+---\n+title: Using TiDB as a Document Store\n+category: user guide\n+---\n+\n+# Using TiDB as a Document Store\n+\n+## About\n+\n+In order to functionally keep pace with the latest version of MySQL (version 5.7 and above) and provide better support for document storage business as well, TiDB supports JSON in the latest version. You can use JSON type fields in TiDB tables, and index the fields in the JSON document in the form of generated column. Based on this, you can flexibly deal with those business with uncertain schema, no longer limited by the bad read performance and lack of support for transaction in traditional document databases.\n+\n+## JSON Functions\n+\n+The support for JSON in TiDB mainly refers to the user interface of MySQL 5.7. For example, you can create a table that includes a JSON field to store complex information:\n+\n+```sql\n+CREATE TABLE person （\n+    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\n+    name VARCHAR(255) NOT NULL,\n+    address_info JSON\n+）\n+```\n+\n+When you insert data into a table, you can deal with those data with uncertain schema like this:\n+\n+```sql\n+INSERT INTO person (name, address_info) VALUES (\"John\", '{\"city\": \"Beijing\"}');\n+```\n+\n+Put a legal JSON string at the location corresponding to the JSON field, and then you can insert JSON into the table. TiDB will parse the text and saves it in a more compact and easy-to-access form.\n+\n+You can also convert other types of data into JSON using CAST:\n+\n+```sql\n+INSERT INTO person (name, address_info) VALUES (\"John\", CAST('{\"city\": \"Beijing\"}' AS JSON));\n+INSERT INTO person (name, address_info) VALUES (\"John\", CAST('123' AS JSON));\n+INSERT INTO person (name, address_info) VALUES (\"John\", CAST(123 AS JSON));\n+```\n+\n+Now, if you want to query all the users living in Beijing, what should you do? Is it necessary to pull back all the data and then filter in the business layer? Not really. The same with traditional document databases like MongoDB, TiDB supports complex combinations of query conditions in the user side.\n+\n+```sql\n+SELECT id, name FROM person WHERE JSON_EXTRACT(address_info, '$.city') = 'Beijing');\n+```\n+\n+`JSON_EXTRACT` is a function that TiDB supports. If you are familiar with JSON in MySQL 5.7, you must know it. The `JSON_EXTRACT` function in TiDB behaves the same as in MySQL. The meaning of the above function is to extract the `city` field from the `address_info` document. The second argument is a \"path expression\" and is used to specify which field to extract. The complete syntax description of the path expression is complex. Here is a few simple examples to help you understand its general use:\n+\n+```sql\n+SET @person = '{\"name\":\"John\",\"friends\":[{\"name\":\"Forest\",\"age\":16},{\"name\":\"Zhang San\",\"gender\":\"male\"}]}';\n+\n+SELECT JSON_EXTRACT(@person,  '$.name'); -- gets \"John\"\n+SELECT JSON_EXTRACT(@person,  '$.friends[0].age'); -- gets 16\n+SELECT JSON_EXTRACT(@person,  '$.friends[1].gender'); -- gets \"male\"\n+SELECT JSON_EXTRACT(@person,  '$.friends[2].name'); -- gets NULL\n+``` \n+\n+In addition to inserting and querying data, TiDB also support editing JSON. In general, TiDB currently support the following JSON functions in MySQL 5.7:\n+\n+- [JSON_EXTRACT](https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract)\n+- [JSON_ARRAY](https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-array)\n+- [JSON_OBJECT](https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-object)\n+- [JSON_SET](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-set)\n+- [JSON_REPLACE](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-replace)\n+- [JSON_INSERT](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-insert)\n+- [JSON_REMOVE](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-remove)\n+- [JSON_TYPE](https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-type)\n+- [JSON_UNQUOTE](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-unquote)\n+\n+You can get the general use of these functions directly from the function name. These functions in TiDB behave the same as in MySQL 5.7. If you want to query the specific use, see the [JSON Functions document of MySQL 5.7](https://dev.mysql.com/doc/refman/5.7/en/json-functions.html). If you are a user of MySQL 5.7, you can migrate seamlessly.\n+\n+Currently TiDB does not support all the JSON functions in MySQL 5.7. This is because our preliminary goal is to provide complete support for **MySQL X Plugin**, which covers the majority of JSON functions used to insert, select, update and delete data. More functions will be supported if necessary.\n+\n+## Index JSON Using Generated Column\n+\n+The full table scan is executed when you query a JSON field. When you run `EXPLAIN` statements in TiDB, the results show that it is full table scan. Then, can you index the JSON field?\n+\n+First, this type of index is wrong:\n+\n+```sql\n+CREATE TABLE person （\n+    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\n+    name VARCHAR(255) NOT NULL,\n+    address_info JSON,\n+    KEY (address_info)\n+）\n+```\n+\n+This is not because of technical impossibility but because the direct comparison of JSON itself is meaningless. Although we can agree on some comparison rules, such as `ARRAY` is bigger than all `OBJECT`, it is useless. Therefore, as what is done in MySQL 5.7, TiDB prohibits the direct creation of index on JSON field, but you can index the fields in the JSON document in the form of generated column:\n+\n+```sql\n+CREATE TABLE person （\n+    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\n+    name VARCHAR(255) NOT NULL,\n+    address_info JSON,\n+    city VARCHAR(64) GENERATED AS JSON_EXTRACT(address_info, '$.city') VIRTUAL,\n+    KEY (city)\n+）\n+```\n+\n+In this table, the `city` column is a **generated column**. As the name implies, the column is generated by other columns in the table, and cannot be assigned a value when inserted or updated. For generating a column, you can specify it as `VIRTUAL` to prevent it from being explicitly saved in the record, but by other columns when needed. This is particularly useful when the column is wide and you need to save storage space. With this generated column, you can create an index on it, and it looks the same with other regular columns. In query, you can run the following statements:  \n+\n+```sql\n+SELECT name, id FROM person WHERE city = 'Beijing'\n+```\n+\n+In this way, you can create an index. \n+\n+> **Note**: In the JSON document, if the field in the specified path does not exist, the result of `JSON_EXTRACT` will be `NULL`. The value of the generated column with index is also `NULL`. If this is not what you want to see, you can add a `NOT NULL` constraint on the generated column. In this way, when the value of the `city` field is `NULL` after you insert data, it can be detected.\n+\n+## Current Limitations         \n+\n+The current limitations of JSON and generated column are as follows:\n+\n+- You cannot add the generated column in the storage type of `STORED` through `ALTER TABLE`.\n+- You cannot create an index on the generated column through `ALTER TABLE`. \n+\n+The above functions and some other JSON functions are in development.","path":"sql/json-functions-generated-column.md","position":117,"original_position":117,"commit_id":"a36fd099760e4fd586ed65de74c7af68a298ef30","original_commit_id":"a36fd099760e4fd586ed65de74c7af68a298ef30","user":{"login":"andelf","id":72891,"avatar_url":"https://avatars1.githubusercontent.com/u/72891?v=4","gravatar_id":"","url":"https://api.github.com/users/andelf","html_url":"https://github.com/andelf","followers_url":"https://api.github.com/users/andelf/followers","following_url":"https://api.github.com/users/andelf/following{/other_user}","gists_url":"https://api.github.com/users/andelf/gists{/gist_id}","starred_url":"https://api.github.com/users/andelf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andelf/subscriptions","organizations_url":"https://api.github.com/users/andelf/orgs","repos_url":"https://api.github.com/users/andelf/repos","events_url":"https://api.github.com/users/andelf/events{/privacy}","received_events_url":"https://api.github.com/users/andelf/received_events","type":"User","site_admin":false},"body":"Is `under development` better?","created_at":"2017-09-19T07:16:13Z","updated_at":"2017-09-19T07:16:30Z","html_url":"https://github.com/pingcap/docs/pull/198#discussion_r139612877","pull_request_url":"https://api.github.com/repos/pingcap/docs/pulls/198","author_association":"MEMBER","_links":{"self":{"href":"https://api.github.com/repos/pingcap/docs/pulls/comments/139612877"},"html":{"href":"https://github.com/pingcap/docs/pull/198#discussion_r139612877"},"pull_request":{"href":"https://api.github.com/repos/pingcap/docs/pulls/198"}}},"pull_request":{"url":"https://api.github.com/repos/pingcap/docs/pulls/198","id":141752125,"html_url":"https://github.com/pingcap/docs/pull/198","diff_url":"https://github.com/pingcap/docs/pull/198.diff","patch_url":"https://github.com/pingcap/docs/pull/198.patch","issue_url":"https://api.github.com/repos/pingcap/docs/issues/198","number":198,"state":"open","locked":false,"title":"Create json-functions-generated-column.md","user":{"login":"lilin90","id":30922556,"avatar_url":"https://avatars1.githubusercontent.com/u/30922556?v=4","gravatar_id":"","url":"https://api.github.com/users/lilin90","html_url":"https://github.com/lilin90","followers_url":"https://api.github.com/users/lilin90/followers","following_url":"https://api.github.com/users/lilin90/following{/other_user}","gists_url":"https://api.github.com/users/lilin90/gists{/gist_id}","starred_url":"https://api.github.com/users/lilin90/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lilin90/subscriptions","organizations_url":"https://api.github.com/users/lilin90/orgs","repos_url":"https://api.github.com/users/lilin90/repos","events_url":"https://api.github.com/users/lilin90/events{/privacy}","received_events_url":"https://api.github.com/users/lilin90/received_events","type":"User","site_admin":false},"body":"PTAL","created_at":"2017-09-19T03:49:40Z","updated_at":"2017-09-19T07:16:30Z","closed_at":null,"merged_at":null,"merge_commit_sha":"8dd6bb29fa51c2154038c5ea3d437c7c1bdb00db","assignee":null,"assignees":[],"requested_reviewers":[{"login":"queenypingcap","id":19528243,"avatar_url":"https://avatars2.githubusercontent.com/u/19528243?v=4","gravatar_id":"","url":"https://api.github.com/users/queenypingcap","html_url":"https://github.com/queenypingcap","followers_url":"https://api.github.com/users/queenypingcap/followers","following_url":"https://api.github.com/users/queenypingcap/following{/other_user}","gists_url":"https://api.github.com/users/queenypingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/queenypingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/queenypingcap/subscriptions","organizations_url":"https://api.github.com/users/queenypingcap/orgs","repos_url":"https://api.github.com/users/queenypingcap/repos","events_url":"https://api.github.com/users/queenypingcap/events{/privacy}","received_events_url":"https://api.github.com/users/queenypingcap/received_events","type":"User","site_admin":false}],"milestone":null,"commits_url":"https://api.github.com/repos/pingcap/docs/pulls/198/commits","review_comments_url":"https://api.github.com/repos/pingcap/docs/pulls/198/comments","review_comment_url":"https://api.github.com/repos/pingcap/docs/pulls/comments{/number}","comments_url":"https://api.github.com/repos/pingcap/docs/issues/198/comments","statuses_url":"https://api.github.com/repos/pingcap/docs/statuses/a36fd099760e4fd586ed65de74c7af68a298ef30","head":{"label":"pingcap:json-functions-generated-column","ref":"json-functions-generated-column","sha":"a36fd099760e4fd586ed65de74c7af68a298ef30","user":{"login":"pingcap","id":11855343,"avatar_url":"https://avatars3.githubusercontent.com/u/11855343?v=4","gravatar_id":"","url":"https://api.github.com/users/pingcap","html_url":"https://github.com/pingcap","followers_url":"https://api.github.com/users/pingcap/followers","following_url":"https://api.github.com/users/pingcap/following{/other_user}","gists_url":"https://api.github.com/users/pingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/pingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pingcap/subscriptions","organizations_url":"https://api.github.com/users/pingcap/orgs","repos_url":"https://api.github.com/users/pingcap/repos","events_url":"https://api.github.com/users/pingcap/events{/privacy}","received_events_url":"https://api.github.com/users/pingcap/received_events","type":"Organization","site_admin":false},"repo":{"id":63995402,"name":"docs","full_name":"pingcap/docs","owner":{"login":"pingcap","id":11855343,"avatar_url":"https://avatars3.githubusercontent.com/u/11855343?v=4","gravatar_id":"","url":"https://api.github.com/users/pingcap","html_url":"https://github.com/pingcap","followers_url":"https://api.github.com/users/pingcap/followers","following_url":"https://api.github.com/users/pingcap/following{/other_user}","gists_url":"https://api.github.com/users/pingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/pingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pingcap/subscriptions","organizations_url":"https://api.github.com/users/pingcap/orgs","repos_url":"https://api.github.com/users/pingcap/repos","events_url":"https://api.github.com/users/pingcap/events{/privacy}","received_events_url":"https://api.github.com/users/pingcap/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/pingcap/docs","description":"TiDB/TiKV/PD documents.","fork":false,"url":"https://api.github.com/repos/pingcap/docs","forks_url":"https://api.github.com/repos/pingcap/docs/forks","keys_url":"https://api.github.com/repos/pingcap/docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/pingcap/docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/pingcap/docs/teams","hooks_url":"https://api.github.com/repos/pingcap/docs/hooks","issue_events_url":"https://api.github.com/repos/pingcap/docs/issues/events{/number}","events_url":"https://api.github.com/repos/pingcap/docs/events","assignees_url":"https://api.github.com/repos/pingcap/docs/assignees{/user}","branches_url":"https://api.github.com/repos/pingcap/docs/branches{/branch}","tags_url":"https://api.github.com/repos/pingcap/docs/tags","blobs_url":"https://api.github.com/repos/pingcap/docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/pingcap/docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/pingcap/docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/pingcap/docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/pingcap/docs/statuses/{sha}","languages_url":"https://api.github.com/repos/pingcap/docs/languages","stargazers_url":"https://api.github.com/repos/pingcap/docs/stargazers","contributors_url":"https://api.github.com/repos/pingcap/docs/contributors","subscribers_url":"https://api.github.com/repos/pingcap/docs/subscribers","subscription_url":"https://api.github.com/repos/pingcap/docs/subscription","commits_url":"https://api.github.com/repos/pingcap/docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/pingcap/docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/pingcap/docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/pingcap/docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/pingcap/docs/contents/{+path}","compare_url":"https://api.github.com/repos/pingcap/docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/pingcap/docs/merges","archive_url":"https://api.github.com/repos/pingcap/docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/pingcap/docs/downloads","issues_url":"https://api.github.com/repos/pingcap/docs/issues{/number}","pulls_url":"https://api.github.com/repos/pingcap/docs/pulls{/number}","milestones_url":"https://api.github.com/repos/pingcap/docs/milestones{/number}","notifications_url":"https://api.github.com/repos/pingcap/docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/pingcap/docs/labels{/name}","releases_url":"https://api.github.com/repos/pingcap/docs/releases{/id}","deployments_url":"https://api.github.com/repos/pingcap/docs/deployments","created_at":"2016-07-23T02:39:04Z","updated_at":"2017-09-15T12:24:35Z","pushed_at":"2017-09-19T03:49:41Z","git_url":"git://github.com/pingcap/docs.git","ssh_url":"git@github.com:pingcap/docs.git","clone_url":"https://github.com/pingcap/docs.git","svn_url":"https://github.com/pingcap/docs","homepage":null,"size":1521,"stargazers_count":63,"watchers_count":63,"language":"HTML","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":34,"mirror_url":null,"open_issues_count":19,"forks":34,"open_issues":19,"watchers":63,"default_branch":"master"}},"base":{"label":"pingcap:master","ref":"master","sha":"af693cbeb9f7972cd29f0885bcde6d01177cbcbf","user":{"login":"pingcap","id":11855343,"avatar_url":"https://avatars3.githubusercontent.com/u/11855343?v=4","gravatar_id":"","url":"https://api.github.com/users/pingcap","html_url":"https://github.com/pingcap","followers_url":"https://api.github.com/users/pingcap/followers","following_url":"https://api.github.com/users/pingcap/following{/other_user}","gists_url":"https://api.github.com/users/pingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/pingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pingcap/subscriptions","organizations_url":"https://api.github.com/users/pingcap/orgs","repos_url":"https://api.github.com/users/pingcap/repos","events_url":"https://api.github.com/users/pingcap/events{/privacy}","received_events_url":"https://api.github.com/users/pingcap/received_events","type":"Organization","site_admin":false},"repo":{"id":63995402,"name":"docs","full_name":"pingcap/docs","owner":{"login":"pingcap","id":11855343,"avatar_url":"https://avatars3.githubusercontent.com/u/11855343?v=4","gravatar_id":"","url":"https://api.github.com/users/pingcap","html_url":"https://github.com/pingcap","followers_url":"https://api.github.com/users/pingcap/followers","following_url":"https://api.github.com/users/pingcap/following{/other_user}","gists_url":"https://api.github.com/users/pingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/pingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pingcap/subscriptions","organizations_url":"https://api.github.com/users/pingcap/orgs","repos_url":"https://api.github.com/users/pingcap/repos","events_url":"https://api.github.com/users/pingcap/events{/privacy}","received_events_url":"https://api.github.com/users/pingcap/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/pingcap/docs","description":"TiDB/TiKV/PD documents.","fork":false,"url":"https://api.github.com/repos/pingcap/docs","forks_url":"https://api.github.com/repos/pingcap/docs/forks","keys_url":"https://api.github.com/repos/pingcap/docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/pingcap/docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/pingcap/docs/teams","hooks_url":"https://api.github.com/repos/pingcap/docs/hooks","issue_events_url":"https://api.github.com/repos/pingcap/docs/issues/events{/number}","events_url":"https://api.github.com/repos/pingcap/docs/events","assignees_url":"https://api.github.com/repos/pingcap/docs/assignees{/user}","branches_url":"https://api.github.com/repos/pingcap/docs/branches{/branch}","tags_url":"https://api.github.com/repos/pingcap/docs/tags","blobs_url":"https://api.github.com/repos/pingcap/docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/pingcap/docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/pingcap/docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/pingcap/docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/pingcap/docs/statuses/{sha}","languages_url":"https://api.github.com/repos/pingcap/docs/languages","stargazers_url":"https://api.github.com/repos/pingcap/docs/stargazers","contributors_url":"https://api.github.com/repos/pingcap/docs/contributors","subscribers_url":"https://api.github.com/repos/pingcap/docs/subscribers","subscription_url":"https://api.github.com/repos/pingcap/docs/subscription","commits_url":"https://api.github.com/repos/pingcap/docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/pingcap/docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/pingcap/docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/pingcap/docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/pingcap/docs/contents/{+path}","compare_url":"https://api.github.com/repos/pingcap/docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/pingcap/docs/merges","archive_url":"https://api.github.com/repos/pingcap/docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/pingcap/docs/downloads","issues_url":"https://api.github.com/repos/pingcap/docs/issues{/number}","pulls_url":"https://api.github.com/repos/pingcap/docs/pulls{/number}","milestones_url":"https://api.github.com/repos/pingcap/docs/milestones{/number}","notifications_url":"https://api.github.com/repos/pingcap/docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/pingcap/docs/labels{/name}","releases_url":"https://api.github.com/repos/pingcap/docs/releases{/id}","deployments_url":"https://api.github.com/repos/pingcap/docs/deployments","created_at":"2016-07-23T02:39:04Z","updated_at":"2017-09-15T12:24:35Z","pushed_at":"2017-09-19T03:49:41Z","git_url":"git://github.com/pingcap/docs.git","ssh_url":"git@github.com:pingcap/docs.git","clone_url":"https://github.com/pingcap/docs.git","svn_url":"https://github.com/pingcap/docs","homepage":null,"size":1521,"stargazers_count":63,"watchers_count":63,"language":"HTML","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":34,"mirror_url":null,"open_issues_count":19,"forks":34,"open_issues":19,"watchers":63,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/pingcap/docs/pulls/198"},"html":{"href":"https://github.com/pingcap/docs/pull/198"},"issue":{"href":"https://api.github.com/repos/pingcap/docs/issues/198"},"comments":{"href":"https://api.github.com/repos/pingcap/docs/issues/198/comments"},"review_comments":{"href":"https://api.github.com/repos/pingcap/docs/pulls/198/comments"},"review_comment":{"href":"https://api.github.com/repos/pingcap/docs/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/pingcap/docs/pulls/198/commits"},"statuses":{"href":"https://api.github.com/repos/pingcap/docs/statuses/a36fd099760e4fd586ed65de74c7af68a298ef30"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"andelf"}}