[
https://issues.apache.org/jira/browse/PHOENIX-7044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17777091#comment-17777091
]
ASF GitHub Bot commented on PHOENIX-7044:
-----------------------------------------
ranganathg commented on code in PR #1708:
URL: https://github.com/apache/phoenix/pull/1708#discussion_r1365044946
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java:
##########
@@ -633,4 +637,34 @@ public void testJsonQuery() throws Exception {
compareJson(rs.getString(5), JsonDatatypes,
"$.datatypes.mixedArray");
}
}
+
+ @Test
+ public void testExpressionIndex() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String
+ ddl =
+ "create table if not exists " + tableName +
+ " (pk integer primary key, col integer,
jsoncol.jsoncol json)";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES
(1,2, '" + JsonDoc1 + "')");
+ conn.createStatement().execute(
+ "CREATE INDEX " + indexName + " ON " + tableName
+ + " (JSON_VALUE(JSONCOL,'$.type'),
JSON_VALUE(JSONCOL,'$.info.address.town')) include (col)");
Review Comment:
Tested it this way:
`0: jdbc:phoenix:localhost> create index indexmytable1 on mytable1 (colA)
include (PHOENIX_ROW_TIMESTAMP());
Error: ERROR 601 (42P00): Syntax error. Encountered "(" at line 1, column
77. (state=42P00,code=601)`
`0: jdbc:phoenix:localhost> create index idx_jsontest6 on jsontest6
(JSON_VALUE(JSONCOL,'$.type'), JSON_VALUE(JSONCOL,'$.info.address.town'))
include (JSON_VALUE(JSONCOL, '$.info.address.county'));
Error: ERROR 601 (42P00): Syntax error. Encountered "(" at line 1, column
134. (state=42P00,code=601)`
> Support Index on a generated column that extracts a scalar value from the
> JSON column
> -------------------------------------------------------------------------------------
>
> Key: PHOENIX-7044
> URL: https://issues.apache.org/jira/browse/PHOENIX-7044
> Project: Phoenix
> Issue Type: Sub-task
> Components: phoenix
> Reporter: Ranganath Govardhanagiri
> Assignee: Ranganath Govardhanagiri
> Priority: Major
>
> As per the Design doc attached we can implement the below proposal:
> Using index expressions: Phoenix lets index’s PK to be defined as certain
> expressions. Aggregate (sum()), stateless(a constant value) or
> non-deterministic(rand()) is not allowed. But expressions like (mycol/2) is
> allowed. The Json function could be used as expression with some code
> changes.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
