This is an automated email from the ASF dual-hosted git repository.
vjasani pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/master by this push:
new 01fbe01c3f PHOENIX-7072 : Implement json_modify function on the json
object as Atomic Upserts (#1893)
01fbe01c3f is described below
commit 01fbe01c3fefae972bdf33886cf282f56b4b0cae
Author: RanganathG <[email protected]>
AuthorDate: Thu Jun 13 08:28:22 2024 +0530
PHOENIX-7072 : Implement json_modify function on the json object as Atomic
Upserts (#1893)
---
.../apache/phoenix/compile/ExpressionCompiler.java | 3 +-
.../apache/phoenix/expression/ExpressionType.java | 3 +-
.../expression/function/JsonModifyFunction.java | 120 +++++++++++++++++++++
.../apache/phoenix/parse/JsonModifyParseNode.java | 48 +++++++++
.../phoenix/end2end/json/JsonFunctionsIT.java | 110 +++++++++++++++++++
5 files changed, 282 insertions(+), 2 deletions(-)
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
index cd5dfef073..cff3d964f5 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
@@ -89,6 +89,7 @@ import org.apache.phoenix.parse.FunctionParseNode;
import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunctionInfo;
import org.apache.phoenix.parse.InListParseNode;
import org.apache.phoenix.parse.IsNullParseNode;
+import org.apache.phoenix.parse.JsonModifyParseNode;
import org.apache.phoenix.parse.JsonQueryParseNode;
import org.apache.phoenix.parse.LikeParseNode;
import org.apache.phoenix.parse.LikeParseNode.LikeType;
@@ -296,7 +297,7 @@ public class ExpressionCompiler extends
UnsupportedAllParseNodeVisitor<Expressio
@Override
public boolean visitEnter(FunctionParseNode node) throws SQLException {
- if (node instanceof JsonQueryParseNode) {
+ if (node instanceof JsonQueryParseNode || node instanceof
JsonModifyParseNode) {
this.isJsonFragment = true;
}
// TODO: Oracle supports nested aggregate function while other DBs
don't. Should we?
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
index 3ee14593c5..e0abce8900 100644
---
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/ExpressionType.java
@@ -195,7 +195,8 @@ public enum ExpressionType {
PhoenixRowTimestampFunction(PhoenixRowTimestampFunction.class),
JsonValueFunction(JsonValueFunction.class),
JsonQueryFunction(JsonQueryFunction.class),
- JsonExistsFunction(JsonExistsFunction.class)
+ JsonExistsFunction(JsonExistsFunction.class),
+ JsonModifyFunction(JsonModifyFunction.class)
;
ExpressionType(Class<? extends Expression> clazz) {
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java
new file mode 100644
index 0000000000..a66f67be44
--- /dev/null
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java
@@ -0,0 +1,120 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.expression.function;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode;
+import org.apache.phoenix.parse.JsonModifyParseNode;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PJson;
+import org.apache.phoenix.schema.types.PVarchar;
+import org.apache.phoenix.thirdparty.com.google.common.base.Preconditions;
+import org.apache.phoenix.util.json.JsonDataFormat;
+import org.apache.phoenix.util.json.JsonDataFormatFactory;
+
+import java.nio.ByteBuffer;
+import java.util.List;
+
+/**
+ * Built-in function for JSON_MODIFY
JSON_MODIFY(<column_with_json/json_string>, <path> [returning
+ * <type>], newValue) Updates the value of a property in a JSON string and
returns the updated JSON
+ * string.
+ */
[email protected](name = JsonModifyFunction.NAME,
+ nodeClass = JsonModifyParseNode.class,
+ args = { @FunctionParseNode.Argument(allowedTypes = { PJson.class,
PVarchar.class }),
+ @FunctionParseNode.Argument(allowedTypes = { PVarchar.class }),
+ @FunctionParseNode.Argument(allowedTypes = { PVarchar.class })
})
+public class JsonModifyFunction extends ScalarFunction {
+
+ public static final String NAME = "JSON_MODIFY";
+ private final JsonDataFormat
+ jsonDataFormat =
+
JsonDataFormatFactory.getJsonDataFormat(JsonDataFormatFactory.DataFormat.BSON);
+
+ // This is called from ExpressionType newInstance
+ public JsonModifyFunction() {
+
+ }
+
+ public JsonModifyFunction(List<Expression> children) {
+ super(children);
+ Preconditions.checkNotNull(getJSONPathExpr());
+ }
+
+ @Override
+ public String getName() {
+ return NAME;
+ }
+
+ @Override
+ public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
+ if (!getColValExpr().evaluate(tuple, ptr)) {
+ return false;
+ }
+ if (ptr == null || ptr.getLength() == 0) {
+ return false;
+ }
+
+ // Column name or JSON string
+ Object top = PJson.INSTANCE.toObject(ptr,
getColValExpr().getSortOrder());
+
+ if (!getJSONPathExpr().evaluate(tuple, ptr)) {
+ return false;
+ }
+
+ if (ptr.getLength() == 0) {
+ return false;
+ }
+
+ String
+ jsonPathExprStr =
+ (String) PVarchar.INSTANCE.toObject(ptr,
getJSONPathExpr().getSortOrder());
+ if (jsonPathExprStr == null) {
+ return false;
+ }
+
+ if (!getNewValueExpr().evaluate(tuple, ptr)) {
+ return false;
+ }
+
+ String newVal = (String) PVarchar.INSTANCE.toObject(ptr,
getNewValueExpr().getSortOrder());
+ ByteBuffer buffer = jsonDataFormat.updateValue(top, jsonPathExprStr,
newVal);
+ ptr.set(buffer.array(), buffer.arrayOffset(), buffer.limit());
+ return true;
+ }
+
+ private Expression getNewValueExpr() {
+ return getChildren().get(2);
+ }
+
+ private Expression getColValExpr() {
+ return getChildren().get(0);
+ }
+
+ private Expression getJSONPathExpr() {
+ return getChildren().get(1);
+ }
+
+ @Override
+ public PDataType getDataType() {
+ return PJson.INSTANCE;
+ }
+}
\ No newline at end of file
diff --git
a/phoenix-core-client/src/main/java/org/apache/phoenix/parse/JsonModifyParseNode.java
b/phoenix-core-client/src/main/java/org/apache/phoenix/parse/JsonModifyParseNode.java
new file mode 100644
index 0000000000..059da3209e
--- /dev/null
+++
b/phoenix-core-client/src/main/java/org/apache/phoenix/parse/JsonModifyParseNode.java
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.parse;
+
+import org.apache.phoenix.compile.StatementContext;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.function.FunctionExpression;
+import org.apache.phoenix.expression.function.JsonModifyFunction;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PJson;
+
+import java.sql.SQLException;
+import java.util.List;
+
+/**
+ * ParseNode for JSON_MODIFY function.
+ */
+public class JsonModifyParseNode extends FunctionParseNode {
+
+ public JsonModifyParseNode(String name, List<ParseNode> children,
BuiltInFunctionInfo info) {
+ super(name, children, info);
+ }
+
+ @Override
+ public FunctionExpression create(List<Expression> children,
StatementContext context)
+ throws SQLException {
+ PDataType dataType = children.get(0).getDataType();
+ if (!dataType.isCoercibleTo(PJson.INSTANCE)) {
+ throw new SQLException(dataType + " type is unsupported for
JSON_MODIFY().");
+ }
+ return new JsonModifyFunction(children);
+ }
+}
\ No newline at end of file
diff --git
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
index e7de5dbc7a..04c1070fe9 100644
---
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
+++
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
@@ -122,6 +122,116 @@ public class JsonFunctionsIT extends
ParallelStatsDisabledIT {
assertEquals(mapper.readTree(read.toString()),
mapper.readTree(result));
}
+ @Test
+ public void testAtomicUpsertJsonModifyWithAutoCommit() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String ddl = "create table " + tableName + " (pk integer primary
key, col integer, jsoncol json) COLUMN_ENCODED_BYTES=0";
+ conn.createStatement().execute(ddl);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES (?,?,?)");
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 2);
+ stmt.setString(3, basicJson);
+ stmt.execute();
+
+ String upsert ="UPSERT INTO " + tableName + " (pk, col)
VALUES(1,2" +
+ ") ON DUPLICATE KEY UPDATE jsoncol = JSON_MODIFY(jsoncol,
'$.info.address.town', '\"Manchester\"')";
+ conn.createStatement().execute(upsert);
+
+ String query="SELECT JSON_VALUE(jsoncol, '$.info.address.town')
FROM " + tableName;
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Manchester", rs.getString(1));
+
+ conn.createStatement().execute("UPSERT INTO " + tableName + " (pk,
col) VALUES(1,2" +
+ ") ON DUPLICATE KEY UPDATE jsoncol = JSON_MODIFY(jsoncol,
'$.info.tags[1]', '\"alto1\"')");
+
+ // Querying now should give the old value since we haven't
committed.
+ query="SELECT JSON_VALUE(jsoncol, '$.info.tags[1]') FROM " +
tableName;
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("alto1", rs.getString(1));
+
+ conn.createStatement().execute("UPSERT INTO " + tableName + " (pk,
col) VALUES(1,2" +
+ ") ON DUPLICATE KEY UPDATE jsoncol = JSON_MODIFY(jsoncol,
'$.info.tags', '[\"Sport\", \"alto1\", \"Books\"]')");
+ String queryTemplate ="SELECT JSON_VALUE(jsoncol, '$.type'),
JSON_VALUE(jsoncol, '$.info.address.town'), " +
+ "JSON_VALUE(jsoncol, '$.info.tags[1]'),
JSON_QUERY(jsoncol, '$.info.tags'), JSON_QUERY(jsoncol, '$.info') " +
+ " FROM " + tableName +
+ " WHERE JSON_VALUE(jsoncol, '$.name') = '%s'";
+ query = String.format(queryTemplate, "AndersenFamily");
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Basic", rs.getString(1));
+ assertEquals("Manchester", rs.getString(2));
+ assertEquals("alto1", rs.getString(3));
+ assertEquals("[\"Sport\", \"alto1\", \"Books\"]", rs.getString(4));
+ assertEquals("{\"type\": 1, \"address\": {\"town\":
\"Manchester\", \"county\": \"Avon\", \"country\": \"England\", \"exists\":
true}, \"tags\": [\"Sport\", \"alto1\", \"Books\"]}", rs.getString(5));
+
+ // Now check for empty match
+ query = String.format(queryTemplate, "Windsors");
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+ }
+ }
+
+ @Test
+ public void testAtomicUpsertJsonModifyWithoutAutoCommit() throws Exception
{
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ String ddl = "create table " + tableName + " (pk integer primary
key, col integer, jsoncol json) COLUMN_ENCODED_BYTES=0";
+ conn.createStatement().execute(ddl);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES (?,?,?)");
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 2);
+ stmt.setString(3, basicJson);
+ stmt.execute();
+ conn.commit();
+
+ String upsert ="UPSERT INTO " + tableName + " (pk, col)
VALUES(1,2" +
+ ") ON DUPLICATE KEY UPDATE jsoncol = JSON_MODIFY(jsoncol,
'$.info.address.town', '\"Manchester\"')";
+ conn.createStatement().execute(upsert);
+
+ // Querying now should give the old value since we haven't
committed.
+ String query="SELECT JSON_VALUE(jsoncol, '$.info.address.town')
FROM " + tableName;
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Bristol", rs.getString(1));
+
+ conn.createStatement().execute("UPSERT INTO " + tableName + " (pk,
col) VALUES(1,2" +
+ ") ON DUPLICATE KEY UPDATE jsoncol = JSON_MODIFY(jsoncol,
'$.info.tags[1]', '\"alto1\"')");
+
+ // Querying now should give the old value since we haven't
committed.
+ query="SELECT JSON_VALUE(jsoncol, '$.info.tags[1]') FROM " +
tableName;
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Water polo", rs.getString(1));
+
+ conn.createStatement().execute("UPSERT INTO " + tableName + " (pk,
col) VALUES(1,2" +
+ ") ON DUPLICATE KEY UPDATE jsoncol = JSON_MODIFY(jsoncol,
'$.info.tags', '[\"Sport\", \"alto1\", \"Books\"]')");
+ conn.commit();
+ String queryTemplate ="SELECT JSON_VALUE(jsoncol, '$.type'),
JSON_VALUE(jsoncol, '$.info.address.town'), " +
+ "JSON_VALUE(jsoncol, '$.info.tags[1]'),
JSON_QUERY(jsoncol, '$.info.tags'), JSON_QUERY(jsoncol, '$.info') " +
+ " FROM " + tableName +
+ " WHERE JSON_VALUE(jsoncol, '$.name') = '%s'";
+ query = String.format(queryTemplate, "AndersenFamily");
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Basic", rs.getString(1));
+ assertEquals("Manchester", rs.getString(2));
+ assertEquals("alto1", rs.getString(3));
+ assertEquals("[\"Sport\", \"alto1\", \"Books\"]", rs.getString(4));
+ assertEquals("{\"type\": 1, \"address\": {\"town\":
\"Manchester\", \"county\": \"Avon\", \"country\": \"England\", \"exists\":
true}, \"tags\": [\"Sport\", \"alto1\", \"Books\"]}", rs.getString(5));
+
+ // Now check for empty match
+ query = String.format(queryTemplate, "Windsors");
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+ }
+ }
+
@Test
public void testSimpleJsonDatatypes() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
