You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by vj...@apache.org on 2023/11/01 07:24:17 UTC
(phoenix) branch PHOENIX-628-feature updated: PHOENIX-7044 : Support Index on a generated column that extracts a scalar value from JSON column (#1708)
This is an automated email from the ASF dual-hosted git repository.
vjasani pushed a commit to branch PHOENIX-628-feature
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/PHOENIX-628-feature by this push:
new 739131082d PHOENIX-7044 : Support Index on a generated column that extracts a scalar value from JSON column (#1708)
739131082d is described below
commit 739131082d1b9f79d277949a1c2d9e823c0b2ce8
Author: RanganathG <ra...@gmail.com>
AuthorDate: Wed Nov 1 12:54:12 2023 +0530
PHOENIX-7044 : Support Index on a generated column that extracts a scalar value from JSON column (#1708)
---
.../phoenix/end2end/json/JsonFunctionsIT.java | 572 ++++++---------------
.../src/it/resources/json/json_datatypes.json | 36 ++
.../it/resources/json/json_functions_basic.json | 82 +++
.../it/resources/json/json_functions_tests.json | 371 +++++++++++++
.../apache/phoenix/compile/ExpressionCompiler.java | 14 +-
.../apache/phoenix/exception/SQLExceptionCode.java | 2 +
.../org/apache/phoenix/schema/MetaDataClient.java | 8 +-
7 files changed, 665 insertions(+), 420 deletions(-)
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
index 71d886632e..822097de32 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
@@ -22,424 +22,48 @@ import com.fasterxml.jackson.databind.ObjectMapper;
import com.jayway.jsonpath.Configuration;
import com.jayway.jsonpath.JsonPath;
import com.jayway.jsonpath.spi.json.GsonJsonProvider;
+import org.apache.commons.io.FileUtils;
import org.apache.hadoop.hbase.TableName;
+import org.apache.phoenix.end2end.IndexToolIT;
import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.schema.PTable;
+import org.apache.phoenix.schema.PTableKey;
+import org.apache.phoenix.schema.TableNotFoundException;
import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
import org.apache.phoenix.util.TestUtil;
+import org.junit.Before;
import org.junit.Test;
+import java.io.File;
+import java.io.IOException;
+import java.net.URL;
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 static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
import static org.junit.Assert.*;
public class JsonFunctionsIT extends ParallelStatsDisabledIT {
- private String JsonDoc1 = "{ \n" +
- " \"info\":{ \n" +
- " \"type\":1, \n" +
- " \"address\":{ \n" +
- " \"town\":\"Bristol\", \n" +
- " \"county\":\"Avon\", \n" +
- " \"country\":\"England\" \n" +
- " }, \n" +
- " \"tags\":[\"Sport\", \"Water polo\"] \n" +
- " }, \n" +
- " \"type\":\"Basic\", \n" +
- " \"name\":\"AndersenFamily\" \n" +
- " }";
+ public static String BASIC_JSON = "json/json_functions_basic.json";
+ public static String FUNCTIONS_TEST_JSON = "json/json_functions_tests.json";
+ public static String DATA_TYPES_JSON = "json/json_datatypes.json";
+ String basicJson = "";
+ String dataTypesJson = "";
+ String functionsJson = "";
- private String JsonDatatypes = "{\n" + " " +
- " \"datatypes\": {\n" + " " +
- " \"stringtype\": \"someString\",\n" +
- " \"inttype\": 1,\n" +
- " \"booltype\": true,\n" +
- " \"booltypef\": false,\n" +
- " \"doubletype\": 2.5, \n" +
- " \"longtype\": 1490020778457845, \n" +
- " \"intArray\": [1, 2, 3], \n" +
- " \"nullcheck\": null, \n"+
- " \"boolArray\": [true, false, false], \n" +
- " \"doubleArray\": [1.2,2.3,3.4], \n" +
- " \"stringArray\": [\"hello\",\"world\"], \n" +
- " \"mixedArray\": [2, \"string\", 1.2 , false] \n" +
- " }\n" +
- "}";
-
- private String JsonDoc2="{\n" +
- " \"testCnt\": \"SomeCnt1\", \n" +
- " \"test\": \"test1\",\n" +
- " \"batchNo\": 1,\n" +
- " \"infoTop\":[\n" +
- " {\n" +
- " \"_id\": \"618d982e407a8dbd65781450\",\n" +
- " \"index\": 0,\n" +
- " \"guid\": \"4f5a46f2-7271-492a-8347-a8223516715f\",\n" +
- " \"isActive\": true,\n" +
- " \"balance\": \"$3,746.11\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 20,\n" +
- " \"eyeColor\": \"green\",\n" +
- " \"name\": \"Castaneda Golden\",\n" +
- " \"gender\": \"male\",\n" +
- " \"company\": \"AUSTEX\",\n" +
- " \"email\": \"castanedagolden@austex.com\",\n" +
- " \"phone\": \"+1 (979) 486-3061\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Urbana\",\n" +
- " \"state\": \"Delaware\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"322 Hancock Street, Nicut, Georgia, 5007\",\n" +
- " \"about\": \"Esse anim minim nostrud aliquip. Quis anim ex dolore magna exercitation deserunt minim ad do est non. Magna fugiat eiusmod incididunt cupidatat. Anim occaecat nulla cillum culpa sunt amet.\\r\\n\",\n" +
- " \"registered\": \"2015-11-06T01:32:28 +08:00\",\n" +
- " \"latitude\": 83.51654,\n" +
- " \"longitude\": -93.749216,\n" +
- " \"tags\": [\n" +
- " \"incididunt\",\n" +
- " \"nostrud\",\n" +
- " \"incididunt\",\n" +
- " \"Lorem\",\n" +
- " \"mollit\",\n" +
- " \"tempor\",\n" +
- " \"incididunt\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Cortez Bowman\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Larsen Wolf\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Colon Rivers\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Castaneda Golden! You have 10 unread messages.\",\n" +
- " \"favoriteFruit\": \"banana\"\n" +
- " },\n" +
- " {\n" +
- " \"_id\": \"618d982ef091f4785f15251f\",\n" +
- " \"index\": 1,\n" +
- " \"guid\": \"bcfc487d-de23-4721-86bd-809d37a007c2\",\n" +
- " \"isActive\": false,\n" +
- " \"balance\": \"$1,539.97\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 31,\n" +
- " \"eyeColor\": \"brown\",\n" +
- " \"name\": \"Jackson Dillard\",\n" +
- " \"gender\": \"male\",\n" +
- " \"company\": \"QUONATA\",\n" +
- " \"email\": \"jacksondillard@quonata.com\",\n" +
- " \"phone\": \"+1 (950) 552-3553\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Cetronia\",\n" +
- " \"state\": \"Massachusetts\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"848 Hampton Avenue, Shasta, Marshall Islands, 6596\",\n" +
- " \"about\": \"Mollit nisi cillum sunt aliquip. Est ex nisi deserunt aliqua anim nisi dolor. Ullamco est consectetur deserunt do voluptate excepteur esse reprehenderit laboris officia. Deserunt sint velit mollit aliquip amet ad in tempor excepteur magna proident Lorem reprehenderit consequat.\\r\\n\",\n" +
- " \"registered\": \"2018-05-13T10:54:03 +07:00\",\n" +
- " \"latitude\": -68.213281,\n" +
- " \"longitude\": -147.388909,\n" +
- " \"tags\": [\n" +
- " \"adipisicing\",\n" +
- " \"Lorem\",\n" +
- " \"sit\",\n" +
- " \"voluptate\",\n" +
- " \"cupidatat\",\n" +
- " \"deserunt\",\n" +
- " \"consectetur\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Casandra Best\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Lauri Santiago\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Maricela Foster\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Jackson Dillard! You have 4 unread messages.\",\n" +
- " \"favoriteFruit\": \"strawberry\"\n" +
- " },\n" +
- " {\n" +
- " \"_id\": \"618d982eecb0f6158d7415b7\",\n" +
- " \"index\": 2,\n" +
- " \"guid\": \"09b31b54-6341-4a7e-8e58-bec0f766d5f4\",\n" +
- " \"isActive\": true,\n" +
- " \"balance\": \"$1,357.52\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 20,\n" +
- " \"eyeColor\": \"brown\",\n" +
- " \"name\": \"Battle Washington\",\n" +
- " \"gender\": \"male\",\n" +
- " \"company\": \"ONTALITY\",\n" +
- " \"email\": \"battlewashington@ontality.com\",\n" +
- " \"phone\": \"+1 (934) 429-3950\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Windsor\",\n" +
- " \"state\": \"Virginia\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"299 Campus Place, Innsbrook, Nevada, 4795\",\n" +
- " \"about\": \"Consequat voluptate nisi duis nostrud anim cupidatat officia dolore non velit Lorem. Pariatur sit consectetur do reprehenderit irure Lorem consectetur ad nostrud. Dolore tempor est fugiat officia ad nostrud. Cupidatat quis aute consectetur Lorem. Irure qui tempor deserunt nisi quis quis culpa veniam cillum est. Aute consequat pariatur ut minim sunt.\\r\\n\",\n" +
- " \"registered\": \"2018-12-07T03:42:53 +08:00\",\n" +
- " \"latitude\": -6.967753,\n" +
- " \"longitude\": 64.796997,\n" +
- " \"tags\": [\n" +
- " \"in\",\n" +
- " \"do\",\n" +
- " \"labore\",\n" +
- " \"laboris\",\n" +
- " \"dolore\",\n" +
- " \"est\",\n" +
- " \"nisi\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Faye Decker\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Judy Skinner\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Angie Faulkner\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Battle Washington! You have 2 unread messages.\",\n" +
- " \"favoriteFruit\": \"banana\"\n" +
- " },\n" +
- " {\n" +
- " \"_id\": \"618d982e1298ef388f75cda0\",\n" +
- " \"index\": 3,\n" +
- " \"guid\": \"deebe756-c9cd-43f5-9dd6-bc8d2edeab01\",\n" +
- " \"isActive\": false,\n" +
- " \"balance\": \"$3,684.61\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 27,\n" +
- " \"eyeColor\": \"brown\",\n" +
- " \"name\": \"Watkins Aguirre\",\n" +
- " \"gender\": \"male\",\n" +
- " \"company\": \"WAAB\",\n" +
- " \"email\": \"watkinsaguirre@waab.com\",\n" +
- " \"phone\": \"+1 (861) 526-2440\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Healy\",\n" +
- " \"state\": \"Nebraska\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"245 Bouck Court, Malo, Minnesota, 8990\",\n" +
- " \"about\": \"Elit fugiat aliquip occaecat nostrud deserunt eu in ut et officia pariatur ipsum non. Dolor exercitation irure cupidatat velit eiusmod voluptate esse enim. Minim aliquip do ut esse irure commodo duis aliquip deserunt ea enim incididunt. Consequat Lorem id duis occaecat proident mollit ad officia fugiat. Nostrud irure deserunt commodo consectetur cillum. Quis qui eiusmod ullamco exercitation amet do occaecat sint laboris ut laboris amet. El [...]
- " \"registered\": \"2021-05-27T03:15:12 +07:00\",\n" +
- " \"latitude\": 86.552038,\n" +
- " \"longitude\": 175.688809,\n" +
- " \"tags\": [\n" +
- " \"nostrud\",\n" +
- " \"et\",\n" +
- " \"ullamco\",\n" +
- " \"aliqua\",\n" +
- " \"minim\",\n" +
- " \"tempor\",\n" +
- " \"proident\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Dionne Lindsey\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Bonner Logan\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Neal Case\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Watkins Aguirre! You have 5 unread messages.\",\n" +
- " \"favoriteFruit\": \"strawberry\"\n" +
- " },\n" +
- " {\n" +
- " \"_id\": \"618d982e3cb0317d825dfbb5\",\n" +
- " \"index\": 4,\n" +
- " \"guid\": \"ac778765-da9a-4923-915b-1b967e1bee96\",\n" +
- " \"isActive\": true,\n" +
- " \"balance\": \"$2,787.54\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 34,\n" +
- " \"eyeColor\": \"green\",\n" +
- " \"name\": \"Barbra Fry\",\n" +
- " \"gender\": \"female\",\n" +
- " \"company\": \"SPACEWAX\",\n" +
- " \"email\": \"barbrafry@spacewax.com\",\n" +
- " \"phone\": \"+1 (895) 538-2479\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Movico\",\n" +
- " \"state\": \"Pennsylvania\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"812 Losee Terrace, Elbert, South Dakota, 9870\",\n" +
- " \"about\": \"Ea Lorem nisi aliqua incididunt deserunt sint. Cillum do magna sint quis enim velit cupidatat deserunt pariatur esse labore. Laborum velit nostrud in occaecat amet commodo enim ex commodo. Culpa do est sit reprehenderit nulla duis ex irure reprehenderit velit aliquip. Irure et eiusmod ad minim laborum ut fugiat dolore in anim mollit aliquip aliqua sunt. Commodo Lorem anim magna eiusmod.\\r\\n\",\n" +
- " \"registered\": \"2020-05-05T05:27:59 +07:00\",\n" +
- " \"latitude\": -55.592888,\n" +
- " \"longitude\": 68.056625,\n" +
- " \"tags\": [\n" +
- " \"magna\",\n" +
- " \"sint\",\n" +
- " \"minim\",\n" +
- " \"dolore\",\n" +
- " \"ad\",\n" +
- " \"exercitation\",\n" +
- " \"laborum\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Mccullough Roman\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Lang Morales\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Luann Carrillo\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Barbra Fry! You have 6 unread messages.\",\n" +
- " \"favoriteFruit\": \"banana\"\n" +
- " },\n" +
- " {\n" +
- " \"_id\": \"618d982e44e4e11611e5f62a\",\n" +
- " \"index\": 5,\n" +
- " \"guid\": \"d02e17de-fed9-4839-8d75-e8d05fe68c94\",\n" +
- " \"isActive\": true,\n" +
- " \"balance\": \"$1,023.39\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 38,\n" +
- " \"eyeColor\": \"green\",\n" +
- " \"name\": \"Byers Grant\",\n" +
- " \"gender\": \"male\",\n" +
- " \"company\": \"ZAGGLES\",\n" +
- " \"email\": \"byersgrant@zaggles.com\",\n" +
- " \"phone\": \"+1 (992) 570-3190\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Chamberino\",\n" +
- " \"state\": \"North Dakota\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"826 Cumberland Street, Shaft, Washington, 424\",\n" +
- " \"about\": \"Deserunt tempor sint culpa in ex occaecat quis exercitation voluptate mollit occaecat officia. Aute aliquip officia id cupidatat non consectetur nulla mollit laborum ex mollit culpa exercitation. Aute nisi ullamco adipisicing sit proident proident duis. Exercitation ex id id enim cupidatat pariatur amet reprehenderit fugiat ea.\\r\\n\",\n" +
- " \"registered\": \"2017-10-12T04:55:42 +07:00\",\n" +
- " \"latitude\": -26.03892,\n" +
- " \"longitude\": -35.959528,\n" +
- " \"tags\": [\n" +
- " \"et\",\n" +
- " \"adipisicing\",\n" +
- " \"excepteur\",\n" +
- " \"do\",\n" +
- " \"ad\",\n" +
- " \"exercitation\",\n" +
- " \"commodo\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Louise Clarke\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Pratt Velazquez\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Violet Reyes\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Byers Grant! You have 8 unread messages.\",\n" +
- " \"favoriteFruit\": \"banana\"\n" +
- " },\n" +
- " {\n" +
- " \"_id\": \"618d982ef6ed0ffe65e0f414\",\n" +
- " \"index\": 6,\n" +
- " \"guid\": \"37f92715-a4d1-476e-98d9-b4901426c5ea\",\n" +
- " \"isActive\": true,\n" +
- " \"balance\": \"$2,191.12\",\n" +
- " \"picture\": \"http://placehold.it/32x32\",\n" +
- " \"age\": 33,\n" +
- " \"eyeColor\": \"brown\",\n" +
- " \"name\": \"Rasmussen Todd\",\n" +
- " \"gender\": \"male\",\n" +
- " \"company\": \"ROUGHIES\",\n" +
- " \"email\": \"rasmussentodd@roughies.com\",\n" +
- " \"phone\": \"+1 (893) 420-3792\",\n" +
- " \"info\": {\n" +
- " \"address\": {\n" +
- " \"street\": \"function\",\n" +
- " \"town\": \"Floriston\",\n" +
- " \"state\": \"Indiana\"\n" +
- " }\n" +
- " },\n" +
- " \"address\": \"295 McClancy Place, Berlin, Federated States Of Micronesia, 303\",\n" +
- " \"about\": \"Est cillum fugiat reprehenderit minim minim esse qui. Eiusmod quis pariatur adipisicing sunt ipsum duis dolor veniam. Aliqua ex cupidatat officia exercitation sint duis exercitation ut. Cillum magna laboris id Lorem mollit consequat ex anim voluptate Lorem enim et velit nulla. Non consectetur incididunt id et ad tempor amet elit tempor aliquip velit incididunt esse adipisicing. Culpa pariatur est occaecat voluptate. Voluptate pariatur pari [...]
- " \"registered\": \"2015-10-10T12:39:42 +07:00\",\n" +
- " \"latitude\": -20.559815,\n" +
- " \"longitude\": 28.453852,\n" +
- " \"tags\": [\n" +
- " \"reprehenderit\",\n" +
- " \"velit\",\n" +
- " \"non\",\n" +
- " \"non\",\n" +
- " \"veniam\",\n" +
- " \"laborum\",\n" +
- " \"duis\"\n" +
- " ],\n" +
- " \"friends\": [\n" +
- " {\n" +
- " \"id\": 0,\n" +
- " \"name\": \"Stark Carney\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 1,\n" +
- " \"name\": \"Price Roberts\"\n" +
- " },\n" +
- " {\n" +
- " \"id\": 2,\n" +
- " \"name\": \"Lillian Henry\"\n" +
- " }\n" +
- " ],\n" +
- " \"greeting\": \"Hello, Rasmussen Todd! You have 3 unread messages.\",\n" +
- " \"favoriteFruit\": \"banana\"\n" +
- " }\n" +
- " ]\n" +
- "}";
+ @Before
+ public void setup() throws IOException {
+ basicJson = getJsonString(BASIC_JSON, "$[0]");
+ dataTypesJson = getJsonString(DATA_TYPES_JSON);
+ functionsJson = getJsonString(FUNCTIONS_TEST_JSON);
+ }
@Test
public void testSimpleJsonValue() throws Exception {
@@ -451,7 +75,7 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?,?,?)");
stmt.setInt(1, 1);
stmt.setInt(2, 2);
- stmt.setString(3, JsonDoc1);
+ stmt.setString(3, basicJson);
stmt.execute();
conn.commit();
TestUtil.dumpTable(conn, TableName.valueOf(tableName));
@@ -467,8 +91,8 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
assertEquals("Bristol", rs.getString(2));
assertEquals("Water polo", rs.getString(3));
// returned format is different
- compareJson(rs.getString(4), JsonDoc1, "$.info.tags");
- compareJson(rs.getString(5), JsonDoc1, "$.info");
+ compareJson(rs.getString(4), basicJson, "$.info.tags");
+ compareJson(rs.getString(5), basicJson, "$.info");
assertFalse(rs.next());
// Now check for empty match
@@ -489,7 +113,7 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?,?,?)");
stmt.setInt(1, 1);
stmt.setInt(2, 2);
- stmt.setString(3, JsonDoc1);
+ stmt.setString(3, basicJson);
stmt.execute();
conn.commit();
@@ -532,7 +156,7 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?,?,?)");
stmt.setInt(1, 1);
stmt.setInt(2, 2);
- stmt.setString(3, JsonDoc2);
+ stmt.setString(3, functionsJson);
stmt.execute();
conn.commit();
ResultSet rs = conn.createStatement().executeQuery("SELECT JSON_VALUE(JSONCOL,'$.test'), " +
@@ -548,9 +172,9 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
assertEquals("SomeCnt1", rs.getString(2));
assertEquals("North Dakota", rs.getString(3));
assertEquals("sint", rs.getString(4));
- compareJson(rs.getString(5), JsonDoc2, "$.infoTop");
- compareJson(rs.getString(6), JsonDoc2, "$.infoTop[5].info");
- compareJson(rs.getString(7), JsonDoc2, "$.infoTop[5].friends");
+ compareJson(rs.getString(5), functionsJson, "$.infoTop");
+ compareJson(rs.getString(6), functionsJson, "$.infoTop[5].info");
+ compareJson(rs.getString(7), functionsJson, "$.infoTop[5].friends");
}
}
@@ -572,7 +196,7 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?,?,?)");
stmt.setInt(1, 1);
stmt.setInt(2, 2);
- stmt.setString(3, JsonDatatypes);
+ stmt.setString(3, dataTypesJson);
stmt.execute();
conn.commit();
ResultSet rs = conn.createStatement().executeQuery("SELECT JSON_VALUE(JSONCOL,'$.datatypes.stringtype'), " +
@@ -615,7 +239,7 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?,?,?)");
stmt.setInt(1, 1);
stmt.setInt(2, 2);
- stmt.setString(3, JsonDatatypes);
+ stmt.setString(3, dataTypesJson);
stmt.execute();
conn.commit();
ResultSet rs = conn.createStatement().executeQuery("SELECT " +
@@ -626,11 +250,127 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
"JSON_QUERY(JSONCOL, '$.datatypes.mixedArray') FROM "
+ tableName + " WHERE JSON_VALUE(JSONCOL, '$.datatypes.stringtype')='someString'");
assertTrue(rs.next());
- compareJson(rs.getString(1), JsonDatatypes, "$.datatypes.intArray");
- compareJson(rs.getString(2), JsonDatatypes, "$.datatypes.boolArray");
- compareJson(rs.getString(3), JsonDatatypes, "$.datatypes.doubleArray");
- compareJson(rs.getString(4), JsonDatatypes, "$.datatypes.stringArray");
- compareJson(rs.getString(5), JsonDatatypes, "$.datatypes.mixedArray");
+ compareJson(rs.getString(1), dataTypesJson, "$.datatypes.intArray");
+ compareJson(rs.getString(2), dataTypesJson, "$.datatypes.boolArray");
+ compareJson(rs.getString(3), dataTypesJson, "$.datatypes.doubleArray");
+ compareJson(rs.getString(4), dataTypesJson, "$.datatypes.stringArray");
+ compareJson(rs.getString(5), dataTypesJson, "$.datatypes.mixedArray");
+ }
+ }
+
+ @Test
+ public void testJsonExpressionIndex() throws IOException {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String
+ ddl =
+ "create table if not exists " + tableName +
+ " (pk integer primary key, col integer, jsoncol.jsoncol json)";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES (1,2, '" + basicJson + "')");
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES (2,3, '" + getJsonString(BASIC_JSON, "$[1]") + "')");
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES (3,4, '" + getJsonString(BASIC_JSON, "$[2]") + "')");
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES (4,5, '" + getJsonString(BASIC_JSON, "$[3]") + "')");
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES (5,6, '" + getJsonString(BASIC_JSON, "$[4]") + "')");
+ conn.createStatement().execute(
+ "CREATE INDEX " + indexName + " ON " + tableName
+ + " (JSON_VALUE(JSONCOL,'$.type'), JSON_VALUE(JSONCOL,'$.info.address.town')) include (col)");
+ String
+ selectSql =
+ "SELECT JSON_VALUE(JSONCOL,'$.type'), " +
+ "JSON_VALUE(JSONCOL,'$.info.address.town') FROM " + tableName +
+ " WHERE JSON_VALUE(JSONCOL,'$.type') = 'Basic'";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + selectSql);
+ String actualExplainPlan = QueryUtil.getExplainPlan(rs);
+ IndexToolIT.assertExplainPlan(false, actualExplainPlan, tableName, indexName);
+ // Validate the total count of rows
+ String countSql = "SELECT COUNT(1) FROM " + tableName;
+ rs = conn.createStatement().executeQuery( countSql);
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+ // Delete the rows
+ String deleteQuery = "DELETE FROM " + tableName + " WHERE JSON_VALUE(JSONCOL,'$.type') = 'Normal'";
+ boolean deleted = conn.createStatement().execute(deleteQuery);
+ rs = conn.createStatement().executeQuery( countSql);
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ // Do a count now for the deleted rows, the count should be 0
+ selectSql = "SELECT COUNT(1) FROM " + tableName +
+ " WHERE JSON_VALUE(JSONCOL,'$.type') = 'Normal'";
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(0, rs.getInt(1));
+ // Drop the JSON column
+ conn.createStatement().execute("ALTER TABLE " + tableName + " DROP COLUMN jsoncol ");
+
+ // verify the both of the indexes' metadata were dropped
+ conn.createStatement().execute("SELECT * FROM " + tableName);
+ try {
+ conn.createStatement().execute("SELECT * FROM " + indexName);
+ fail("Index should have been dropped");
+ } catch (TableNotFoundException e) {
+ }
+ PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class);
+ PTable dataTable = pconn.getTable(new PTableKey(null, tableName));
+ pconn = conn.unwrap(PhoenixConnection.class);
+ dataTable = pconn.getTable(new PTableKey(null, tableName));
+ try {
+ pconn.getTable(new PTableKey(null, indexName));
+ fail("index should have been dropped");
+ } catch (TableNotFoundException e) {
+ }
+ assertEquals("Unexpected number of indexes ", 0, dataTable.getIndexes().size());
+ } catch (SQLException e) {
+ assertFalse("Failed to execute test", true);
}
}
+
+ @Test
+ public void testJsonExpressionIndexInvalid() {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+ checkInvalidJsonIndexExpression(props, tableName, indexName,
+ " (JSON_QUERY(JSONCOL,'$.info.address')) include (col)");
+ checkInvalidJsonIndexExpression(props, tableName, indexName,
+ " (JSON_MODIFY(jsoncol, '$.info.tags[2]', '\"newValue\"')) include (col)");
+ }
+
+ private void checkInvalidJsonIndexExpression(Properties props, String tableName,
+ String indexName, String indexExpression) {
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String
+ ddl =
+ "create table if not exists " + tableName + " (pk integer primary key, col integer, jsoncol.jsoncol json)";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES (1,2, '" + basicJson + "')");
+ conn.createStatement()
+ .execute("CREATE INDEX " + indexName + " ON " + tableName + indexExpression);
+ } catch (SQLException e) {
+ assertEquals(
+ SQLExceptionCode.JSON_FRAGMENT_NOT_ALLOWED_IN_INDEX_EXPRESSION.getErrorCode(),
+ e.getErrorCode());
+ }
+ }
+
+ private static String getJsonString(String jsonFilePath) throws IOException {
+ return getJsonString(jsonFilePath, "$");
+ }
+ private static String getJsonString(String jsonFilePath, String jsonPath) throws IOException {
+ URL fileUrl = JsonFunctionsIT.class.getClassLoader().getResource(jsonFilePath);
+ String json = FileUtils.readFileToString(new File(fileUrl.getFile()));
+ Configuration conf = Configuration.builder().jsonProvider(new GsonJsonProvider()).build();
+ Object read = JsonPath.using(conf).parse(json).read(jsonPath);
+ return read.toString();
+ }
}
diff --git a/phoenix-core/src/it/resources/json/json_datatypes.json b/phoenix-core/src/it/resources/json/json_datatypes.json
new file mode 100644
index 0000000000..7cebac0ab6
--- /dev/null
+++ b/phoenix-core/src/it/resources/json/json_datatypes.json
@@ -0,0 +1,36 @@
+{
+ "datatypes": {
+ "stringtype": "someString",
+ "inttype": 1,
+ "booltype": true,
+ "booltypef": false,
+ "doubletype": 2.5,
+ "longtype": 1490020778457845,
+ "intArray": [
+ 1,
+ 2,
+ 3
+ ],
+ "nullcheck": null,
+ "boolArray": [
+ true,
+ false,
+ false
+ ],
+ "doubleArray": [
+ 1.2,
+ 2.3,
+ 3.4
+ ],
+ "stringArray": [
+ "hello",
+ "world"
+ ],
+ "mixedArray": [
+ 2,
+ "string",
+ 1.2,
+ false
+ ]
+ }
+}
\ No newline at end of file
diff --git a/phoenix-core/src/it/resources/json/json_functions_basic.json b/phoenix-core/src/it/resources/json/json_functions_basic.json
new file mode 100644
index 0000000000..fc2050c823
--- /dev/null
+++ b/phoenix-core/src/it/resources/json/json_functions_basic.json
@@ -0,0 +1,82 @@
+[
+ {
+ "info": {
+ "type": 1,
+ "address": {
+ "town": "Bristol",
+ "county": "Avon",
+ "country": "England"
+ },
+ "tags": [
+ "Sport",
+ "Water polo"
+ ]
+ },
+ "type": "Basic",
+ "name": "AndersenFamily"
+},
+ {
+ "info": {
+ "type": 2,
+ "address": {
+ "town": "Bristol2",
+ "county": "Avon2",
+ "country": "France"
+ },
+ "tags": [
+ "Cricket",
+ "Foot Ball"
+ ]
+ },
+ "type": "Normal",
+ "name": "SomeFamily"
+ },
+ {
+ "info": {
+ "type": 3,
+ "address": {
+ "town": "Bristol3",
+ "county": "Avon3",
+ "country": "Australia"
+ },
+ "tags": [
+ "Rugby",
+ "Ice Hockey"
+ ]
+ },
+ "type": "Advanced",
+ "name": "AdvancedFamily"
+ },
+ {
+ "info": {
+ "type": 4,
+ "address": {
+ "town": "Bristol4",
+ "county": "Avon4",
+ "country": "Antarctica"
+ },
+ "tags": [
+ "HorseRide",
+ "Dolphine"
+ ]
+ },
+ "type": "Advanced",
+ "name": "AntarcticaFamily"
+ },
+ {
+ "info": {
+ "type": 5,
+ "address": {
+ "town": "Bristol5",
+ "county": "Avon5",
+ "country": "Europe"
+ },
+ "tags": [
+ "Jumping",
+ "Hopping"
+ ]
+ },
+ "type": "Normal",
+ "name": "EuropeFamily"
+ }
+]
\ No newline at end of file
diff --git a/phoenix-core/src/it/resources/json/json_functions_tests.json b/phoenix-core/src/it/resources/json/json_functions_tests.json
new file mode 100644
index 0000000000..27df7d42c7
--- /dev/null
+++ b/phoenix-core/src/it/resources/json/json_functions_tests.json
@@ -0,0 +1,371 @@
+{
+ "testCnt": "SomeCnt1",
+ "test": "test1",
+ "batchNo": 1,
+ "infoTop": [
+ {
+ "_id": "618d982e407a8dbd65781450",
+ "index": 0,
+ "guid": "4f5a46f2-7271-492a-8347-a8223516715f",
+ "isActive": true,
+ "balance": "$3,746.11",
+ "picture": "http://placehold.it/32x32",
+ "age": 20,
+ "eyeColor": "green",
+ "name": "Castaneda Golden",
+ "gender": "male",
+ "company": "AUSTEX",
+ "email": "castanedagolden@austex.com",
+ "phone": "+1 (979) 486-3061",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Urbana",
+ "state": "Delaware"
+ }
+ },
+ "address": "322 Hancock Street, Nicut, Georgia, 5007",
+ "about": "Esse anim minim nostrud aliquip. Quis anim ex dolore magna exercitation deserunt minim ad do est non. Magna fugiat eiusmod incididunt cupidatat. Anim occaecat nulla cillum culpa sunt amet.\\r\\n",
+ "registered": "2015-11-06T01:32:28 +08:00",
+ "latitude": 83.51654,
+ "longitude": -93.749216,
+ "tags": [
+ "incididunt",
+ "nostrud",
+ "incididunt",
+ "Lorem",
+ "mollit",
+ "tempor",
+ "incididunt"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Cortez Bowman"
+ },
+ {
+ "id": 1,
+ "name": "Larsen Wolf"
+ },
+ {
+ "id": 2,
+ "name": "Colon Rivers"
+ }
+ ],
+ "greeting": "Hello, Castaneda Golden! You have 10 unread messages.",
+ "favoriteFruit": "banana"
+ },
+ {
+ "_id": "618d982ef091f4785f15251f",
+ "index": 1,
+ "guid": "bcfc487d-de23-4721-86bd-809d37a007c2",
+ "isActive": false,
+ "balance": "$1,539.97",
+ "picture": "http://placehold.it/32x32",
+ "age": 31,
+ "eyeColor": "brown",
+ "name": "Jackson Dillard",
+ "gender": "male",
+ "company": "QUONATA",
+ "email": "jacksondillard@quonata.com",
+ "phone": "+1 (950) 552-3553",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Cetronia",
+ "state": "Massachusetts"
+ }
+ },
+ "address": "848 Hampton Avenue, Shasta, Marshall Islands, 6596",
+ "about": "Mollit nisi cillum sunt aliquip. Est ex nisi deserunt aliqua anim nisi dolor. Ullamco est consectetur deserunt do voluptate excepteur esse reprehenderit laboris officia. Deserunt sint velit mollit aliquip amet ad in tempor excepteur magna proident Lorem reprehenderit consequat.\\r\\n",
+ "registered": "2018-05-13T10:54:03 +07:00",
+ "latitude": -68.213281,
+ "longitude": -147.388909,
+ "tags": [
+ "adipisicing",
+ "Lorem",
+ "sit",
+ "voluptate",
+ "cupidatat",
+ "deserunt",
+ "consectetur"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Casandra Best"
+ },
+ {
+ "id": 1,
+ "name": "Lauri Santiago"
+ },
+ {
+ "id": 2,
+ "name": "Maricela Foster"
+ }
+ ],
+ "greeting": "Hello, Jackson Dillard! You have 4 unread messages.",
+ "favoriteFruit": "strawberry"
+ },
+ {
+ "_id": "618d982eecb0f6158d7415b7",
+ "index": 2,
+ "guid": "09b31b54-6341-4a7e-8e58-bec0f766d5f4",
+ "isActive": true,
+ "balance": "$1,357.52",
+ "picture": "http://placehold.it/32x32",
+ "age": 20,
+ "eyeColor": "brown",
+ "name": "Battle Washington",
+ "gender": "male",
+ "company": "ONTALITY",
+ "email": "battlewashington@ontality.com",
+ "phone": "+1 (934) 429-3950",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Windsor",
+ "state": "Virginia"
+ }
+ },
+ "address": "299 Campus Place, Innsbrook, Nevada, 4795",
+ "about": "Consequat voluptate nisi duis nostrud anim cupidatat officia dolore non velit Lorem. Pariatur sit consectetur do reprehenderit irure Lorem consectetur ad nostrud. Dolore tempor est fugiat officia ad nostrud. Cupidatat quis aute consectetur Lorem. Irure qui tempor deserunt nisi quis quis culpa veniam cillum est. Aute consequat pariatur ut minim sunt.\\r\\n",
+ "registered": "2018-12-07T03:42:53 +08:00",
+ "latitude": -6.967753,
+ "longitude": 64.796997,
+ "tags": [
+ "in",
+ "do",
+ "labore",
+ "laboris",
+ "dolore",
+ "est",
+ "nisi"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Faye Decker"
+ },
+ {
+ "id": 1,
+ "name": "Judy Skinner"
+ },
+ {
+ "id": 2,
+ "name": "Angie Faulkner"
+ }
+ ],
+ "greeting": "Hello, Battle Washington! You have 2 unread messages.",
+ "favoriteFruit": "banana"
+ },
+ {
+ "_id": "618d982e1298ef388f75cda0",
+ "index": 3,
+ "guid": "deebe756-c9cd-43f5-9dd6-bc8d2edeab01",
+ "isActive": false,
+ "balance": "$3,684.61",
+ "picture": "http://placehold.it/32x32",
+ "age": 27,
+ "eyeColor": "brown",
+ "name": "Watkins Aguirre",
+ "gender": "male",
+ "company": "WAAB",
+ "email": "watkinsaguirre@waab.com",
+ "phone": "+1 (861) 526-2440",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Healy",
+ "state": "Nebraska"
+ }
+ },
+ "address": "245 Bouck Court, Malo, Minnesota, 8990",
+ "about": "Elit fugiat aliquip occaecat nostrud deserunt eu in ut et officia pariatur ipsum non. Dolor exercitation irure cupidatat velit eiusmod voluptate esse enim. Minim aliquip do ut esse irure commodo duis aliquip deserunt ea enim incididunt. Consequat Lorem id duis occaecat proident mollit ad officia fugiat. Nostrud irure deserunt commodo consectetur cillum. Quis qui eiusmod ullamco exercitation amet do occaecat sint laboris ut laboris amet. Elit consequat fugiat cupidatat eni [...]
+ "registered": "2021-05-27T03:15:12 +07:00",
+ "latitude": 86.552038,
+ "longitude": 175.688809,
+ "tags": [
+ "nostrud",
+ "et",
+ "ullamco",
+ "aliqua",
+ "minim",
+ "tempor",
+ "proident"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Dionne Lindsey"
+ },
+ {
+ "id": 1,
+ "name": "Bonner Logan"
+ },
+ {
+ "id": 2,
+ "name": "Neal Case"
+ }
+ ],
+ "greeting": "Hello, Watkins Aguirre! You have 5 unread messages.",
+ "favoriteFruit": "strawberry"
+ },
+ {
+ "_id": "618d982e3cb0317d825dfbb5",
+ "index": 4,
+ "guid": "ac778765-da9a-4923-915b-1b967e1bee96",
+ "isActive": true,
+ "balance": "$2,787.54",
+ "picture": "http://placehold.it/32x32",
+ "age": 34,
+ "eyeColor": "green",
+ "name": "Barbra Fry",
+ "gender": "female",
+ "company": "SPACEWAX",
+ "email": "barbrafry@spacewax.com",
+ "phone": "+1 (895) 538-2479",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Movico",
+ "state": "Pennsylvania"
+ }
+ },
+ "address": "812 Losee Terrace, Elbert, South Dakota, 9870",
+ "about": "Ea Lorem nisi aliqua incididunt deserunt sint. Cillum do magna sint quis enim velit cupidatat deserunt pariatur esse labore. Laborum velit nostrud in occaecat amet commodo enim ex commodo. Culpa do est sit reprehenderit nulla duis ex irure reprehenderit velit aliquip. Irure et eiusmod ad minim laborum ut fugiat dolore in anim mollit aliquip aliqua sunt. Commodo Lorem anim magna eiusmod.\\r\\n",
+ "registered": "2020-05-05T05:27:59 +07:00",
+ "latitude": -55.592888,
+ "longitude": 68.056625,
+ "tags": [
+ "magna",
+ "sint",
+ "minim",
+ "dolore",
+ "ad",
+ "exercitation",
+ "laborum"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Mccullough Roman"
+ },
+ {
+ "id": 1,
+ "name": "Lang Morales"
+ },
+ {
+ "id": 2,
+ "name": "Luann Carrillo"
+ }
+ ],
+ "greeting": "Hello, Barbra Fry! You have 6 unread messages.",
+ "favoriteFruit": "banana"
+ },
+ {
+ "_id": "618d982e44e4e11611e5f62a",
+ "index": 5,
+ "guid": "d02e17de-fed9-4839-8d75-e8d05fe68c94",
+ "isActive": true,
+ "balance": "$1,023.39",
+ "picture": "http://placehold.it/32x32",
+ "age": 38,
+ "eyeColor": "green",
+ "name": "Byers Grant",
+ "gender": "male",
+ "company": "ZAGGLES",
+ "email": "byersgrant@zaggles.com",
+ "phone": "+1 (992) 570-3190",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Chamberino",
+ "state": "North Dakota"
+ }
+ },
+ "address": "826 Cumberland Street, Shaft, Washington, 424",
+ "about": "Deserunt tempor sint culpa in ex occaecat quis exercitation voluptate mollit occaecat officia. Aute aliquip officia id cupidatat non consectetur nulla mollit laborum ex mollit culpa exercitation. Aute nisi ullamco adipisicing sit proident proident duis. Exercitation ex id id enim cupidatat pariatur amet reprehenderit fugiat ea.\\r\\n",
+ "registered": "2017-10-12T04:55:42 +07:00",
+ "latitude": -26.03892,
+ "longitude": -35.959528,
+ "tags": [
+ "et",
+ "adipisicing",
+ "excepteur",
+ "do",
+ "ad",
+ "exercitation",
+ "commodo"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Louise Clarke"
+ },
+ {
+ "id": 1,
+ "name": "Pratt Velazquez"
+ },
+ {
+ "id": 2,
+ "name": "Violet Reyes"
+ }
+ ],
+ "greeting": "Hello, Byers Grant! You have 8 unread messages.",
+ "favoriteFruit": "banana"
+ },
+ {
+ "_id": "618d982ef6ed0ffe65e0f414",
+ "index": 6,
+ "guid": "37f92715-a4d1-476e-98d9-b4901426c5ea",
+ "isActive": true,
+ "balance": "$2,191.12",
+ "picture": "http://placehold.it/32x32",
+ "age": 33,
+ "eyeColor": "brown",
+ "name": "Rasmussen Todd",
+ "gender": "male",
+ "company": "ROUGHIES",
+ "email": "rasmussentodd@roughies.com",
+ "phone": "+1 (893) 420-3792",
+ "info": {
+ "address": {
+ "street": "function",
+ "town": "Floriston",
+ "state": "Indiana"
+ }
+ },
+ "address": "295 McClancy Place, Berlin, Federated States Of Micronesia, 303",
+ "about": "Est cillum fugiat reprehenderit minim minim esse qui. Eiusmod quis pariatur adipisicing sunt ipsum duis dolor veniam. Aliqua ex cupidatat officia exercitation sint duis exercitation ut. Cillum magna laboris id Lorem mollit consequat ex anim voluptate Lorem enim et velit nulla. Non consectetur incididunt id et ad tempor amet elit tempor aliquip velit incididunt esse adipisicing. Culpa pariatur est occaecat voluptate. Voluptate pariatur pariatur esse cillum proident eiusmod [...]
+ "registered": "2015-10-10T12:39:42 +07:00",
+ "latitude": -20.559815,
+ "longitude": 28.453852,
+ "tags": [
+ "reprehenderit",
+ "velit",
+ "non",
+ "non",
+ "veniam",
+ "laborum",
+ "duis"
+ ],
+ "friends": [
+ {
+ "id": 0,
+ "name": "Stark Carney"
+ },
+ {
+ "id": 1,
+ "name": "Price Roberts"
+ },
+ {
+ "id": 2,
+ "name": "Lillian Henry"
+ }
+ ],
+ "greeting": "Hello, Rasmussen Todd! You have 3 unread messages.",
+ "favoriteFruit": "banana"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
index 13d78a2fb9..cff3d964f5 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
@@ -89,6 +89,8 @@ import org.apache.phoenix.parse.FunctionParseNode;
import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunctionInfo;
import org.apache.phoenix.parse.InListParseNode;
import org.apache.phoenix.parse.IsNullParseNode;
+import org.apache.phoenix.parse.JsonModifyParseNode;
+import org.apache.phoenix.parse.JsonQueryParseNode;
import org.apache.phoenix.parse.LikeParseNode;
import org.apache.phoenix.parse.LikeParseNode.LikeType;
import org.apache.phoenix.parse.LiteralParseNode;
@@ -141,6 +143,7 @@ import org.apache.phoenix.util.StringUtil;
public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expression> {
private boolean isAggregate;
+ private boolean isJsonFragment;
protected ParseNode aggregateFunction;
protected final StatementContext context;
protected final GroupBy groupBy;
@@ -171,6 +174,10 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
return isAggregate;
}
+ public boolean isJsonFragment() {
+ return isJsonFragment;
+ }
+
public boolean isTopLevel() {
return nodeCount == 0;
}
@@ -179,6 +186,7 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
this.isAggregate = false;
this.nodeCount = 0;
this.totalNodeCount = 0;
+ this.isJsonFragment = false;
}
@Override
@@ -289,10 +297,14 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
@Override
public boolean visitEnter(FunctionParseNode node) throws SQLException {
+ if (node instanceof JsonQueryParseNode || node instanceof JsonModifyParseNode) {
+ this.isJsonFragment = true;
+ }
// TODO: Oracle supports nested aggregate function while other DBs don't. Should we?
if (node.isAggregate()) {
if (aggregateFunction != null) {
- throw new SQLFeatureNotSupportedException("Nested aggregate functions are not supported");
+ throw new SQLFeatureNotSupportedException(
+ "Nested aggregate functions are not supported");
}
this.aggregateFunction = node;
this.isAggregate = true;
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index a050ac271a..d2e732290a 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -199,6 +199,8 @@ public enum SQLExceptionCode {
COMPARISON_UNSUPPORTED(539, "42915", "Comparison not supported for the datatype."),
INVALID_JSON_DATA(540, "42916", "Invalid json data."),
+ JSON_FRAGMENT_NOT_ALLOWED_IN_INDEX_EXPRESSION(541, "42917",
+ "Functions returning JSON fragments are not allowed in Index Expression."),
/**
* HBase and Phoenix specific implementation defined sub-classes.
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java b/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
index 4fbee3cc24..5a2f017177 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/MetaDataClient.java
@@ -44,7 +44,6 @@ import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_DEF;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_ENCODED_BYTES;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_FAMILY;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_NAME;
-import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_QUALIFIER;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_QUALIFIER_COUNTER;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.COLUMN_SIZE;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.DATA_TABLE_NAME;
@@ -64,7 +63,6 @@ import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.INDEX_TYPE;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.IS_ARRAY;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.IS_CONSTANT;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.IS_NAMESPACE_MAPPED;
-import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.IS_ROW_TIMESTAMP;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.IS_VIEW_REFERENCED;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.JAR_PATH;
import static org.apache.phoenix.jdbc.PhoenixDatabaseMetaData.KEY_SEQ;
@@ -121,7 +119,6 @@ import static org.apache.phoenix.schema.PTable.ImmutableStorageScheme.ONE_CELL_P
import static org.apache.phoenix.schema.PTable.ImmutableStorageScheme.SINGLE_CELL_ARRAY_WITH_OFFSETS;
import static org.apache.phoenix.schema.PTable.QualifierEncodingScheme.NON_ENCODED_QUALIFIERS;
import static org.apache.phoenix.schema.PTable.ViewType.MAPPED;
-import static org.apache.phoenix.schema.PTableImpl.getColumnsToClone;
import static org.apache.phoenix.schema.PTableType.INDEX;
import static org.apache.phoenix.schema.PTableType.TABLE;
import static org.apache.phoenix.schema.PTableType.VIEW;
@@ -1529,6 +1526,11 @@ public class MetaDataClient {
if (expressionIndexCompiler.isAggregate()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.AGGREGATE_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException();
}
+ if (expressionIndexCompiler.isJsonFragment()) {
+ throw new SQLExceptionInfo.Builder(
+ SQLExceptionCode.JSON_FRAGMENT_NOT_ALLOWED_IN_INDEX_EXPRESSION).build()
+ .buildException();
+ }
if (!(expression.getDeterminism() == Determinism.ALWAYS || expression.getDeterminism() == Determinism.PER_ROW)) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.NON_DETERMINISTIC_EXPRESSION_NOT_ALLOWED_IN_INDEX).build().buildException();
}