You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by ictwanglei <gi...@git.apache.org> on 2015/06/28 14:28:23 UTC
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
GitHub user ictwanglei opened a pull request:
https://github.com/apache/phoenix/pull/93
PHOENIX-1661 Implement built-in functions for JSON
1. Add some functional files: JsonArrayElementsFunction.java JsonArrayLengthFunction.java JsonEachFunction.java JsonObjectKeysFunction.java JsonPopulateRecordFunction.java JsonPopulateRecordSetFunction.java. ArrayToJsonFunction.java
2. Add a unit test file: JsonFunctionTest.java
3. Modify file PhoenixJson.java to which I added some assistant functions.
@JamesRTaylor @twdsilva @AakashPradeep please review the changelist.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/ictwanglei/phoenix master
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/phoenix/pull/93.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #93
----
commit 26383070e5980697a0de1193c069692a8107b7c2
Author: ictwanglei <59...@qq.com>
Date: 2015-03-21T15:32:40Z
Create ArrayToJsonFunction.java
add a function to support "ArrayToJson" function as a build-in function for JSON
commit 2c63494f99267fbcd0b7e52be76a1dc0154e306a
Author: ictwanglei <59...@qq.com>
Date: 2015-03-21T15:34:57Z
Create ArrayToJsonFunctionTest.java
a simple test for testing ArrayToJsonFunction
commit 241f4c3186a9f70f057e9196f962a0ed1b2f593b
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:26:56Z
Delete ArrayToJsonFunctionTest.java
commit f66ba90f92a667405759fceaf1ee74c27ef1d1c8
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:35:37Z
Create JsonFunctionTest.java
commit 7f2a6cf0c3d56b563a528036844dd5096f98ab6a
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:53:44Z
PHOENIX-1661
Implement built-in functions for JSON
commit db5c9e1cfbb1f3145a992efef13a8dca7cee0ec7
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:54:53Z
Delete ArrayToJsonFunction.java
commit a02b954453ff67220c08801e0eafd1c23bdd4b9c
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:55:10Z
Rename ArrayToJsonFunction1.java to ArrayToJsonFunction.java
commit eb76667b0c61ea646a63c1b46b41f418c6b43239
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:55:33Z
Update ArrayToJsonFunction.java
commit 6546bc5fb0620b954e31e54e0c71671c54a8f9dc
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:56:50Z
PHOENIX-1661 Implement built-in functions for JSON
commit 6bb8b116db3dd46434f2020a0ca2f46545b0ac05
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:58:22Z
PHOENIX-1661 Implement built-in functions for JSON
commit 3782d70ce1c0fedbcea48e3df968564311c06a2a
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T11:59:25Z
PHOENIX-1661 Implement built-in functions for JSON
commit 5ff61520d2aa351ea4dab18ec131c74f34b1b463
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T12:00:08Z
PHOENIX-1661 Implement built-in functions for JSON
commit d21c22b3697b64b920cc22a33e32d76b2b8eb074
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T12:01:14Z
PHOENIX-1661 Implement built-in functions for JSON
commit 541697d72d27a809cb8172efd58d83960b79f362
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T12:01:50Z
PHOENIX-1661 Implement built-in functions for JSON
commit 7e0dbaf950b4292bf673bb64367e57085ada25f8
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T12:03:16Z
PHOENIX-1661 Implement built-in functions for JSON
commit 331e09b9df48611421830b3c52afc80fd75099d5
Author: ictwanglei <59...@qq.com>
Date: 2015-06-28T12:09:54Z
PHOENIX-1661 Implement built-in functions for JSON
PHOENIX-1661 Implement built-in functions for JSON
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394937
--- Diff: phoenix-core/src/test/java/org/apache/phoenix/expression/function/JsonFunctionTest.java ---
@@ -0,0 +1,284 @@
+/*
+ * 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.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.types.*;
+import org.junit.Test;
+
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Date;
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
+import java.util.List;
+
+import static org.junit.Assert.assertEquals;
+
+
+/**
+ * Unit tests for JSON build-in function.
+ * Testing function includes below:
+ * {@link ArrayToJsonFunction}
+ * {@link JsonArrayElementsFunction}
+ * {@link JsonPopulateRecordFunction}
+ * {@link JsonPopulateRecordSetFunction}
+ * {@link JsonArrayLengthFunction}
+ * {@link JsonObjectKeysFunction}
+ * {@link ToJsonFunction}
+ * {@link JsonEachFunction}
+ *
+ */
+public class JsonFunctionTest {
+ public static final String TEST_JSON_STR =
+ "{\"f2\":{\"f3\":\"value\"},\"f4\":{\"f5\":99,\"f6\":[1,true,\"foo\"]},\"f7\":true}";
+
+ private static PhoenixJson testArrayToJson (Object[] array,PDataType datatype,PArrayDataType arraydatatype) throws Exception {
+ PhoenixArray pa = PArrayDataType.instantiatePhoenixArray( datatype,array);
+ LiteralExpression arrayExpr = LiteralExpression.newConstant(pa,arraydatatype );
+ List<Expression> children = Arrays.<Expression>asList(arrayExpr);
+ ArrayToJsonFunction e = new ArrayToJsonFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixJson result = (PhoenixJson)e.getDataType().toObject(ptr);
+ return result;
+ }
+
+ @Test
+ public void testNumberArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{1,12,32,432};
+ PhoenixJson result = testArrayToJson(testarray, PInteger.INSTANCE, PIntegerArray.INSTANCE);
+ String expected = "[1,12,32,432]";
+ assertEquals(result.serializeToString(), expected);
+ Object[] testarray2 = new Object[]{1.12,12.34,32.45,432.78};
+ PhoenixJson result2 = testArrayToJson(testarray2, PDouble.INSTANCE, PDoubleArray.INSTANCE);
+ String expected2 = "[1.12,12.34,32.45,432.78]";
+ assertEquals(result2.serializeToString(), expected2);
+ }
+ @Test
+ public void testBooleanArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{false,true};
+ PhoenixJson result = testArrayToJson(testarray, PBoolean.INSTANCE, PBooleanArray.INSTANCE);
+ String expected = "[false,true]";
+ assertEquals(result.toString(), expected);
+ }
+
+ @Test
+ public void testStringArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{"abc123","12.3","string","汉字"};
+ PhoenixJson result = testArrayToJson(testarray, PVarchar.INSTANCE, PVarcharArray.INSTANCE);
+ String expected ="[\"abc123\",\"12.3\",\"string\",\"汉字\"]";
+ assertEquals(result.serializeToString(), expected);
+ }
+ @Test
+ public void testDateArrayToJson() throws Exception {
+ SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss.SSS");
+ Date date1= myFormatter.parse("1990-12-01 11:01:45.0");
+ Date date2 = myFormatter.parse("1989-03-12 13:01:45.0");
+ Date date3 = myFormatter.parse("1974-06-06 12:01:45.0");
+ Object[] testarray = new Object[]{date1,date2,date3};
+ PhoenixJson result = testArrayToJson(testarray, PDate.INSTANCE,PDateArray.INSTANCE);
+ String expected ="[\"1990-12-01\",\"1989-03-12\",\"1974-06-06\"]";
+ assertEquals(result.serializeToString(), expected);
+
+ Timestamp ts1 = Timestamp.valueOf("1990-12-01 11:01:45.123");
+ Timestamp ts2 = Timestamp.valueOf("1989-03-12 01:01:01.0");
+ Timestamp ts3 = Timestamp.valueOf("1989-03-12 23:59:59.1");
+ testarray = new Object[]{ts1,ts2,ts3};
+ result = testArrayToJson(testarray, PTimestamp.INSTANCE,PTimestampArray.INSTANCE);
+ expected ="[\"1990-12-01 11:01:45.123\",\"1989-03-12 01:01:01.0\",\"1989-03-12 23:59:59.1\"]";
+ assertEquals(result.serializeToString(), expected);
+
+ Time t1 = new Time(date1.getTime());
+ Time t2 = new Time(date2.getTime());
+ Time t3 = new Time(date3.getTime());
+ testarray = new Object[]{t1,t2,t3};
+ result = testArrayToJson(testarray, PTime.INSTANCE,PTimeArray.INSTANCE);
+ expected ="[\"11:01:45\",\"13:01:45\",\"12:01:45\"]";
+ assertEquals(result.serializeToString(), expected);
+
+
+ }
+
+
+ private static String[] jsonArrayElements (String json) throws Exception {
--- End diff --
Add a test that executes this function as a query.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35388362
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/JsonArrayLengthFunctionIT.java ---
@@ -0,0 +1,114 @@
+/**
+ *
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+import org.apache.phoenix.expression.function.JsonArrayLengthFunction;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+/**
+ * End to end test for {@link JsonArrayLengthFunction}.
+ *
+ */
+public class JsonArrayLengthFunctionIT extends BaseHBaseManagedTimeIT {
+
+ @Test
+ public void testJsonArrayLengthWithWhereClause() throws Exception {
+ Connection conn = getConnection();
+ String json = "[1,2,3]";
+ String pk = "valueOne";
+ try {
+ populateJsonTable(conn, json, pk);
+
+ String selectQuery = "SELECT col1 FROM testJson WHERE json_array_length(col1) = 3";
+ PreparedStatement stmt = conn.prepareStatement(selectQuery);
+ ResultSet rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("Json data read from DB is not as expected.", json,
+ rs.getString(1));
+ assertFalse(rs.next());
+
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJsonArrayLengthWhenFirstArgumentIsJsonString()
+ throws Exception {
+ Connection conn = getConnection();
+ String json = "[1,2,true,[\"string\",3]]";
+ String pk = "valueOne";
+ try {
+ populateJsonTable(conn, json, pk);
+
+ String selectQuery = "SELECT json_array_length(col1) FROM testJson WHERE pk = 'valueOne'";
+ PreparedStatement stmt = conn.prepareStatement(selectQuery);
+ ResultSet rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("Json data read from DB is not as expected.",
+ 4, rs.getInt(1));
+
+ assertFalse(rs.next());
+
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJsonArrayElementsWithWhereClause() throws Exception {
--- End diff --
Can you add a test that verifies json_array_elements returns an array correctly ?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35392706
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -231,4 +236,197 @@ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
}
return new PhoenixJson(node, node.toString());
}
+
+ /**
+ * If the current {@link PhoenixJson} is a JsonArray,then it returns the length of the JsonArray.
+ * <p>For example:[1,2,3] ,it will return 3
+ * <p>Its required for json_array_length().
+ * @throws SQLException
+ */
+ public int getJsonArrayLength() throws SQLException {
+ if(this.rootNode.isArray()){
+ return this.rootNode.size();
+ }else{
+ throw new SQLException("The JsonNode should be an Array");
+ }
+ }
+
+ /**
+ * If the current {@link PhoenixJson} is a JsonArray,then it returns the set of array elements.
+ * <p>For example:[1,false,[2,"string"]]
+ * it will return (new Object[]{"1","false","[2,\"string\"]"})
+ * <p>Its required for json_array_elements().
+ * @return {@link String []} as the set of JSON elements
+ * @throws SQLException
+ */
+ public Object[] getJsonArrayElements() throws SQLException {
+ if(this.rootNode.isArray()) {
+ List<String> elementlist = new ArrayList();
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while (elements.hasNext()) {
+ JsonNode e = elements.next();
+ elementlist.add(e.toString());
+ }
+ return elementlist.toArray();
+ }else{
+ throw new SQLException("The JsonNode should be an Array");
--- End diff --
Add a new SQLExceptionCode and use SQLExceptionInfo.Builder
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394623
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayToJsonFunction.java ---
@@ -0,0 +1,105 @@
+/*
+ * 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.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode;
+import org.apache.phoenix.schema.IllegalDataException;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.*;
+import org.apache.phoenix.util.ByteUtil;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.List;
+
+
+@FunctionParseNode.BuiltInFunction(name=ArrayToJsonFunction.NAME, args={
+ @FunctionParseNode.Argument(allowedTypes={PVarchar.class})} )
+public class ArrayToJsonFunction extends ScalarFunction {
--- End diff --
Please add tests for this function
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by AakashPradeep <gi...@git.apache.org>.
Github user AakashPradeep commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r33424861
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -0,0 +1,355 @@
+/*
+ * 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.schema.json;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.schema.EqualityNotSupportedException;
+import org.apache.phoenix.schema.types.*;
+import org.codehaus.jackson.JsonFactory;
+import org.codehaus.jackson.JsonNode;
+import org.codehaus.jackson.JsonParser;
+import org.codehaus.jackson.JsonParser.Feature;
+import org.codehaus.jackson.JsonProcessingException;
+import org.codehaus.jackson.map.ObjectMapper;
+import org.codehaus.jackson.node.ValueNode;
+
+import com.google.common.base.Preconditions;
+
+/**
+ * The {@link PhoenixJson} wraps json and uses Jackson library to parse and traverse the json. It
+ * should be used to represent the JSON data type and also should be used to parse Json data and
+ * read the value from it. It always conside the last value if same key exist more than once.
+ */
+public class PhoenixJson implements Comparable<PhoenixJson> {
+ private final JsonNode rootNode;
+ /*
+ * input data has been stored as it is, since some data is lost when json parser runs, for
+ * example if a JSON object within the value contains the same key more than once then only last
+ * one is stored rest all of them are ignored, which will defy the contract of PJsonDataType of
+ * keeping user data as it is.
+ */
+ private final String jsonAsString;
+
+ /**
+ * Static Factory method to get an {@link PhoenixJson} object. It also validates the json and
+ * throws {@link SQLException} if it is invalid with line number and character.
+ * @param jsonData Json data as {@link String}.
+ * @return {@link PhoenixJson}.
+ * @throws SQLException
+ */
+ public static PhoenixJson getInstance(String jsonData) throws SQLException {
+ if (jsonData == null) {
+ return null;
+ }
+ try {
+ JsonFactory jsonFactory = new JsonFactory();
+ JsonParser jsonParser = jsonFactory.createJsonParser(jsonData);
+ JsonNode jsonNode = getRootJsonNode(jsonParser);
+ return new PhoenixJson(jsonNode, jsonData);
+ } catch (IOException x) {
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.INVALID_JSON_DATA).setRootCause(x)
+ .setMessage(x.getMessage()).build().buildException();
+ }
+
+ }
+
+ /**
+ * Returns the root of the resulting {@link JsonNode} tree.
+ */
+ private static JsonNode getRootJsonNode(JsonParser jsonParser) throws IOException,
+ JsonProcessingException {
+ jsonParser.configure(Feature.ALLOW_COMMENTS, true);
+ ObjectMapper objectMapper = new ObjectMapper();
+ try {
+ return objectMapper.readTree(jsonParser);
+ } finally {
+ jsonParser.close();
+ }
+ }
+
+ /* Default for unit testing */PhoenixJson(final JsonNode node, final String jsonData) {
+ Preconditions.checkNotNull(node, "root node cannot be null for json");
+ this.rootNode = node;
+ this.jsonAsString = jsonData;
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it throws {@link SQLException}.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ * @throws SQLException
+ */
+ public PhoenixJson getPhoenixJson(String[] paths) throws SQLException {
+ try {
+ PhoenixJson phoenixJson = getPhoenixJsonInternal(paths);
+ if (phoenixJson == null) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.");
+ }
+ return phoenixJson;
+ } catch (NumberFormatException nfe) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.", nfe);
+ }
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it return null.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ */
+ public PhoenixJson getPhoenixJsonOrNull(String[] paths) {
+ try {
+ return getPhoenixJsonInternal(paths);
+ } catch (NumberFormatException nfe) {
+ // ignore
+ }
+ return null;
+ }
+
+ /**
+ * Serialize the current {@link PhoenixJson} to String. Its required for
+ * json_extract_path_text(). If we just return node.toString() it will wrap String value in
+ * double quote which is not the expectation, hence avoiding calling toString() on
+ * {@link JsonNode} until PhoenixJson represent a Json Array or container for Json object. If
+ * PhoenixJson just represent a {@link ValueNode} then it should return value returned from
+ * objects toString().
+ */
+ public String serializeToString() {
+ if (this.rootNode == null || this.rootNode.isNull()) {
+ return null;
+ } else if (this.rootNode.isValueNode()) {
+
+ if (this.rootNode.isNumber()) {
+ return this.rootNode.getNumberValue().toString();
+ } else if (this.rootNode.isBoolean()) {
+ return String.valueOf(this.rootNode.getBooleanValue());
+ } else if (this.rootNode.isTextual()) {
+ return this.rootNode.getTextValue();
+ } else {
+ return this.jsonAsString;
+ }
+ } else if (this.rootNode.isArray()) {
+ return this.jsonAsString;
+ } else if (this.rootNode.isContainerNode()) {
+ return this.jsonAsString;
+ }
+
+ return null;
+
+ }
+
+ @Override
+ public String toString() {
+ return this.jsonAsString;
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + this.jsonAsString.hashCode();
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ /**
+ * @return length of the string represented by the current {@link PhoenixJson}.
+ */
+ public int estimateByteSize() {
+ String jsonStr = toString();
+ return jsonStr == null ? 1 : jsonStr.length();
+ }
+
+ public byte[] toBytes() {
+ return Bytes.toBytes(this.jsonAsString);
+ }
+
+ @Override
+ public int compareTo(PhoenixJson o) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
+ JsonNode node = this.rootNode;
+ for (String path : paths) {
+ JsonNode nodeTemp = null;
+ if (node.isArray()) {
+ int index = Integer.parseInt(path);
+ nodeTemp = node.path(index);
+ } else {
+ nodeTemp = node.path(path);
+ }
+ if (nodeTemp == null || nodeTemp.isMissingNode()) {
+ return null;
+ }
+ node = nodeTemp;
+ }
+ return new PhoenixJson(node, node.toString());
+ }
+
+ public int getJsonArrayLength() {
+ int count = 0;
--- End diff --
Please add comment here, its hard to follow why we require this method and where we gonna use this.
Probably it seems it will be used for json_array_length() function implementation. In that case first requirement will be to make sure that input is a valid Json array and if it is so then just check whether the rootnode (member variable of this class) isArray() or not, if it is an array then simply use size() method of ArrayNode class.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by AakashPradeep <gi...@git.apache.org>.
Github user AakashPradeep commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r33425152
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -0,0 +1,355 @@
+/*
+ * 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.schema.json;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.schema.EqualityNotSupportedException;
+import org.apache.phoenix.schema.types.*;
+import org.codehaus.jackson.JsonFactory;
+import org.codehaus.jackson.JsonNode;
+import org.codehaus.jackson.JsonParser;
+import org.codehaus.jackson.JsonParser.Feature;
+import org.codehaus.jackson.JsonProcessingException;
+import org.codehaus.jackson.map.ObjectMapper;
+import org.codehaus.jackson.node.ValueNode;
+
+import com.google.common.base.Preconditions;
+
+/**
+ * The {@link PhoenixJson} wraps json and uses Jackson library to parse and traverse the json. It
+ * should be used to represent the JSON data type and also should be used to parse Json data and
+ * read the value from it. It always conside the last value if same key exist more than once.
+ */
+public class PhoenixJson implements Comparable<PhoenixJson> {
+ private final JsonNode rootNode;
+ /*
+ * input data has been stored as it is, since some data is lost when json parser runs, for
+ * example if a JSON object within the value contains the same key more than once then only last
+ * one is stored rest all of them are ignored, which will defy the contract of PJsonDataType of
+ * keeping user data as it is.
+ */
+ private final String jsonAsString;
+
+ /**
+ * Static Factory method to get an {@link PhoenixJson} object. It also validates the json and
+ * throws {@link SQLException} if it is invalid with line number and character.
+ * @param jsonData Json data as {@link String}.
+ * @return {@link PhoenixJson}.
+ * @throws SQLException
+ */
+ public static PhoenixJson getInstance(String jsonData) throws SQLException {
+ if (jsonData == null) {
+ return null;
+ }
+ try {
+ JsonFactory jsonFactory = new JsonFactory();
+ JsonParser jsonParser = jsonFactory.createJsonParser(jsonData);
+ JsonNode jsonNode = getRootJsonNode(jsonParser);
+ return new PhoenixJson(jsonNode, jsonData);
+ } catch (IOException x) {
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.INVALID_JSON_DATA).setRootCause(x)
+ .setMessage(x.getMessage()).build().buildException();
+ }
+
+ }
+
+ /**
+ * Returns the root of the resulting {@link JsonNode} tree.
+ */
+ private static JsonNode getRootJsonNode(JsonParser jsonParser) throws IOException,
+ JsonProcessingException {
+ jsonParser.configure(Feature.ALLOW_COMMENTS, true);
+ ObjectMapper objectMapper = new ObjectMapper();
+ try {
+ return objectMapper.readTree(jsonParser);
+ } finally {
+ jsonParser.close();
+ }
+ }
+
+ /* Default for unit testing */PhoenixJson(final JsonNode node, final String jsonData) {
+ Preconditions.checkNotNull(node, "root node cannot be null for json");
+ this.rootNode = node;
+ this.jsonAsString = jsonData;
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it throws {@link SQLException}.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ * @throws SQLException
+ */
+ public PhoenixJson getPhoenixJson(String[] paths) throws SQLException {
+ try {
+ PhoenixJson phoenixJson = getPhoenixJsonInternal(paths);
+ if (phoenixJson == null) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.");
+ }
+ return phoenixJson;
+ } catch (NumberFormatException nfe) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.", nfe);
+ }
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it return null.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ */
+ public PhoenixJson getPhoenixJsonOrNull(String[] paths) {
+ try {
+ return getPhoenixJsonInternal(paths);
+ } catch (NumberFormatException nfe) {
+ // ignore
+ }
+ return null;
+ }
+
+ /**
+ * Serialize the current {@link PhoenixJson} to String. Its required for
+ * json_extract_path_text(). If we just return node.toString() it will wrap String value in
+ * double quote which is not the expectation, hence avoiding calling toString() on
+ * {@link JsonNode} until PhoenixJson represent a Json Array or container for Json object. If
+ * PhoenixJson just represent a {@link ValueNode} then it should return value returned from
+ * objects toString().
+ */
+ public String serializeToString() {
+ if (this.rootNode == null || this.rootNode.isNull()) {
+ return null;
+ } else if (this.rootNode.isValueNode()) {
+
+ if (this.rootNode.isNumber()) {
+ return this.rootNode.getNumberValue().toString();
+ } else if (this.rootNode.isBoolean()) {
+ return String.valueOf(this.rootNode.getBooleanValue());
+ } else if (this.rootNode.isTextual()) {
+ return this.rootNode.getTextValue();
+ } else {
+ return this.jsonAsString;
+ }
+ } else if (this.rootNode.isArray()) {
+ return this.jsonAsString;
+ } else if (this.rootNode.isContainerNode()) {
+ return this.jsonAsString;
+ }
+
+ return null;
+
+ }
+
+ @Override
+ public String toString() {
+ return this.jsonAsString;
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + this.jsonAsString.hashCode();
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ /**
+ * @return length of the string represented by the current {@link PhoenixJson}.
+ */
+ public int estimateByteSize() {
+ String jsonStr = toString();
+ return jsonStr == null ? 1 : jsonStr.length();
+ }
+
+ public byte[] toBytes() {
+ return Bytes.toBytes(this.jsonAsString);
+ }
+
+ @Override
+ public int compareTo(PhoenixJson o) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
+ JsonNode node = this.rootNode;
+ for (String path : paths) {
+ JsonNode nodeTemp = null;
+ if (node.isArray()) {
+ int index = Integer.parseInt(path);
+ nodeTemp = node.path(index);
+ } else {
+ nodeTemp = node.path(path);
+ }
+ if (nodeTemp == null || nodeTemp.isMissingNode()) {
+ return null;
+ }
+ node = nodeTemp;
+ }
+ return new PhoenixJson(node, node.toString());
+ }
+
+ public int getJsonArrayLength() {
+ int count = 0;
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ elements.next();
+ count++;
+ }
+ return count;
+ }
+
+ public Object[] getJsonArrayElements() {
+ List<String> elementlist = new ArrayList();
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ JsonNode e = elements.next();
+ elementlist.add(e.toString());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonObjectKeys() {
+ List<String> elementlist = new ArrayList();
+ Iterator<String> fieldnames = this.rootNode.getFieldNames();
+ while(fieldnames.hasNext()){
+ elementlist.add(fieldnames.next());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonFields() {
+ List<String> elementlist = new ArrayList();
+ Iterator<Map.Entry<String, JsonNode>> fields = this.rootNode.getFields();
+
+ while(fields.hasNext()){
+ String fieldsstr = "";
--- End diff --
Define variable only the places where it requires it makes easier to read the code.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35388212
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/JsonArrayLengthFunctionIT.java ---
@@ -0,0 +1,114 @@
+/**
+ *
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+import org.apache.phoenix.expression.function.JsonArrayLengthFunction;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+/**
+ * End to end test for {@link JsonArrayLengthFunction}.
+ *
+ */
+public class JsonArrayLengthFunctionIT extends BaseHBaseManagedTimeIT {
+
+ @Test
+ public void testJsonArrayLengthWithWhereClause() throws Exception {
+ Connection conn = getConnection();
+ String json = "[1,2,3]";
+ String pk = "valueOne";
+ try {
+ populateJsonTable(conn, json, pk);
+
+ String selectQuery = "SELECT col1 FROM testJson WHERE json_array_length(col1) = 3";
+ PreparedStatement stmt = conn.prepareStatement(selectQuery);
+ ResultSet rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("Json data read from DB is not as expected.", json,
+ rs.getString(1));
+ assertFalse(rs.next());
+
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJsonArrayLengthWhenFirstArgumentIsJsonString()
--- End diff --
Change test name to testJsonArrayWithDifferentDataTypes
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35392010
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -231,4 +236,197 @@ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
}
return new PhoenixJson(node, node.toString());
}
+
+ /**
+ * If the current {@link PhoenixJson} is a JsonArray,then it returns the length of the JsonArray.
+ * <p>For example:[1,2,3] ,it will return 3
+ * <p>Its required for json_array_length().
+ * @throws SQLException
+ */
+ public int getJsonArrayLength() throws SQLException {
+ if(this.rootNode.isArray()){
+ return this.rootNode.size();
+ }else{
+ throw new SQLException("The JsonNode should be an Array");
--- End diff --
Add a new SQLExceptionCode and use SQLExceptionInfo.Builder
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394674
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonEachFunction.java ---
@@ -0,0 +1,96 @@
+/*
+ * 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.compile.KeyPart;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode.Argument;
+import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.*;
+
+import java.util.List;
+
+@BuiltInFunction(name = JsonEachFunction.NAME, args = {
+ @Argument(allowedTypes = { PJson.class })})
+public class JsonEachFunction extends ScalarFunction {
+ public static final String NAME = "JSON_EACH";
--- End diff --
Add some tests for this function.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on the pull request:
https://github.com/apache/phoenix/pull/93#issuecomment-124247112
@ictwanglei Can you please rebase and upload new patch, this one does not apply cleanly to the json branch?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by AakashPradeep <gi...@git.apache.org>.
Github user AakashPradeep commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r33424657
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayToJsonFunction.java ---
@@ -0,0 +1,87 @@
+package org.apache.phoenix.expression.function;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode;
+import org.apache.phoenix.schema.IllegalDataException;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.*;
+import org.apache.phoenix.util.ByteUtil;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.List;
+
+
+@FunctionParseNode.BuiltInFunction(name=ArrayToJsonFunction.NAME, args={
+ @FunctionParseNode.Argument(allowedTypes={PVarchar.class})} )
+public class ArrayToJsonFunction extends ScalarFunction {
+ public static final String NAME = "Array_To_Json";
--- End diff --
Please use only capital letters for function name. I guess thats the general convention we are following for other functions though I dont know whether it has any functional significance or not.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by ictwanglei <gi...@git.apache.org>.
Github user ictwanglei closed the pull request at:
https://github.com/apache/phoenix/pull/93
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394793
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -231,4 +236,197 @@ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
}
return new PhoenixJson(node, node.toString());
}
+
+ /**
+ * If the current {@link PhoenixJson} is a JsonArray,then it returns the length of the JsonArray.
+ * <p>For example:[1,2,3] ,it will return 3
+ * <p>Its required for json_array_length().
+ * @throws SQLException
+ */
+ public int getJsonArrayLength() throws SQLException {
+ if(this.rootNode.isArray()){
+ return this.rootNode.size();
+ }else{
+ throw new SQLException("The JsonNode should be an Array");
+ }
+ }
+
+ /**
+ * If the current {@link PhoenixJson} is a JsonArray,then it returns the set of array elements.
+ * <p>For example:[1,false,[2,"string"]]
+ * it will return (new Object[]{"1","false","[2,\"string\"]"})
+ * <p>Its required for json_array_elements().
+ * @return {@link String []} as the set of JSON elements
+ * @throws SQLException
+ */
+ public Object[] getJsonArrayElements() throws SQLException {
+ if(this.rootNode.isArray()) {
+ List<String> elementlist = new ArrayList();
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while (elements.hasNext()) {
+ JsonNode e = elements.next();
+ elementlist.add(e.toString());
+ }
+ return elementlist.toArray();
+ }else{
+ throw new SQLException("The JsonNode should be an Array");
+ }
+ }
+ /**
+ * It returns the set of JSON keys for the current {@link PhoenixJson}.Only the outermost keys will be generated
+ * <p>For example:{"f1":"abc","f2":{"f3":"a", "f4":"b"}}
+ * it will return (new Object[]{"f1","f2"})
+ * <p>Its required for json_object_keys().
+ * @return {@link String []} as the set of JSON keys
+ */
+ public Object[] getJsonObjectKeys() {
+ List<String> elementlist = new ArrayList();
+ Iterator<String> fieldnames = this.rootNode.getFieldNames();
+ while(fieldnames.hasNext()){
+ elementlist.add(fieldnames.next());
+ }
+ return elementlist.toArray();
+ }
+
+ /**
+ * It returns the SET of JSON key/value pairs for the current {@link PhoenixJson}.Only the outermost key/value will be generated
+ * Probably it seems we should use a special SET class which can hold different types of elements.
+ * but we use the {@link org.apache.phoenix.schema.types.PVarcharArray} as an alternative of SET TYPE
+ * when implementing the build-in function ,so we use {@link String} directly to store the pair
+ * and in this case we use "," to separate key and value
+ * <p>For example:{"f1":"abc","f2":"edf"}
+ * it will return (new Object[]{"f1,abc","f2,edf"})
+ *
+ * <p>Its required for json_each().
+ * @return {@link String []} as the SET of JSON key/value pairs
+ */
+ public Object[] getJsonFields() {
+ List<String> elementlist = new ArrayList();
+ Iterator<Map.Entry<String, JsonNode>> fields = this.rootNode.getFields();
+ while(fields.hasNext()){
+ Map.Entry<String, JsonNode> entry = fields.next();
+ StringBuilder fieldBuilder = new StringBuilder();
+ fieldBuilder.append(entry.getKey());
+ fieldBuilder.append(",");
+ fieldBuilder.append(entry.getValue().toString());
+ elementlist.add(fieldBuilder.toString());
+ }
+ return elementlist.toArray();
+ }
+ /**
+ * Expands the object in current {@link PhoenixJson} to a record whose columns match the record type defined by base.
+ * Conversion will be best effort; columns in base with no corresponding key will be left null.
+ * If a column is specified more than once, the last value is used.
+ * Also use "," to separate each columns
+ * <p>For example:types :{"a","b"} json: {"a":"1","b":"2"}
+ * it will return new String("1,2")
+ *
+ * <p>Its required for json_populate_record().
+ * @param types {@link String} the record type
+ * @return {@link String} as the result record
+ */
+ public String jsonPopulateRecord(String [] types) {
--- End diff --
Maybe add a helper function that can be used by both jsonPopulateRecord and jsonPopulateRecordSet?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by AakashPradeep <gi...@git.apache.org>.
Github user AakashPradeep commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r33425159
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -0,0 +1,355 @@
+/*
+ * 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.schema.json;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.schema.EqualityNotSupportedException;
+import org.apache.phoenix.schema.types.*;
+import org.codehaus.jackson.JsonFactory;
+import org.codehaus.jackson.JsonNode;
+import org.codehaus.jackson.JsonParser;
+import org.codehaus.jackson.JsonParser.Feature;
+import org.codehaus.jackson.JsonProcessingException;
+import org.codehaus.jackson.map.ObjectMapper;
+import org.codehaus.jackson.node.ValueNode;
+
+import com.google.common.base.Preconditions;
+
+/**
+ * The {@link PhoenixJson} wraps json and uses Jackson library to parse and traverse the json. It
+ * should be used to represent the JSON data type and also should be used to parse Json data and
+ * read the value from it. It always conside the last value if same key exist more than once.
+ */
+public class PhoenixJson implements Comparable<PhoenixJson> {
+ private final JsonNode rootNode;
+ /*
+ * input data has been stored as it is, since some data is lost when json parser runs, for
+ * example if a JSON object within the value contains the same key more than once then only last
+ * one is stored rest all of them are ignored, which will defy the contract of PJsonDataType of
+ * keeping user data as it is.
+ */
+ private final String jsonAsString;
+
+ /**
+ * Static Factory method to get an {@link PhoenixJson} object. It also validates the json and
+ * throws {@link SQLException} if it is invalid with line number and character.
+ * @param jsonData Json data as {@link String}.
+ * @return {@link PhoenixJson}.
+ * @throws SQLException
+ */
+ public static PhoenixJson getInstance(String jsonData) throws SQLException {
+ if (jsonData == null) {
+ return null;
+ }
+ try {
+ JsonFactory jsonFactory = new JsonFactory();
+ JsonParser jsonParser = jsonFactory.createJsonParser(jsonData);
+ JsonNode jsonNode = getRootJsonNode(jsonParser);
+ return new PhoenixJson(jsonNode, jsonData);
+ } catch (IOException x) {
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.INVALID_JSON_DATA).setRootCause(x)
+ .setMessage(x.getMessage()).build().buildException();
+ }
+
+ }
+
+ /**
+ * Returns the root of the resulting {@link JsonNode} tree.
+ */
+ private static JsonNode getRootJsonNode(JsonParser jsonParser) throws IOException,
+ JsonProcessingException {
+ jsonParser.configure(Feature.ALLOW_COMMENTS, true);
+ ObjectMapper objectMapper = new ObjectMapper();
+ try {
+ return objectMapper.readTree(jsonParser);
+ } finally {
+ jsonParser.close();
+ }
+ }
+
+ /* Default for unit testing */PhoenixJson(final JsonNode node, final String jsonData) {
+ Preconditions.checkNotNull(node, "root node cannot be null for json");
+ this.rootNode = node;
+ this.jsonAsString = jsonData;
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it throws {@link SQLException}.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ * @throws SQLException
+ */
+ public PhoenixJson getPhoenixJson(String[] paths) throws SQLException {
+ try {
+ PhoenixJson phoenixJson = getPhoenixJsonInternal(paths);
+ if (phoenixJson == null) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.");
+ }
+ return phoenixJson;
+ } catch (NumberFormatException nfe) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.", nfe);
+ }
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it return null.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ */
+ public PhoenixJson getPhoenixJsonOrNull(String[] paths) {
+ try {
+ return getPhoenixJsonInternal(paths);
+ } catch (NumberFormatException nfe) {
+ // ignore
+ }
+ return null;
+ }
+
+ /**
+ * Serialize the current {@link PhoenixJson} to String. Its required for
+ * json_extract_path_text(). If we just return node.toString() it will wrap String value in
+ * double quote which is not the expectation, hence avoiding calling toString() on
+ * {@link JsonNode} until PhoenixJson represent a Json Array or container for Json object. If
+ * PhoenixJson just represent a {@link ValueNode} then it should return value returned from
+ * objects toString().
+ */
+ public String serializeToString() {
+ if (this.rootNode == null || this.rootNode.isNull()) {
+ return null;
+ } else if (this.rootNode.isValueNode()) {
+
+ if (this.rootNode.isNumber()) {
+ return this.rootNode.getNumberValue().toString();
+ } else if (this.rootNode.isBoolean()) {
+ return String.valueOf(this.rootNode.getBooleanValue());
+ } else if (this.rootNode.isTextual()) {
+ return this.rootNode.getTextValue();
+ } else {
+ return this.jsonAsString;
+ }
+ } else if (this.rootNode.isArray()) {
+ return this.jsonAsString;
+ } else if (this.rootNode.isContainerNode()) {
+ return this.jsonAsString;
+ }
+
+ return null;
+
+ }
+
+ @Override
+ public String toString() {
+ return this.jsonAsString;
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + this.jsonAsString.hashCode();
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ /**
+ * @return length of the string represented by the current {@link PhoenixJson}.
+ */
+ public int estimateByteSize() {
+ String jsonStr = toString();
+ return jsonStr == null ? 1 : jsonStr.length();
+ }
+
+ public byte[] toBytes() {
+ return Bytes.toBytes(this.jsonAsString);
+ }
+
+ @Override
+ public int compareTo(PhoenixJson o) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
+ JsonNode node = this.rootNode;
+ for (String path : paths) {
+ JsonNode nodeTemp = null;
+ if (node.isArray()) {
+ int index = Integer.parseInt(path);
+ nodeTemp = node.path(index);
+ } else {
+ nodeTemp = node.path(path);
+ }
+ if (nodeTemp == null || nodeTemp.isMissingNode()) {
+ return null;
+ }
+ node = nodeTemp;
+ }
+ return new PhoenixJson(node, node.toString());
+ }
+
+ public int getJsonArrayLength() {
+ int count = 0;
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ elements.next();
+ count++;
+ }
+ return count;
+ }
+
+ public Object[] getJsonArrayElements() {
+ List<String> elementlist = new ArrayList();
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ JsonNode e = elements.next();
+ elementlist.add(e.toString());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonObjectKeys() {
+ List<String> elementlist = new ArrayList();
+ Iterator<String> fieldnames = this.rootNode.getFieldNames();
+ while(fieldnames.hasNext()){
+ elementlist.add(fieldnames.next());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonFields() {
+ List<String> elementlist = new ArrayList();
+ Iterator<Map.Entry<String, JsonNode>> fields = this.rootNode.getFields();
+
+ while(fields.hasNext()){
+ String fieldsstr = "";
+ Map.Entry<String, JsonNode> entry = fields.next();
+ fieldsstr += entry.getKey() +","+entry.getValue().toString();
--- End diff --
If you define String variable here, you do not need to use "+" operator, which is a costly affair avoid using it as much as possible.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by ictwanglei <gi...@git.apache.org>.
Github user ictwanglei commented on the pull request:
https://github.com/apache/phoenix/pull/93#issuecomment-117210029
@AakashPradeep sorry for the missing of comments and other coding mistakes and thanks for your patient guidance. I have modified the code according to your suggestion.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35392777
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonArrayElementsFunction.java ---
@@ -0,0 +1,107 @@
+/*
+ * 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.compile.KeyPart;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode.Argument;
+import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction;
+import org.apache.phoenix.schema.IllegalDataException;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.*;
+import org.apache.phoenix.util.ByteUtil;
+
+import java.sql.SQLException;
+import java.util.List;
+
+@BuiltInFunction(name = JsonArrayElementsFunction.NAME, args = {
+ @Argument(allowedTypes = { PJson.class })})
+public class JsonArrayElementsFunction extends ScalarFunction {
--- End diff --
The corresponding postgress function returns a SETOF json. Is there a way that can be supported?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394919
--- Diff: phoenix-core/src/test/java/org/apache/phoenix/expression/function/JsonFunctionTest.java ---
@@ -0,0 +1,284 @@
+/*
+ * 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.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.types.*;
+import org.junit.Test;
+
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Date;
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
+import java.util.List;
+
+import static org.junit.Assert.assertEquals;
+
+
+/**
+ * Unit tests for JSON build-in function.
+ * Testing function includes below:
+ * {@link ArrayToJsonFunction}
+ * {@link JsonArrayElementsFunction}
+ * {@link JsonPopulateRecordFunction}
+ * {@link JsonPopulateRecordSetFunction}
+ * {@link JsonArrayLengthFunction}
+ * {@link JsonObjectKeysFunction}
+ * {@link ToJsonFunction}
+ * {@link JsonEachFunction}
+ *
+ */
+public class JsonFunctionTest {
+ public static final String TEST_JSON_STR =
+ "{\"f2\":{\"f3\":\"value\"},\"f4\":{\"f5\":99,\"f6\":[1,true,\"foo\"]},\"f7\":true}";
+
+ private static PhoenixJson testArrayToJson (Object[] array,PDataType datatype,PArrayDataType arraydatatype) throws Exception {
+ PhoenixArray pa = PArrayDataType.instantiatePhoenixArray( datatype,array);
+ LiteralExpression arrayExpr = LiteralExpression.newConstant(pa,arraydatatype );
+ List<Expression> children = Arrays.<Expression>asList(arrayExpr);
+ ArrayToJsonFunction e = new ArrayToJsonFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixJson result = (PhoenixJson)e.getDataType().toObject(ptr);
+ return result;
+ }
+
+ @Test
+ public void testNumberArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{1,12,32,432};
+ PhoenixJson result = testArrayToJson(testarray, PInteger.INSTANCE, PIntegerArray.INSTANCE);
--- End diff --
Just add test that executes this function as a query.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35374980
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/JsonArrayLengthFunctionIT.java ---
@@ -0,0 +1,114 @@
+/**
--- End diff --
Add the APACHE header
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by AakashPradeep <gi...@git.apache.org>.
Github user AakashPradeep commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r33425135
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -0,0 +1,355 @@
+/*
+ * 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.schema.json;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.schema.EqualityNotSupportedException;
+import org.apache.phoenix.schema.types.*;
+import org.codehaus.jackson.JsonFactory;
+import org.codehaus.jackson.JsonNode;
+import org.codehaus.jackson.JsonParser;
+import org.codehaus.jackson.JsonParser.Feature;
+import org.codehaus.jackson.JsonProcessingException;
+import org.codehaus.jackson.map.ObjectMapper;
+import org.codehaus.jackson.node.ValueNode;
+
+import com.google.common.base.Preconditions;
+
+/**
+ * The {@link PhoenixJson} wraps json and uses Jackson library to parse and traverse the json. It
+ * should be used to represent the JSON data type and also should be used to parse Json data and
+ * read the value from it. It always conside the last value if same key exist more than once.
+ */
+public class PhoenixJson implements Comparable<PhoenixJson> {
+ private final JsonNode rootNode;
+ /*
+ * input data has been stored as it is, since some data is lost when json parser runs, for
+ * example if a JSON object within the value contains the same key more than once then only last
+ * one is stored rest all of them are ignored, which will defy the contract of PJsonDataType of
+ * keeping user data as it is.
+ */
+ private final String jsonAsString;
+
+ /**
+ * Static Factory method to get an {@link PhoenixJson} object. It also validates the json and
+ * throws {@link SQLException} if it is invalid with line number and character.
+ * @param jsonData Json data as {@link String}.
+ * @return {@link PhoenixJson}.
+ * @throws SQLException
+ */
+ public static PhoenixJson getInstance(String jsonData) throws SQLException {
+ if (jsonData == null) {
+ return null;
+ }
+ try {
+ JsonFactory jsonFactory = new JsonFactory();
+ JsonParser jsonParser = jsonFactory.createJsonParser(jsonData);
+ JsonNode jsonNode = getRootJsonNode(jsonParser);
+ return new PhoenixJson(jsonNode, jsonData);
+ } catch (IOException x) {
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.INVALID_JSON_DATA).setRootCause(x)
+ .setMessage(x.getMessage()).build().buildException();
+ }
+
+ }
+
+ /**
+ * Returns the root of the resulting {@link JsonNode} tree.
+ */
+ private static JsonNode getRootJsonNode(JsonParser jsonParser) throws IOException,
+ JsonProcessingException {
+ jsonParser.configure(Feature.ALLOW_COMMENTS, true);
+ ObjectMapper objectMapper = new ObjectMapper();
+ try {
+ return objectMapper.readTree(jsonParser);
+ } finally {
+ jsonParser.close();
+ }
+ }
+
+ /* Default for unit testing */PhoenixJson(final JsonNode node, final String jsonData) {
+ Preconditions.checkNotNull(node, "root node cannot be null for json");
+ this.rootNode = node;
+ this.jsonAsString = jsonData;
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it throws {@link SQLException}.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ * @throws SQLException
+ */
+ public PhoenixJson getPhoenixJson(String[] paths) throws SQLException {
+ try {
+ PhoenixJson phoenixJson = getPhoenixJsonInternal(paths);
+ if (phoenixJson == null) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.");
+ }
+ return phoenixJson;
+ } catch (NumberFormatException nfe) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.", nfe);
+ }
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it return null.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ */
+ public PhoenixJson getPhoenixJsonOrNull(String[] paths) {
+ try {
+ return getPhoenixJsonInternal(paths);
+ } catch (NumberFormatException nfe) {
+ // ignore
+ }
+ return null;
+ }
+
+ /**
+ * Serialize the current {@link PhoenixJson} to String. Its required for
+ * json_extract_path_text(). If we just return node.toString() it will wrap String value in
+ * double quote which is not the expectation, hence avoiding calling toString() on
+ * {@link JsonNode} until PhoenixJson represent a Json Array or container for Json object. If
+ * PhoenixJson just represent a {@link ValueNode} then it should return value returned from
+ * objects toString().
+ */
+ public String serializeToString() {
+ if (this.rootNode == null || this.rootNode.isNull()) {
+ return null;
+ } else if (this.rootNode.isValueNode()) {
+
+ if (this.rootNode.isNumber()) {
+ return this.rootNode.getNumberValue().toString();
+ } else if (this.rootNode.isBoolean()) {
+ return String.valueOf(this.rootNode.getBooleanValue());
+ } else if (this.rootNode.isTextual()) {
+ return this.rootNode.getTextValue();
+ } else {
+ return this.jsonAsString;
+ }
+ } else if (this.rootNode.isArray()) {
+ return this.jsonAsString;
+ } else if (this.rootNode.isContainerNode()) {
+ return this.jsonAsString;
+ }
+
+ return null;
+
+ }
+
+ @Override
+ public String toString() {
+ return this.jsonAsString;
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + this.jsonAsString.hashCode();
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ /**
+ * @return length of the string represented by the current {@link PhoenixJson}.
+ */
+ public int estimateByteSize() {
+ String jsonStr = toString();
+ return jsonStr == null ? 1 : jsonStr.length();
+ }
+
+ public byte[] toBytes() {
+ return Bytes.toBytes(this.jsonAsString);
+ }
+
+ @Override
+ public int compareTo(PhoenixJson o) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
+ JsonNode node = this.rootNode;
+ for (String path : paths) {
+ JsonNode nodeTemp = null;
+ if (node.isArray()) {
+ int index = Integer.parseInt(path);
+ nodeTemp = node.path(index);
+ } else {
+ nodeTemp = node.path(path);
+ }
+ if (nodeTemp == null || nodeTemp.isMissingNode()) {
+ return null;
+ }
+ node = nodeTemp;
+ }
+ return new PhoenixJson(node, node.toString());
+ }
+
+ public int getJsonArrayLength() {
+ int count = 0;
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ elements.next();
+ count++;
+ }
+ return count;
+ }
+
+ public Object[] getJsonArrayElements() {
+ List<String> elementlist = new ArrayList();
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ JsonNode e = elements.next();
+ elementlist.add(e.toString());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonObjectKeys() {
+ List<String> elementlist = new ArrayList();
+ Iterator<String> fieldnames = this.rootNode.getFieldNames();
+ while(fieldnames.hasNext()){
+ elementlist.add(fieldnames.next());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonFields() {
+ List<String> elementlist = new ArrayList();
+ Iterator<Map.Entry<String, JsonNode>> fields = this.rootNode.getFields();
+
+ while(fields.hasNext()){
+ String fieldsstr = "";
+ Map.Entry<String, JsonNode> entry = fields.next();
+ fieldsstr += entry.getKey() +","+entry.getValue().toString();
--- End diff --
Please specify why you are using "," as a separator b/w key and value.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394964
--- Diff: phoenix-core/src/test/java/org/apache/phoenix/expression/function/JsonFunctionTest.java ---
@@ -0,0 +1,284 @@
+/*
+ * 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.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.types.*;
+import org.junit.Test;
+
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Date;
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
+import java.util.List;
+
+import static org.junit.Assert.assertEquals;
+
+
+/**
+ * Unit tests for JSON build-in function.
+ * Testing function includes below:
+ * {@link ArrayToJsonFunction}
+ * {@link JsonArrayElementsFunction}
+ * {@link JsonPopulateRecordFunction}
+ * {@link JsonPopulateRecordSetFunction}
+ * {@link JsonArrayLengthFunction}
+ * {@link JsonObjectKeysFunction}
+ * {@link ToJsonFunction}
+ * {@link JsonEachFunction}
+ *
+ */
+public class JsonFunctionTest {
+ public static final String TEST_JSON_STR =
+ "{\"f2\":{\"f3\":\"value\"},\"f4\":{\"f5\":99,\"f6\":[1,true,\"foo\"]},\"f7\":true}";
+
+ private static PhoenixJson testArrayToJson (Object[] array,PDataType datatype,PArrayDataType arraydatatype) throws Exception {
+ PhoenixArray pa = PArrayDataType.instantiatePhoenixArray( datatype,array);
+ LiteralExpression arrayExpr = LiteralExpression.newConstant(pa,arraydatatype );
+ List<Expression> children = Arrays.<Expression>asList(arrayExpr);
+ ArrayToJsonFunction e = new ArrayToJsonFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixJson result = (PhoenixJson)e.getDataType().toObject(ptr);
+ return result;
+ }
+
+ @Test
+ public void testNumberArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{1,12,32,432};
+ PhoenixJson result = testArrayToJson(testarray, PInteger.INSTANCE, PIntegerArray.INSTANCE);
+ String expected = "[1,12,32,432]";
+ assertEquals(result.serializeToString(), expected);
+ Object[] testarray2 = new Object[]{1.12,12.34,32.45,432.78};
+ PhoenixJson result2 = testArrayToJson(testarray2, PDouble.INSTANCE, PDoubleArray.INSTANCE);
+ String expected2 = "[1.12,12.34,32.45,432.78]";
+ assertEquals(result2.serializeToString(), expected2);
+ }
+ @Test
+ public void testBooleanArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{false,true};
+ PhoenixJson result = testArrayToJson(testarray, PBoolean.INSTANCE, PBooleanArray.INSTANCE);
+ String expected = "[false,true]";
+ assertEquals(result.toString(), expected);
+ }
+
+ @Test
+ public void testStringArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{"abc123","12.3","string","汉字"};
+ PhoenixJson result = testArrayToJson(testarray, PVarchar.INSTANCE, PVarcharArray.INSTANCE);
+ String expected ="[\"abc123\",\"12.3\",\"string\",\"汉字\"]";
+ assertEquals(result.serializeToString(), expected);
+ }
+ @Test
+ public void testDateArrayToJson() throws Exception {
+ SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss.SSS");
+ Date date1= myFormatter.parse("1990-12-01 11:01:45.0");
+ Date date2 = myFormatter.parse("1989-03-12 13:01:45.0");
+ Date date3 = myFormatter.parse("1974-06-06 12:01:45.0");
+ Object[] testarray = new Object[]{date1,date2,date3};
+ PhoenixJson result = testArrayToJson(testarray, PDate.INSTANCE,PDateArray.INSTANCE);
+ String expected ="[\"1990-12-01\",\"1989-03-12\",\"1974-06-06\"]";
+ assertEquals(result.serializeToString(), expected);
+
+ Timestamp ts1 = Timestamp.valueOf("1990-12-01 11:01:45.123");
+ Timestamp ts2 = Timestamp.valueOf("1989-03-12 01:01:01.0");
+ Timestamp ts3 = Timestamp.valueOf("1989-03-12 23:59:59.1");
+ testarray = new Object[]{ts1,ts2,ts3};
+ result = testArrayToJson(testarray, PTimestamp.INSTANCE,PTimestampArray.INSTANCE);
+ expected ="[\"1990-12-01 11:01:45.123\",\"1989-03-12 01:01:01.0\",\"1989-03-12 23:59:59.1\"]";
+ assertEquals(result.serializeToString(), expected);
+
+ Time t1 = new Time(date1.getTime());
+ Time t2 = new Time(date2.getTime());
+ Time t3 = new Time(date3.getTime());
+ testarray = new Object[]{t1,t2,t3};
+ result = testArrayToJson(testarray, PTime.INSTANCE,PTimeArray.INSTANCE);
+ expected ="[\"11:01:45\",\"13:01:45\",\"12:01:45\"]";
+ assertEquals(result.serializeToString(), expected);
+
+
+ }
+
+
+ private static String[] jsonArrayElements (String json) throws Exception {
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr = LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children = Arrays.<Expression>asList(JsonExpr);
+ JsonArrayElementsFunction e = new JsonArrayElementsFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixArray pa = (PhoenixArray)e.getDataType().toObject(ptr);
+ return (String[] )pa.getArray();
+ }
+
+ @Test
+ public void testJsonArrayElements() throws Exception {
+ String json = "[1,true,\"string\",[2,false]]";
+ Object[] expected = new Object[]{"1","true","\"string\"","[2,false]"};
+ String[] result = jsonArrayElements(json);
+ assertEquals(result.length, expected.length);
+ for(int i = 0; i<result.length;i++){
+ assertEquals(result[i], expected[i]);
+ }
+ }
+ private static String jsonPopulateRecord (Object[] types,String json) throws Exception {
+ PhoenixArray pa =PArrayDataType.instantiatePhoenixArray( PVarchar.INSTANCE,types);
+ LiteralExpression typesExpr = LiteralExpression.newConstant(pa,PVarcharArray.INSTANCE );
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr= LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children = Arrays.<Expression>asList(typesExpr,JsonExpr);
+ JsonPopulateRecordFunction e = new JsonPopulateRecordFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ String record = (String)e.getDataType().toObject(ptr);
+ return record;
+ }
+
+ @Test
+ public void testJsonPopulateRecord() throws Exception {
+ Object[] types= new Object[]{"a","b"};
+ String json = "{\"a\":1,\"b\":2}";
+ String expected = "1,2";
+ String result = jsonPopulateRecord(types, json);
+ assertEquals(result, expected);
+ }
+
+ private static String[] jsonPopulateRecordSet (Object[] types,String json) throws Exception {
+ PhoenixArray pa =PArrayDataType.instantiatePhoenixArray( PVarchar.INSTANCE,types);
+ LiteralExpression typesExpr = LiteralExpression.newConstant(pa,PVarcharArray.INSTANCE );
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr = LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children = Arrays.<Expression>asList(typesExpr,JsonExpr);
+ JsonPopulateRecordSetFunction e = new JsonPopulateRecordSetFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixArray record = (PhoenixArray)e.getDataType().toObject(ptr);
+ return (String[] )record.getArray();
+ }
+
+
+ @Test
+ public void testJsonPopulateRecordSet() throws Exception {
+ Object[] types= new Object[]{"a","b"};
+ String json = "[{\"a\":1,\"b\":2},{\"a\":2,\"b\":3},{\"a\":4,\"b\":5}]";
+ Object[] expected = new Object[]{"1,2","2,3","4,5"};
+ String[] result = jsonPopulateRecordSet(types, json);
+ assertEquals(result.length, expected.length);
+ for(int i = 0; i<result.length;i++){
+ assertEquals(result[i], expected[i]);
+ }
+ }
+
+ private static int jsonArrayLength (String json) throws Exception {
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr = LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children= Arrays.<Expression>asList(JsonExpr);
+ JsonArrayLengthFunction e = new JsonArrayLengthFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ int len = (int)e.getDataType().toObject(ptr);
+ return len;
+ }
+
+ @Test
+ public void testJsonArrayLength() throws Exception {
+ String array1 = "[1,true,\"string\",[2,false]]";
+ String array2 = "[1,2.34,[1,\"abc\"],4,true,\"string\",[2,false]]";
+ assertEquals(jsonArrayLength(array1),4);
+ assertEquals(jsonArrayLength(array2),7);
+ }
+
+
+ private static PhoenixJson toJson (Object obj,PDataType datatype) throws Exception {
+ LiteralExpression op =LiteralExpression.newConstant(obj, datatype);
+ List<Expression> children = Arrays.<Expression>asList(op);
+ ToJsonFunction e = new ToJsonFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixJson result = (PhoenixJson)e.getDataType().toObject(ptr);
+ return result;
+ }
+
+
+ @Test
+ public void testToJson() throws Exception {
+ assertEquals(toJson(-256, PInteger.INSTANCE).serializeToString(),"-256");
+ assertEquals(toJson(-256, PLong.INSTANCE).serializeToString(),"-256");
+ assertEquals(toJson(-1, PSmallint.INSTANCE).serializeToString(),"-1");
+ assertEquals(toJson(-1, PTinyint.INSTANCE).serializeToString(),"-1");
+ assertEquals(toJson(12, PUnsignedInt.INSTANCE).serializeToString(),"12");
+ assertEquals(toJson(12, PUnsignedSmallint.INSTANCE).serializeToString(),"12");
+ assertEquals(toJson(12, PUnsignedLong.INSTANCE).serializeToString(),"12");
+ assertEquals(toJson(123.456, PDouble.INSTANCE).serializeToString(),"123.456");
+ assertEquals(toJson(123.456, PFloat.INSTANCE).serializeToString(),"123.456");
+ assertEquals(toJson(123.456, PUnsignedDouble.INSTANCE).serializeToString(),"123.456");
+ assertEquals(toJson(123.456, PUnsignedFloat.INSTANCE).serializeToString(),"123.456");
+ assertEquals(toJson(false, PBoolean.INSTANCE).serializeToString(),"false");
+ assertEquals(toJson(true, PBoolean.INSTANCE).serializeToString(),"true");
+ assertEquals(toJson("string_abc", PVarchar.INSTANCE).toString(),"\"string_abc\"");
+ assertEquals(toJson("string_abc", PVarchar.INSTANCE).serializeToString(),"string_abc");
+ }
+ private static String[] jsonObjectKeys (String json) throws Exception {
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr = LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children = Arrays.<Expression>asList(JsonExpr);
+ JsonObjectKeysFunction e = new JsonObjectKeysFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixArray pa = (PhoenixArray)e.getDataType().toObject(ptr);
+ return (String[] )pa.getArray();
+ }
+
+ @Test
+ public void testJsonObjectKeys() throws Exception {
+ Object[] expected = new Object[]{"f2","f4","f7"};
+ String[] result = jsonObjectKeys(TEST_JSON_STR);
+
+ assertEquals(result.length, expected.length);
+ for(int i = 0; i<result.length;i++){
+ assertEquals(result[i], expected[i]);
+ }
+ }
+
+ private static String[] jsonEach (String json) throws Exception {
--- End diff --
Add a test that executes this function as a query.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35394953
--- Diff: phoenix-core/src/test/java/org/apache/phoenix/expression/function/JsonFunctionTest.java ---
@@ -0,0 +1,284 @@
+/*
+ * 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.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.LiteralExpression;
+import org.apache.phoenix.schema.json.PhoenixJson;
+import org.apache.phoenix.schema.types.*;
+import org.junit.Test;
+
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Date;
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
+import java.util.List;
+
+import static org.junit.Assert.assertEquals;
+
+
+/**
+ * Unit tests for JSON build-in function.
+ * Testing function includes below:
+ * {@link ArrayToJsonFunction}
+ * {@link JsonArrayElementsFunction}
+ * {@link JsonPopulateRecordFunction}
+ * {@link JsonPopulateRecordSetFunction}
+ * {@link JsonArrayLengthFunction}
+ * {@link JsonObjectKeysFunction}
+ * {@link ToJsonFunction}
+ * {@link JsonEachFunction}
+ *
+ */
+public class JsonFunctionTest {
+ public static final String TEST_JSON_STR =
+ "{\"f2\":{\"f3\":\"value\"},\"f4\":{\"f5\":99,\"f6\":[1,true,\"foo\"]},\"f7\":true}";
+
+ private static PhoenixJson testArrayToJson (Object[] array,PDataType datatype,PArrayDataType arraydatatype) throws Exception {
+ PhoenixArray pa = PArrayDataType.instantiatePhoenixArray( datatype,array);
+ LiteralExpression arrayExpr = LiteralExpression.newConstant(pa,arraydatatype );
+ List<Expression> children = Arrays.<Expression>asList(arrayExpr);
+ ArrayToJsonFunction e = new ArrayToJsonFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixJson result = (PhoenixJson)e.getDataType().toObject(ptr);
+ return result;
+ }
+
+ @Test
+ public void testNumberArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{1,12,32,432};
+ PhoenixJson result = testArrayToJson(testarray, PInteger.INSTANCE, PIntegerArray.INSTANCE);
+ String expected = "[1,12,32,432]";
+ assertEquals(result.serializeToString(), expected);
+ Object[] testarray2 = new Object[]{1.12,12.34,32.45,432.78};
+ PhoenixJson result2 = testArrayToJson(testarray2, PDouble.INSTANCE, PDoubleArray.INSTANCE);
+ String expected2 = "[1.12,12.34,32.45,432.78]";
+ assertEquals(result2.serializeToString(), expected2);
+ }
+ @Test
+ public void testBooleanArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{false,true};
+ PhoenixJson result = testArrayToJson(testarray, PBoolean.INSTANCE, PBooleanArray.INSTANCE);
+ String expected = "[false,true]";
+ assertEquals(result.toString(), expected);
+ }
+
+ @Test
+ public void testStringArrayToJson() throws Exception {
+ Object[] testarray = new Object[]{"abc123","12.3","string","汉字"};
+ PhoenixJson result = testArrayToJson(testarray, PVarchar.INSTANCE, PVarcharArray.INSTANCE);
+ String expected ="[\"abc123\",\"12.3\",\"string\",\"汉字\"]";
+ assertEquals(result.serializeToString(), expected);
+ }
+ @Test
+ public void testDateArrayToJson() throws Exception {
+ SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss.SSS");
+ Date date1= myFormatter.parse("1990-12-01 11:01:45.0");
+ Date date2 = myFormatter.parse("1989-03-12 13:01:45.0");
+ Date date3 = myFormatter.parse("1974-06-06 12:01:45.0");
+ Object[] testarray = new Object[]{date1,date2,date3};
+ PhoenixJson result = testArrayToJson(testarray, PDate.INSTANCE,PDateArray.INSTANCE);
+ String expected ="[\"1990-12-01\",\"1989-03-12\",\"1974-06-06\"]";
+ assertEquals(result.serializeToString(), expected);
+
+ Timestamp ts1 = Timestamp.valueOf("1990-12-01 11:01:45.123");
+ Timestamp ts2 = Timestamp.valueOf("1989-03-12 01:01:01.0");
+ Timestamp ts3 = Timestamp.valueOf("1989-03-12 23:59:59.1");
+ testarray = new Object[]{ts1,ts2,ts3};
+ result = testArrayToJson(testarray, PTimestamp.INSTANCE,PTimestampArray.INSTANCE);
+ expected ="[\"1990-12-01 11:01:45.123\",\"1989-03-12 01:01:01.0\",\"1989-03-12 23:59:59.1\"]";
+ assertEquals(result.serializeToString(), expected);
+
+ Time t1 = new Time(date1.getTime());
+ Time t2 = new Time(date2.getTime());
+ Time t3 = new Time(date3.getTime());
+ testarray = new Object[]{t1,t2,t3};
+ result = testArrayToJson(testarray, PTime.INSTANCE,PTimeArray.INSTANCE);
+ expected ="[\"11:01:45\",\"13:01:45\",\"12:01:45\"]";
+ assertEquals(result.serializeToString(), expected);
+
+
+ }
+
+
+ private static String[] jsonArrayElements (String json) throws Exception {
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr = LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children = Arrays.<Expression>asList(JsonExpr);
+ JsonArrayElementsFunction e = new JsonArrayElementsFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ PhoenixArray pa = (PhoenixArray)e.getDataType().toObject(ptr);
+ return (String[] )pa.getArray();
+ }
+
+ @Test
+ public void testJsonArrayElements() throws Exception {
+ String json = "[1,true,\"string\",[2,false]]";
+ Object[] expected = new Object[]{"1","true","\"string\"","[2,false]"};
+ String[] result = jsonArrayElements(json);
+ assertEquals(result.length, expected.length);
+ for(int i = 0; i<result.length;i++){
+ assertEquals(result[i], expected[i]);
+ }
+ }
+ private static String jsonPopulateRecord (Object[] types,String json) throws Exception {
+ PhoenixArray pa =PArrayDataType.instantiatePhoenixArray( PVarchar.INSTANCE,types);
+ LiteralExpression typesExpr = LiteralExpression.newConstant(pa,PVarcharArray.INSTANCE );
+ PhoenixJson phoenixJson = PhoenixJson.getInstance(json);
+ LiteralExpression JsonExpr= LiteralExpression.newConstant(phoenixJson,PJson.INSTANCE );
+ List<Expression> children = Arrays.<Expression>asList(typesExpr,JsonExpr);
+ JsonPopulateRecordFunction e = new JsonPopulateRecordFunction(children);
+ ImmutableBytesWritable ptr = new ImmutableBytesWritable();
+ boolean evaluated = e.evaluate(null, ptr);
+ String record = (String)e.getDataType().toObject(ptr);
+ return record;
+ }
+
+ @Test
+ public void testJsonPopulateRecord() throws Exception {
--- End diff --
Add a test that executes this function as a query.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by twdsilva <gi...@git.apache.org>.
Github user twdsilva commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r35388305
--- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/JsonArrayLengthFunctionIT.java ---
@@ -0,0 +1,114 @@
+/**
+ *
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+import org.apache.phoenix.expression.function.JsonArrayLengthFunction;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+/**
+ * End to end test for {@link JsonArrayLengthFunction}.
+ *
+ */
+public class JsonArrayLengthFunctionIT extends BaseHBaseManagedTimeIT {
+
+ @Test
+ public void testJsonArrayLengthWithWhereClause() throws Exception {
--- End diff --
Can you test with more complex json (like nested json and the different data types)
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] phoenix pull request: PHOENIX-1661 Implement built-in functions fo...
Posted by AakashPradeep <gi...@git.apache.org>.
Github user AakashPradeep commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/93#discussion_r33425136
--- Diff: phoenix-core/src/main/java/org/apache/phoenix/schema/json/PhoenixJson.java ---
@@ -0,0 +1,355 @@
+/*
+ * 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.schema.json;
+
+import java.io.IOException;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.schema.EqualityNotSupportedException;
+import org.apache.phoenix.schema.types.*;
+import org.codehaus.jackson.JsonFactory;
+import org.codehaus.jackson.JsonNode;
+import org.codehaus.jackson.JsonParser;
+import org.codehaus.jackson.JsonParser.Feature;
+import org.codehaus.jackson.JsonProcessingException;
+import org.codehaus.jackson.map.ObjectMapper;
+import org.codehaus.jackson.node.ValueNode;
+
+import com.google.common.base.Preconditions;
+
+/**
+ * The {@link PhoenixJson} wraps json and uses Jackson library to parse and traverse the json. It
+ * should be used to represent the JSON data type and also should be used to parse Json data and
+ * read the value from it. It always conside the last value if same key exist more than once.
+ */
+public class PhoenixJson implements Comparable<PhoenixJson> {
+ private final JsonNode rootNode;
+ /*
+ * input data has been stored as it is, since some data is lost when json parser runs, for
+ * example if a JSON object within the value contains the same key more than once then only last
+ * one is stored rest all of them are ignored, which will defy the contract of PJsonDataType of
+ * keeping user data as it is.
+ */
+ private final String jsonAsString;
+
+ /**
+ * Static Factory method to get an {@link PhoenixJson} object. It also validates the json and
+ * throws {@link SQLException} if it is invalid with line number and character.
+ * @param jsonData Json data as {@link String}.
+ * @return {@link PhoenixJson}.
+ * @throws SQLException
+ */
+ public static PhoenixJson getInstance(String jsonData) throws SQLException {
+ if (jsonData == null) {
+ return null;
+ }
+ try {
+ JsonFactory jsonFactory = new JsonFactory();
+ JsonParser jsonParser = jsonFactory.createJsonParser(jsonData);
+ JsonNode jsonNode = getRootJsonNode(jsonParser);
+ return new PhoenixJson(jsonNode, jsonData);
+ } catch (IOException x) {
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.INVALID_JSON_DATA).setRootCause(x)
+ .setMessage(x.getMessage()).build().buildException();
+ }
+
+ }
+
+ /**
+ * Returns the root of the resulting {@link JsonNode} tree.
+ */
+ private static JsonNode getRootJsonNode(JsonParser jsonParser) throws IOException,
+ JsonProcessingException {
+ jsonParser.configure(Feature.ALLOW_COMMENTS, true);
+ ObjectMapper objectMapper = new ObjectMapper();
+ try {
+ return objectMapper.readTree(jsonParser);
+ } finally {
+ jsonParser.close();
+ }
+ }
+
+ /* Default for unit testing */PhoenixJson(final JsonNode node, final String jsonData) {
+ Preconditions.checkNotNull(node, "root node cannot be null for json");
+ this.rootNode = node;
+ this.jsonAsString = jsonData;
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it throws {@link SQLException}.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ * @throws SQLException
+ */
+ public PhoenixJson getPhoenixJson(String[] paths) throws SQLException {
+ try {
+ PhoenixJson phoenixJson = getPhoenixJsonInternal(paths);
+ if (phoenixJson == null) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.");
+ }
+ return phoenixJson;
+ } catch (NumberFormatException nfe) {
+ throw new SQLException("path: " + Arrays.asList(paths) + " not found.", nfe);
+ }
+ }
+
+ /**
+ * Get {@link PhoenixJson} for a given json paths. For example :
+ * <p>
+ * <code>
+ * {"f2":{"f3":1},"f4":{"f5":99,"f6":{"f7":"2"}}}'
+ * </code>
+ * <p>
+ * for this source json, if we want to know the json at path {'f4','f6'} it will return
+ * {@link PhoenixJson} object for json {"f7":"2"}. It always returns the last key if same key
+ * exist more than once.
+ * <p>
+ * If the given path is unreachable then it return null.
+ * @param paths {@link String []} of path in the same order as they appear in json.
+ * @return {@link PhoenixJson} for the json against @paths.
+ */
+ public PhoenixJson getPhoenixJsonOrNull(String[] paths) {
+ try {
+ return getPhoenixJsonInternal(paths);
+ } catch (NumberFormatException nfe) {
+ // ignore
+ }
+ return null;
+ }
+
+ /**
+ * Serialize the current {@link PhoenixJson} to String. Its required for
+ * json_extract_path_text(). If we just return node.toString() it will wrap String value in
+ * double quote which is not the expectation, hence avoiding calling toString() on
+ * {@link JsonNode} until PhoenixJson represent a Json Array or container for Json object. If
+ * PhoenixJson just represent a {@link ValueNode} then it should return value returned from
+ * objects toString().
+ */
+ public String serializeToString() {
+ if (this.rootNode == null || this.rootNode.isNull()) {
+ return null;
+ } else if (this.rootNode.isValueNode()) {
+
+ if (this.rootNode.isNumber()) {
+ return this.rootNode.getNumberValue().toString();
+ } else if (this.rootNode.isBoolean()) {
+ return String.valueOf(this.rootNode.getBooleanValue());
+ } else if (this.rootNode.isTextual()) {
+ return this.rootNode.getTextValue();
+ } else {
+ return this.jsonAsString;
+ }
+ } else if (this.rootNode.isArray()) {
+ return this.jsonAsString;
+ } else if (this.rootNode.isContainerNode()) {
+ return this.jsonAsString;
+ }
+
+ return null;
+
+ }
+
+ @Override
+ public String toString() {
+ return this.jsonAsString;
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + this.jsonAsString.hashCode();
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ /**
+ * @return length of the string represented by the current {@link PhoenixJson}.
+ */
+ public int estimateByteSize() {
+ String jsonStr = toString();
+ return jsonStr == null ? 1 : jsonStr.length();
+ }
+
+ public byte[] toBytes() {
+ return Bytes.toBytes(this.jsonAsString);
+ }
+
+ @Override
+ public int compareTo(PhoenixJson o) {
+ throw new EqualityNotSupportedException(PJson.INSTANCE);
+ }
+
+ private PhoenixJson getPhoenixJsonInternal(String[] paths) {
+ JsonNode node = this.rootNode;
+ for (String path : paths) {
+ JsonNode nodeTemp = null;
+ if (node.isArray()) {
+ int index = Integer.parseInt(path);
+ nodeTemp = node.path(index);
+ } else {
+ nodeTemp = node.path(path);
+ }
+ if (nodeTemp == null || nodeTemp.isMissingNode()) {
+ return null;
+ }
+ node = nodeTemp;
+ }
+ return new PhoenixJson(node, node.toString());
+ }
+
+ public int getJsonArrayLength() {
+ int count = 0;
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ elements.next();
+ count++;
+ }
+ return count;
+ }
+
+ public Object[] getJsonArrayElements() {
+ List<String> elementlist = new ArrayList();
+ Iterator<JsonNode> elements = this.rootNode.getElements();
+ while(elements.hasNext()){
+ JsonNode e = elements.next();
+ elementlist.add(e.toString());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonObjectKeys() {
+ List<String> elementlist = new ArrayList();
+ Iterator<String> fieldnames = this.rootNode.getFieldNames();
+ while(fieldnames.hasNext()){
+ elementlist.add(fieldnames.next());
+ }
+ return elementlist.toArray();
+ }
+
+ public Object[] getJsonFields() {
+ List<String> elementlist = new ArrayList();
+ Iterator<Map.Entry<String, JsonNode>> fields = this.rootNode.getFields();
--- End diff --
Nice !!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---