Examples with JsonString com.mysql.cj.xdevapi.JsonString used on opensource projects

Example 16 with JsonString

use of com.mysql.cj.xdevapi.JsonString in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindOverlaps.

@Test
public void testCollectionFindOverlaps() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    try {
        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DbDoc doc = null;
        DocResult docs = null;
        long l3 = 2147483647;
        double d1 = 1000.1234;
        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
            }
            newDoc2.add("ARR1", jarray);
            JsonArray karray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
            }
            newDoc2.add("ARR2", karray);
            JsonArray larray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
            }
            newDoc2.add("ARR3", larray);
            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }
        assertEquals((maxrec), this.collection.count());
        /* find with single OVERLAPS in array */
        docs = this.collection.find("2147483647 overlaps $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        Table table = this.schema.getCollectionAsTable(this.collectionName);
        RowResult rows = table.select("doc->$._id as _id").where("2147483647 overlaps $.ARR1").execute();
        Row r = rows.next();
        assertEquals("\"1000\"", r.getString("_id"));
        assertFalse(rows.hasNext());
        /* find with array OVERLAPS array */
        docs = this.collection.find("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
        r = rows.next();
        assertEquals("\"1000\"", r.getString("_id"));
        r = rows.next();
        assertEquals("\"1001\"", r.getString("_id"));
        r = rows.next();
        assertEquals("\"1002\"", r.getString("_id"));
        assertFalse(rows.hasNext());
        /* find with array OVERLAPS array with orderBy */
        docs = this.collection.find("[2147483648, 2147483648, 2147483649] overlaps $.ARR1").orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        /* */
        docs = this.collection.find("[!false && true] OVERLAPS [true]").execute();
        assertEquals(maxrec, docs.count());
        /* Overlaps with NULL */
        docs = this.collection.find("NULL overlaps $.ARR1").execute();
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("NULL overlaps $.ARR1").execute();
        assertFalse(rows.hasNext());
        docs = this.collection.find("$.ARR1 overlaps null").execute();
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("$.ARR1 overlaps NULL").execute();
        assertFalse(rows.hasNext());
        /* Not Overlaps with NULL */
        docs = this.collection.find("NULL not overlaps $.ARR1").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select("doc->$._id as _id").where("NULL not overlaps $.ARR1").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        docs = this.collection.find("$.ARR1 not overlaps null").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select("doc->$._id as _id").where("$.ARR1 not overlaps null").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        /* Test OVERLAPS/NOT OVERLAPS with empty array - Expected to pass, though the array is empty but still valid */
        // checking the case insensitivity as well
        docs = this.collection.find("[] Overlaps $.ARR1").execute();
        assertFalse(docs.hasNext());
        rows = table.select().where("[] ovErlaps $.ARR1").execute();
        assertFalse(rows.hasNext());
        // checking the case insensitivity as well
        docs = this.collection.find("$.ARR1 overlapS []").execute();
        assertFalse(docs.hasNext());
        rows = table.select().where("$.ARR1 ovErlaps []").execute();
        assertFalse(rows.hasNext());
        docs = this.collection.find("[] not overlaps $.ARR1").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select().where("[] not overlaps $.ARR1").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        // checking the case insensitivity as well
        docs = this.collection.find("$.ARR1 not oveRlaps []").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select().where("$.ARR1 not overlaps []").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        /* When the right number of operands are not provided - error should be thrown */
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> this.collection.find("$.ARR1 OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> this.collection.find("$.ARR1 NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not OVERLAPS").execute());
        final Table table1 = this.schema.getCollectionAsTable(this.collectionName);
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> table1.select().where("$.ARR1 OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> table1.select().where("$.ARR1 NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not OVERLAPS").execute());
        /* invalid criteria, e.g. .find("[1, 2, 3] OVERLAPS $.age") . where $.age is atomic value */
        dropCollection("coll2");
        Collection coll2 = this.schema.createCollection("coll2", true);
        coll2.add("{ \"_id\": \"1\", \"name\": \"nonjson\", \"age\": \"50\",\"arrayField\":[1,[7]]}").execute();
        // The below command should give exception, but X-plugin doesn't return any error
        docs = coll2.find("[1,2,3] overlaps $.age").execute();
        assertEquals(0, docs.count());
        docs = coll2.find("arrayField OVERLAPS [7]").execute();
        assertEquals(0, docs.count());
        docs = coll2.find("arrayField[1] OVERLAPS [7]").execute();
        assertEquals(1, docs.count());
        table = this.schema.getCollectionAsTable("coll2");
        rows = table.select().where("[1,2,3] overlaps $.age").execute();
        assertEquals(0, rows.count());
        /* Test with empty spaces */
        dropCollection("coll3");
        Collection coll3 = this.schema.createCollection("coll3", true);
        // List contains an array without any space
        coll3.add("{ \"_id\":1, \"name\": \"Record1\",\"list\":[\"\"], \"age\":15, \"intList\":[1,2,3] }").execute();
        // List contains an array with space
        coll3.add("{ \"_id\":2, \"name\": \"overlaps\",\"list\":[\" \"],\"age\":24}").execute();
        coll3.add("{ \"_id\":3, \"overlaps\": \"overlaps\",\"age\":30}").execute();
        docs = coll3.find("[''] OVERLAPS $.list").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());
        table = this.schema.getCollectionAsTable("coll3");
        rows = table.select("doc->$._id as _id").where("[''] overlaps $.list").execute();
        r = rows.next();
        assertEquals("1", r.getString("_id"));
        docs = coll3.find("[' '] OVERLAPS $.list").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        rows = table.select("doc->$._id as _id").where("[' '] overlaps $.list").execute();
        r = rows.next();
        assertEquals("2", r.getString("_id"));
        docs = coll3.find("'overlaps' OVERLAPS $.name").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        rows = table.select("doc->$._id as _id").where("'overlaps' overlaps $.name").execute();
        r = rows.next();
        assertEquals(1, docs.count());
        assertEquals("2", r.getString("_id"));
        docs = coll3.find("[3] OVERLAPS $.intList").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("[3] overlaps $.intList").execute();
        assertEquals(1, rows.count());
        /* Escape the keyword, to use it as identifier */
        docs = coll3.find("`overlaps` OVERLAPS $.`overlaps`").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("'overlaps' overlaps $.`overlaps`").execute();
        assertEquals(1, rows.count());
        docs = coll3.find("$.`overlaps` OVERLAPS `overlaps`").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("$.`overlaps` overlaps 'overlaps'").execute();
        assertEquals(1, rows.count());
        dropCollection("coll4");
        Collection coll4 = this.schema.createCollection("coll4", true);
        coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"one\":1, \"two\":2, \"three\":3},\"name\":\"one\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"four\":4, \"five\":5, \"six\":6},\"name\":\"two\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"two\":2, \"four\":4, \"six\":6},\"name\":\"three\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":3, \"six\":9, \"nine\":9},\"name\":\"four\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":6, \"six\":12, \"nine\":18},\"name\":\"five\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[3,4,5]}, \"name\":\"six\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[1,2,3]}, \"name\":\"seven\"}").execute();
        docs = coll4.find("`overlaps` OVERLAPS `list`").execute();
        assertEquals(3, docs.count());
        doc = docs.fetchOne();
        assertEquals("one", (((JsonString) doc.get("name")).getString()));
        doc = docs.fetchOne();
        assertEquals("four", (((JsonString) doc.get("name")).getString()));
        doc = docs.fetchOne();
        assertEquals("seven", (((JsonString) doc.get("name")).getString()));
        table = this.schema.getCollectionAsTable("coll4");
        rows = table.select("doc->$.name as name").where("$.`overlaps` OVERLAPS $.`list`").execute();
        assertEquals(3, rows.count());
        r = rows.next();
        assertEquals("\"one\"", r.getString("name"));
    } finally {
        dropCollection("coll4");
        dropCollection("coll3");
        dropCollection("coll2");
    }
}

