Using PartiQL on Json Query

I am going to use PartiQL for message processing in JSON format among MQTT data.
A typical JSON character text is:
{“msg”:{
“device” :{
“id” : “SerialXYZ1123”
},
“key” : “s10”,
“time” : 1939492934
}
}

Select device.id, key from msg where key = ‘s10’

Through the query, the following results are obtained:
{
“id” : “SerialXYZ1123”
“key” : “s10”,
}

  1. Can PartiQL query JSON data? <<>> => [], ’ quote => “” double …
    will i have to replace character ?
  2. I followed the example on github, but only CSV, S3.
    Can you give me a sample for querying Json Raw text with SQL?
    kotlin very hard and docs not usefull…

Are you using this as the CLI tool or as part of your Java/Kotlin application?

Now I am using Java/Kotlin application.

  1. I can’t find import org.partiql.examples.util.Example;

  2. I need example that just raw JsonString(not CSV,S3) queried PartiQL(SQL statement) for json formater (<<>> => [], ’ ’ => " ")

  3. if PartiQL have a Syntax check function on SQL Statement… I want to use.
    if PartiQL have function as 1~3, it is good for me.

thanks.

Here is a quick simple test that I think does what you want. A few warnings

  1. The down-conversion is lossy. See http://amzn.github.io/ion-docs/guides/cookbook.html#down-converting-to-json. The sample uses the code from the Ion Cookbook
  2. The word key in the query is wrapped in double quotes. key is a keyword in PartiQL. Wrapping it in double quotes also makes the lookup case sensitive (i.e. it will not match KeY )
package jsoninjsonout;

import com.amazon.ion.IonDatagram;
import com.amazon.ion.IonReader;
import com.amazon.ion.IonSystem;
import com.amazon.ion.IonWriter;
import com.amazon.ion.system.IonReaderBuilder;
import com.amazon.ion.system.IonSystemBuilder;
import com.amazon.ion.system.IonTextWriterBuilder;
import org.junit.Test;
import org.partiql.lang.CompilerPipeline;
import org.partiql.lang.eval.*;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;


public class JsonInJsonOUt {

    private static final IonSystem ION = IonSystemBuilder.standard().build();
    private final CompilerPipeline pipeline = CompilerPipeline.standard(ION);
    protected ExprValueFactory valueFactory = pipeline.getValueFactory();
    private static String QUERY =
            "SELECT d.msg.device.id, d.msg.\"key\" " +
            "FROM data AS d " +
            "WHERE msg.\"key\" = 's10'";
    private static String DATA = "{\"msg\":{\n" +
            "\"device\" :{\n" +
            "\"id\" : \"SerialXYZ1123\"\n" +
            "},\n" +
            "\"key\" : \"s10\",\n" +
            "\"time\" : 1939492934\n" +
            "}\n" +
            "}";

    @Test
    public void test() throws IOException {

        // Compile the query
        Expression expr = pipeline.compile(QUERY);
        // Turn JSON into ExprValue
        ExprValue table = valueFactory.newFromIonValue(ION.newLoader().load(DATA));


        Map<String, ExprValue> globals = new HashMap<>();
        // create table named data
        globals.put("data", valueFactory.newList(table));

        EvaluationSession session = EvaluationSession.builder().globals(Bindings.ofMap(globals)).build();

        ExprValue ionOutput = expr.eval(session);
        System.out.println("Ion : " +  ionOutput.getIonValue().toPrettyString());
        // toJson taken from http://amzn.github.io/ion-docs/guides/cookbook.html#down-converting-to-json
        StringBuilder stringBuilder = new StringBuilder();
        try (IonWriter jsonWriter = IonTextWriterBuilder.json().withPrettyPrinting().build(stringBuilder)) {
            ionOutput.getIonValue();
            rewrite(ionOutput.getIonValue().toString(), jsonWriter);
        }
        System.out.println("JSON: " + stringBuilder.toString());

    }

    void rewrite(String textIon, IonWriter writer) throws IOException {
        IonReader reader = IonReaderBuilder.standard().build(textIon);
        writer.writeValues(reader);
    }
}

And here is the output that I get

Ion : 
[
  {
    id:"SerialXYZ1123",
    key:"s10"
  }
]
JSON: 
[
  {
    "id":"SerialXYZ1123",
    "key":"s10"
  }
]

Process finished with exit code 0

For convenience with the JSON conversion you might also want to look at
https://javadoc.io/doc/software.amazon.ion/ion-java/latest/software/amazon/ion/system/IonTextWriterBuilder.html#withJsonDowngrade--

HTH

It was Great Library on Json Data Query.
It is fast and complying with SQL standards is very.

I’m developing a solution that uses PartiQL to do high-capacity Json processing.
It would be very nice to have a formal version, not an alpha version.
And we will try to add SQL Function by modifying the code.
I have one question: The list is always printed out as a result of the query.
To verify that the list is empty, check the String with ==[].
Isn’t there a smarter way?
Thank you for always letting me use a good solution.

There is no need to modify the PartiQL source code to add a function. See here for an example.

I’m not certain I understand your other question. Are you referring to the example here?

In that example result is an instance of ExprValue which implements Iterable<ExprValue>, which contains the result of the query. The toString() function of ExprValue is mostly intended for human readability and probably shouldn’t be used in production code.