You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2021/05/03 04:07:00 UTC
[shardingsphere] branch master updated: support postgresql json &
jsonb operator parse (#10244)
This is an automated email from the ASF dual-hosted git repository.
zhangliang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new e75c7fb support postgresql json & jsonb operator parse (#10244)
e75c7fb is described below
commit e75c7fbbb8b3900794a0206cf3c9cb8c1600a123
Author: Zhengqiang Duan <st...@gmail.com>
AuthorDate: Mon May 3 12:06:35 2021 +0800
support postgresql json & jsonb operator parse (#10244)
* support postgresql json & jsonb operator parse
* add postgresql json & jsonb operator parse test case
---
.../src/main/antlr4/imports/postgresql/BaseRule.g4 | 22 ++-
.../src/main/antlr4/imports/postgresql/Symbol.g4 | 10 ++
.../src/main/resources/case/dml/select.xml | 153 +++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 14 ++
4 files changed, 197 insertions(+), 2 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
index 7d2477a..681d561 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
@@ -423,6 +423,7 @@ unreservedWord
| YEAR
| YES
| ZONE
+ | JSON
;
typeFuncNameKeyword
@@ -521,10 +522,10 @@ aExpr
| aExpr SLASH_ aExpr
| aExpr MOD_ aExpr
| aExpr CARET_ aExpr
- | aExpr comparisonOperator aExpr
| aExpr qualOp aExpr
| qualOp aExpr
| aExpr qualOp
+ | aExpr comparisonOperator aExpr
| NOT aExpr
| aExpr LIKE aExpr
| aExpr LIKE aExpr ESCAPE aExpr
@@ -656,7 +657,7 @@ columnref
;
qualOp
- : mathOperator
+ : jsonOperator
| TILDE_TILDE_
| NOT_TILDE_TILDE_
| OPERATOR LP_ anyOperator RP_
@@ -737,6 +738,23 @@ mathOperator
| NEQ_
;
+jsonOperator
+ : JSON_EXTRACT_ # jsonExtract
+ | JSON_EXTRACT_TEXT_ # jsonExtractText
+ | JSON_PATH_EXTRACT_ # jsonPathExtract
+ | JSON_PATH_EXTRACT_TEXT_ # jsonPathExtractText
+ | JSONB_CONTAIN_RIGHT_ # jsonbContainRight
+ | JSONB_CONTAIN_LEFT_ # jsonbContainLeft
+ | QUESTION_ # jsonbContainTopKey
+ | QUESTION_ VERTICAL_BAR_ # jsonbContainAnyTopKey
+ | JSONB_CONTAIN_ALL_TOP_KEY_ # jsonbContainAllTopKey
+ | OR_ # jsonbConcat
+ | MINUS_ # jsonbDelete
+ | JSONB_PATH_DELETE_ # jsonbPathDelete
+ | JSONB_PATH_CONTAIN_ANY_VALUE_ # jsonbPathContainAnyValue
+ | JSONB_PATH_PREDICATE_CHECK_ # jsonbPathPredicateCheck
+ ;
+
qualAllOp
: allOp
| OPERATOR LP_ anyOperator RP_
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/Symbol.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/Symbol.g4
index f1b12dc..7935e13 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/Symbol.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/Symbol.g4
@@ -61,3 +61,13 @@ SEMI_: ';';
TILDE_TILDE_: '~~';
NOT_TILDE_TILDE_: '!~~';
TYPE_CAST_: '::';
+JSON_EXTRACT_: '->';
+JSON_EXTRACT_TEXT_: '->>';
+JSON_PATH_EXTRACT_: '#>';
+JSON_PATH_EXTRACT_TEXT_: '#>>';
+JSONB_CONTAIN_RIGHT_: '@>';
+JSONB_CONTAIN_LEFT_: '<@';
+JSONB_CONTAIN_ALL_TOP_KEY_: '?&';
+JSONB_PATH_DELETE_: '#-';
+JSONB_PATH_CONTAIN_ANY_VALUE_: '@?';
+JSONB_PATH_PREDICATE_CHECK_: '@@';
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
index d426920..6c4e969 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
@@ -2771,5 +2771,158 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_json_extract">
+ <from>
+ <simple-table name="tb_content_json" start-index="70" stop-index="84" />
+ </from>
+ <projections start-index="7" stop-index="63">
+ <expression-projection text="content_json::json->'title'" start-index="7" stop-index="33" />
+ <expression-projection text="content_json::json->'nation'" start-index="36" stop-index="63" />
+ </projections>
+ <where start-index="86" stop-index="105">
+ <expr>
+ <binary-operation-expression start-index="92" stop-index="105">
+ <left>
+ <column name="content_id" start-index="92" stop-index="101" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="105" stop-index="105" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_with_json_extract_text">
+ <from>
+ <simple-table name="tb_content_json" start-index="14" stop-index="28" />
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="30" stop-index="72">
+ <expr>
+ <binary-operation-expression start-index="36" stop-index="72">
+ <left>
+ <common-expression literal-text="content_json::json->>'nation'" start-index="36" stop-index="64" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="CHINA" start-index="66" stop-index="72" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_with_json_path_extract">
+ <from>
+ <simple-table name="tb_content_json" start-index="46" stop-index="60" />
+ </from>
+ <projections start-index="7" stop-index="39">
+ <expression-projection text="content_json::json#>'{keyword,1}'" start-index="7" stop-index="39" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_json_path_extract_text">
+ <from>
+ <simple-table name="tb_content_json" start-index="47" stop-index="61" />
+ </from>
+ <projections start-index="7" stop-index="40">
+ <expression-projection text="content_json::json#>>'{keyword,1}'" start-index="7" stop-index="40" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_contain_right">
+ <from>
+ <simple-table name="tb_content_json" start-index="58" stop-index="72" />
+ </from>
+ <projections start-index="7" stop-index="51">
+ <expression-projection text="content_json::jsonb@>'{&title&:&abc&}'::jsonb" start-index="7" stop-index="51" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_contain_left">
+ <from>
+ <simple-table name="tb_content_json" start-index="58" stop-index="72" />
+ </from>
+ <projections start-index="7" stop-index="51">
+ <expression-projection text="'{&title&:&abc&}'::jsonb<@content_json::jsonb" start-index="7" stop-index="51" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_contain_top_key">
+ <from>
+ <simple-table name="tb_content_json" start-index="40" stop-index="54" />
+ </from>
+ <projections start-index="7" stop-index="33">
+ <expression-projection text="content_json::jsonb?'title'" start-index="7" stop-index="33" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_contain_any_top_key">
+ <from>
+ <simple-table name="tb_content_json" start-index="57" stop-index="71" />
+ </from>
+ <projections start-index="7" stop-index="50">
+ <expression-projection text="content_json::jsonb?|array['title','nation']" start-index="7" stop-index="50" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_contain_all_top_key">
+ <from>
+ <simple-table name="tb_content_json" start-index="57" stop-index="71" />
+ </from>
+ <projections start-index="7" stop-index="50">
+ <expression-projection text="content_json::jsonb?&array['title','nation']" start-index="7" stop-index="50" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_concat">
+ <from>
+ <simple-table name="tb_content_json" start-index="56" stop-index="70" />
+ </from>
+ <projections start-index="7" stop-index="49">
+ <expression-projection text="content_json::jsonb||'{"price":999}'::jsonb" start-index="7" stop-index="49" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_delete">
+ <from>
+ <simple-table name="tb_content_json" start-index="40" stop-index="54" />
+ </from>
+ <projections start-index="7" stop-index="33">
+ <expression-projection text="content_json::jsonb-'title'" start-index="7" stop-index="33" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_path_delete">
+ <from>
+ <simple-table name="tb_content_json" start-index="43" stop-index="57" />
+ </from>
+ <projections start-index="7" stop-index="36">
+ <expression-projection text="content_json::jsonb#-'{title}'" start-index="7" stop-index="36" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_path_contain_any_value">
+ <from>
+ <simple-table name="tb_content_json" start-index="59" stop-index="73" />
+ </from>
+ <projections start-index="7" stop-index="52">
+ <expression-projection text="content_json::jsonb@?'$.keyword[*]?(@=="ss")'" start-index="7" stop-index="52" />
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_jsonb_path_predicate_check">
+ <from>
+ <simple-table name="tb_content_json" start-index="54" stop-index="68" />
+ </from>
+ <projections start-index="7" stop-index="47">
+ <expression-projection text="content_json::jsonb@@'$.keyword[*]=="ss"'" start-index="7" stop-index="47" />
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
index e437bf7..da21c14 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
@@ -65,4 +65,18 @@
<sql-case id="select_with_match_against" value="SELECT * FROM t_order_item WHERE MATCH(t_order_item.description) AGAINST (? IN NATURAL LANGUAGE MODE) AND user_id = ?" db-types="MySQL" />
<sql-case id="select_with_json_separator" value="select content_json->>'$.nation' as nation,content_json->>'$.title' as title from tb_content_json b where b.content_id=1" db-types="MySQL" />
<sql-case id="select_with_convert_function" value="SELECT CONVERT(SUBSTRING(content, 5) , SIGNED) AS signed_content FROM t_order WHERE order_id = 1" db-types="MySQL" />
+ <sql-case id="select_with_json_extract" value="SELECT content_json::json->'title', content_json::json->'nation' FROM tb_content_json WHERE content_id = 1" db-types="PostgreSQL" />
+ <sql-case id="select_with_json_extract_text" value="SELECT * FROM tb_content_json WHERE content_json::json->>'nation'='CHINA'" db-types="PostgreSQL" />
+ <sql-case id="select_with_json_path_extract" value="SELECT content_json::json#>'{keyword,1}' FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_json_path_extract_text" value="SELECT content_json::json#>>'{keyword,1}' FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_contain_right" value="SELECT content_json::jsonb@>'{&title&:&abc&}'::jsonb FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_contain_left" value="SELECT '{&title&:&abc&}'::jsonb<@content_json::jsonb FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_contain_top_key" value="SELECT content_json::jsonb?'title' FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_contain_any_top_key" value="SELECT content_json::jsonb?|array['title','nation'] FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_contain_all_top_key" value="SELECT content_json::jsonb?&array['title','nation'] FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_concat" value="SELECT content_json::jsonb||'{"price":999}'::jsonb FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_delete" value="SELECT content_json::jsonb-'title' FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_path_delete" value="SELECT content_json::jsonb#-'{title}' FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_path_contain_any_value" value="SELECT content_json::jsonb @?'$.keyword[*]?(@=="ss")' FROM tb_content_json" db-types="PostgreSQL" />
+ <sql-case id="select_with_jsonb_path_predicate_check" value="SELECT content_json::jsonb@@'$.keyword[*]=="ss"' FROM tb_content_json" db-types="PostgreSQL" />
</sql-cases>