Example 17 with JsonString

use of com.mysql.cj.xdevapi.JsonString in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollRemoveTabDeleteWithOverlaps.

@SuppressWarnings("deprecation")
@Test
public void testCollRemoveTabDeleteWithOverlaps() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, maxrec = 10;
    DocResult docs = null;
    Result res = null;
    /* add(DbDoc[] docs) */
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    /* find without Condition */
    docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
    i = 0;
    while (docs.hasNext()) {
        docs.next();
        i++;
    }
    assertEquals((maxrec), i);
    /* remove with single OVERLAPS */
    res = this.collection.remove("'1001' overlaps $._id").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("'1001' overlaps $._id").execute();
    assertFalse(docs.hasNext());
    /* remove with mulltiple OVERLAPS */
    String findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not overlaps $._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    res = this.collection.remove(findCond).execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find(findCond).execute();
    assertFalse(docs.hasNext());
    /* remove with single OVERLAPS */
    res = this.collection.remove("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    assertFalse(docs.hasNext());
    /* remove with single OVERLAPS for float */
    res = this.collection.remove("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    assertEquals(1, res.getAffectedItemsCount());
    docs = this.collection.find("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    assertFalse(docs.hasNext());
    res = this.collection.remove("true").execute();
    jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    Table table = this.schema.getCollectionAsTable(this.collectionName);
    /* delete with single OVERLAPS */
    res = table.delete().where("'1001' overlaps doc->$._id").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    RowResult rows = table.select("doc->$.F1 as _id").where("'1001' overlaps doc->$._id").execute();
    assertFalse(rows.hasNext());
    /* delete with multiple OVERLAPS */
    findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not overlaps doc->$._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    res = table.delete().where(findCond).execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as _id").where(findCond).execute();
    assertFalse(rows.hasNext());
    /* delete with single OVERLAPS for float */
    res = table.delete().where("30.1234 overlaps doc->$.F2").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as F1").where("30.1234 overlaps doc->$.F2").execute();
    assertFalse(rows.hasNext());
    /* delete with single OVERLAPS for int */
    res = table.delete().where("10004 overlaps doc->$.F4").execute();
    assertEquals(res.getAffectedItemsCount(), 1);
    rows = table.select("doc->$.F1 as F1").where("10004 overlaps doc->$.F4").execute();
    assertFalse(rows.hasNext());
}

Example 18 with JsonString

use of com.mysql.cj.xdevapi.JsonString in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindInValidFunction.

@Test
public void testCollectionFindInValidFunction() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    DbDoc doc = null;
    DocResult docs = null;
    this.collection.add("{\"_id\": \"1001\", \"ARR\":[1,1,2], \"ARR1\":[\"name1\", \"name2\", \"name3\"]}").execute();
    this.collection.add("{\"_id\": \"1002\", \"ARR\":[1,2,3], \"ARR1\":[\"name4\", \"name5\", \"name6\"]}").execute();
    this.collection.add("{\"_id\": \"1003\", \"ARR\":[1,4,5], \"ARR1\":[\"name1\", \"name1\", \"name5\"]}").execute();
    docs = this.collection.find("[1,1,3] in $.ARR").execute();
    doc = docs.next();
    assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    docs = this.collection.find("[2,5] in $.ARR").execute();
    assertFalse(docs.hasNext());
    docs = this.collection.find("(1+2) in (1, 2, 3)").execute();
    doc = docs.next();
    docs = this.collection.find("concat('name', '6') in ('name1', 'name2', 'name6')").execute();
    doc = docs.next();
    Table tabNew = this.schema.getCollectionAsTable(this.collectionName);
    RowResult rows = tabNew.select("doc->$._id as _id").where("(1+2) in (1, 2, 3)").execute();
    rows.next();
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.", () -> tabNew.select("doc->$._id as _id").where("(1+2) in [1, 2, 3]").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.", () -> tabNew.select("doc->$._id as _id").where("(1+2) in doc->$.ARR").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires function that produce a JSON value\\.", () -> this.collection.find("concat('name', '6') in ['name1', 'name2', 'name6']").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.", () -> this.collection.find("(1+2) in $.ARR").execute());
    assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires function that produce a JSON value\\.", () -> this.collection.find("concat('name', '6') in $.ARR1").execute());
}

