PartiQL Query Some case

I queried using this library and found the following situation.

Query : Select topic from “topic”
Message :

My code below (Java)

IonSystem ION = IonSystemBuilder. standard().build();
IonLoader loader = ION.newLoader();
CompilerPipeline pipeline = CompilerPipeline.standard(ION);
ExprValueFactory valueFactory = pipeline.getValueFactory();

Expression expr = (Expression) pipeline.compile("SELECT lux from \"lux\"");
ExprValue table = valueFactory.newFromIonValue(loader.load("{\"lux\":1}"));
Map<String, ExprValue> globals = new HashMap<>();
globals.put("lux", valueFactory.newList(table));
EvaluationSession session = EvaluationSession.builder().globals(Bindings.ofMap(globals)).build();
ExprValue ionOutput = expr.eval(session);

StringBuilder stringBuilder = new StringBuilder();
IonWriter jsonWriter = IonTextWriterBuilder.json().withPrettyPrinting().build(stringBuilder);

if(ionOutput.iterator().hasNext()) {
   try {
        IonReader reader = IonReaderBuilder.standard().build(ionOutput.iterator().next().getIonValue().toString());
        jsonWriter.writeValues(reader);
        return stringBuilder.toString();

    } catch (IOException e) {
        return e.toString(); //error case
    }
} else {
	return null;
}

  1. result stringBuilder.toString() is { “lux”:{ “lux”:1 }} but I want { “lux”:1 }. Is this intended? Or issue?
    i guess SELECT Item field lux not direct JSON key but FROM Statement lux has all message…
    how to 1) result { “lux”:{ “lux”:1 }} to { “lux”:1 }?

2)this case not same FROM and SELECT ITEM if query is “SELECT lux2 from “lux”” and message is “{“lux2”:1}” => result is { “lux2”:1 }.

  1. this case set alias t like “select lux from “lux” as t”
    result is { “lux”:1 }. Is it best that I avoid this problem?

Hi,

This does look suspicious. I am not sure what this line in your code does though,

ExprValue resultExpressioin = partiQLSQL(valueFactory, expr, loader.load("{\"lux\":1}"), "lux");

What does partiQLSQL do? Can you share that function’s definition?

One of the things going on here that is probably somewhat confusing is that SELECT lux FROM “lux” is the same as SELECT lux AS lux FROM “lux” AS lux. It’s also confusing that the name of your field is in the struct also lux. To make it more clear what is going on, use different names for everything and specifying aliases manually to disambiguate the variables. i.e. SELECT l.luxId FROM lux AS l and then bind the global variable lux to {luxId:1}.

Hi. I paste Wrong code. now fixed.

above all. explain project I develop…
We make simple query service on MQTT JSON Data.

Like… MQTT topic is “myhome/+/abc”, and message is {“lux” : 1, “id”: 2} and than
Query = [Select lux from “myhome/+/abc” where id = 2] => result is {“lux” : 1}
So. I want to set topic on FROM statement.

Yes. I hope that this Some User make query like (FROM statement) != (JSON key name). But this query is used on user side with unknown this PartiQL Spec not on me. So… User has possibility make topic = lux and query = SELECT lux FROM “lux” and message = {“lux” : 1}.

finally. as your guide, I can avoid my issue way of 1. different name and 2. use alias. thanks.

And another Question.
if userValue is {“lux” : 0.2}

StringBuilder stringBuilder = new StringBuilder();
IonWriter jsonWriter = IonTextWriterBuilder.json().build(stringBuilder);
IonReader reader =
IonReaderBuilder.standard().build(userValue.getIonValue().toString());
jsonWriter.writeValues(reader);
String showString = stringBuilder.toString();
result is {“lux”:1e-1}, I want to {“lux”:0.1} (+ IonTextWriterBuilder.json().mininal(). => {lux:0.} no double quotation. not want).
how can i show dot point float value?

The Ion cookbook has a section called “Down-converting to JSON” that covers some of these issues with some caveats.

http://amzn.github.io/ion-docs/guides/cookbook.html

Have you tried using that code (or snippets of that code)?

See also: https://www.javadoc.io/doc/software.amazon.ion/ion-java/1.0.0/software/amazon/ion/system/IonTextWriterBuilder.html#json--