Support JSON_VALUE in SQL

[navis]

https://calcite.apache.org/docs/reference.html#json-functions

    testQuery(
        "WITH X AS ("
        + " SELECT * FROM "
        + " (VALUES"
        + "   (0, 'k1', '{\"field1\":\"value1\", \"field2\": 2}'),"
        + "   (0, 'k2', '{\"field1\":\"noval1\", \"field2\": 2}')"
        + " ) AS T (__time, assetId, ctx)"
        + ")"
        + "SELECT * FROM X WHERE json_value(ctx, '$.field1') like 'value%'",
        new Object[]{0, "k1", "{\"field1\":\"value1\", \"field2\": 2}"}
    );

[navis]

Not supports other options, for now

[navis]

Supports return type

    testQuery(
        "WITH X AS ("
        + " SELECT * FROM "
        + " (VALUES"
        + "   (0, '{\"a\": \"[1,2]\", \"b\": [1,2], \"c\": \"hi\", \"d\": 3}')"
        + " ) AS T (__time, v)"
        + ")"
        + "SELECT cast(v as int)," 
        + "       json_value(v, '$.a')," 
        + "       json_value(v, '$.b')," 
        + "       json_value(v, '$.c')," 
        + "       json_value(v, '$.d' RETURNING int) FROM X",
        new Object[]{null, "[1,2]", "", "hi", 3}
    );

[navis]

Supports default on empty

    testQuery(
        "WITH X AS ("
        + " SELECT * FROM "
        + " (VALUES"
        + "   (0, '{\"a\": \"[1,2]\", \"b\": [1,2], \"c\": \"hi\", \"d\": 3}')"
        + " ) AS T (__time, v)"
        + ")"
        + "SELECT cast(v as int),"
        + "       json_value(v, '$.a'),"
        + "       json_value(v, '$.b'),"
        + "       json_value(v, '$.b' DEFAULT 'x' ON EMPTY),"
        + "       json_value(v, '$.b' DEFAULT __time ON EMPTY),"
        + "       json_value(v, '$.c'),"
        + "       json_value(v, '$.d' RETURNING int) FROM X",
        new Object[]{null, "[1,2]", "", "x", "0", "hi", 3}
    );