Example 19 with JsonString

use of com.mysql.cj.xdevapi.JsonString in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindInInvalid.

@Test
public void testCollectionFindInInvalid() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, j = 0, maxrec = 8, minArraySize = 3;
    DocResult docs = null;
    String json = "";
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
        JsonArray jarray = new JsonArray();
        for (j = 0; j < (minArraySize + i); j++) {
            jarray.addValue(new JsonString().setValue("Field-1-Data-" + i));
        }
        newDoc2.add("ARR1", jarray);
        this.collection.add(newDoc2).execute();
        newDoc2 = null;
        jarray = null;
    }
    assertEquals((maxrec), this.collection.count());
    /* add(DbDoc[] docs) */
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1100)));
        newDoc2.add("ARR1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonString().setValue("10-15-201" + i));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    json = "{\"_id\":\"1201\",\"XYZ\":2222, \"DATAX\":{\"D1\":1, \"D2\":2, \"D3\":3}}";
    this.collection.add(json).execute();
    /* find with invalid IN in document */
    try {
        docs = this.collection.find("{\"D1\":3, \"D2\":2, \"D3\":3} in $.DATAX").execute();
        assertFalse(docs.hasNext());
    } catch (XProtocolError Ex) {
        Ex.printStackTrace();
        if (Ex.getErrorCode() != MysqlErrorNumbers.ER_BAD_NULL_ERROR) {
            throw Ex;
        }
    }
    /* find with IN that does not match */
    docs = this.collection.find("\"2222\" in $.XYZ").execute();
    assertFalse(docs.hasNext());
    /* find with NULL IN */
    docs = this.collection.find("NULL in $.ARR1").execute();
    assertFalse(docs.hasNext());
    /* find with NULL IN */
    docs = this.collection.find("NULL in $.DATAX").execute();
    assertFalse(docs.hasNext());
    /* find with IN for non existant key */
    docs = this.collection.find("\"ABC\" in $.nonexistant").execute();
    assertFalse(docs.hasNext());
}

Example 20 with JsonString

use of com.mysql.cj.xdevapi.JsonString in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindInSanity.

@Test
public void testCollectionFindInSanity() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, maxrec = 10;
    DbDoc doc = null;
    DocResult docs = null;
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    /* find without Condition */
    docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
    i = 0;
    while (docs.hasNext()) {
        doc = docs.next();
        i++;
    }
    assertEquals((maxrec), i);
    /* find with single element IN which uses json_contains */
    docs = this.collection.find("'1001' in $._id").execute();
    doc = docs.next();
    assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with multiple IN which uses json_contains */
    String findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    docs = this.collection.find(findCond).execute();
    doc = docs.next();
    assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for string with orderBy */
    docs = this.collection.find("'Field-1-Data-2' in $.F1").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1002), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for numeric with orderBy */
    docs = this.collection.find("10000 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1000), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for float with orderBy */
    docs = this.collection.find("20.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* Testing with table */
    Table table = this.schema.getCollectionAsTable(this.collectionName);
    /* find with single IN for string */
    RowResult rows = table.select("doc->$._id as _id").where("'1001' in doc->$._id").execute();
    Row r = rows.next();
    assertEquals(r.getString("_id"), "\"1001\"");
    assertFalse(rows.hasNext());
    /* find with multiple IN in single select */
    findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    /* find with single IN for string */
    rows = table.select("doc->$._id as _id").where(findCond).execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1000\"");
    assertFalse(rows.hasNext());
    /* find with single IN for float */
    rows = table.select("doc->$._id as _id").where("20.1234 in doc->$.F2").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1001\"");
    assertFalse(rows.hasNext());
    /* find with single IN for string */
    rows = table.select("doc->$._id as _id").where("'Field-1-Data-2' in doc->$.F1").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1002\"");
    assertFalse(rows.hasNext());
    /* find with single IN for numeric */
    rows = table.select("doc->$._id as _id").where("10000 in doc->$.F4").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1000\"");
    assertFalse(rows.hasNext());
}