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:
“device” :{
“id” : “SerialXYZ1123”
“key” : “s10”,
“time” : 1939492934

Select, 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.


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

  1. The down-conversion is lossy. See 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 org.junit.Test;
import org.partiql.lang.CompilerPipeline;
import org.partiql.lang.eval.*;

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.\"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" +

    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
        StringBuilder stringBuilder = new StringBuilder();
        try (IonWriter jsonWriter = IonTextWriterBuilder.json().withPrettyPrinting().build(stringBuilder)) {
            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);

And here is the output that I get

Ion : 

Process finished with exit code 0

For convenience with the JSON conversion you might also want to look at


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.