[
https://issues.apache.org/jira/browse/PHOENIX-7099?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17783813#comment-17783813
]
ASF GitHub Bot commented on PHOENIX-7099:
-----------------------------------------
tkhurana commented on code in PR #1732:
URL: https://github.com/apache/phoenix/pull/1732#discussion_r1385636192
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java:
##########
@@ -344,6 +344,68 @@ public void testJsonExpressionIndexInvalid() {
" (JSON_MODIFY(jsoncol, '$.info.tags[2]', '\"newValue\"'))
include (col)");
}
+ @Test
+ public void testJsonExists() throws SQLException, IOException {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ String ddl = "create table " + tableName + " (pk integer primary
key, col integer, jsoncol json)";
+ conn.createStatement().execute(ddl);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
tableName + " VALUES (?,?,?)");
+ stmt.setInt(1, 1);
+ stmt.setInt(2, 2);
+ stmt.setString(3, basicJson);
+ stmt.execute();
+ stmt.setInt(1, 2);
+ stmt.setInt(2, 3);
+ stmt.setString(3, getJsonString(BASIC_JSON, "$[1]"));
+ stmt.execute();
+ conn.commit();
+
+ String query ="SELECT JSON_VALUE(jsoncol, '$.type'),
JSON_VALUE(jsoncol, '$.info.address.town') " +
+ " FROM " + tableName +
+ " WHERE JSON_EXISTS(jsoncol, '$.info.address.town')";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Basic", rs.getString(1));
+ assertEquals("Bristol", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals("Normal", rs.getString(1));
+ assertEquals("Bristol2", rs.getString(2));
+ assertFalse(rs.next());
+
+ query ="SELECT JSON_VALUE(jsoncol, '$.type'), JSON_VALUE(jsoncol,
'$.info.address.town') " +
+ " FROM " + tableName +
+ " WHERE JSON_EXISTS(jsoncol, '$.info.address.exists')";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("Bristol", rs.getString(2));
+ assertFalse(rs.next());
+
+ query ="SELECT JSON_VALUE(jsoncol, '$.type'), JSON_VALUE(jsoncol,
'$.info.address.town') " +
+ " FROM " + tableName +
+ " WHERE JSON_EXISTS(jsoncol, '$.info.address.name')";
+ rs = conn.createStatement().executeQuery(query);
+ assertFalse(rs.next());
+
+ query ="SELECT JSON_VALUE(jsoncol, '$.type'), JSON_VALUE(jsoncol,
'$.info.address.town') " +
+ " FROM " + tableName +
+ " WHERE JSON_EXISTS(jsoncol, '$.existsFail')";
+ rs = conn.createStatement().executeQuery(query);
+
+ query ="SELECT JSON_VALUE(jsoncol, '$.type'), JSON_VALUE(jsoncol,
'$.info.address.town') " +
+ " FROM " + tableName +
+ " WHERE JSON_EXISTS(jsoncol, '$.existsFail')";
+ rs = conn.createStatement().executeQuery(query);
Review Comment:
Copy paste twice
> Implement JSON_EXISTS function on json object
> ---------------------------------------------
>
> Key: PHOENIX-7099
> URL: https://issues.apache.org/jira/browse/PHOENIX-7099
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: Ranganath Govardhanagiri
> Assignee: Ranganath Govardhanagiri
> Priority: Major
>
> JSON_EXISTS determines whether a JSON value satisfies a search criterion.
> SELECT Col1 FROM Table WHERE JSON_EXISTS (jsonCol, '$.where')
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
