Load a JSON Document

QLDB stores data as Amazon Ion documents. Ion is a super-set of JSON. Customers often wish to integrate QLDB with applications that process data formatted as JSON. Since Ion is a superset of JSON, this can be done quite easily. The Ion libraries provide easy mechanisms to convert data to and from JSON.

However, simply storing JSON documents in QLDB doesn’t take advantage of all of the features of Ion and QLDB. Ion provides a richer set of data types than JSON and these types are supported by the PartiQL query language. For example, Ion provides a timestamp data type that provides rich support for manipulating and querying dates and times. In JSON, dates and times are captured as text strings. This limits the application to using only PartiQL string functions and comparisons when working with QLDB.

It is therefore desirable to be able to convert some fields in our input JSON to Ion types before storage in QLDB. In this exercise, we’ll show a document-oriented approach to converting JSON to Ion using higher-level Ion APIs. This approach is very easy to code and is useful when the amount of manipulation of the incoming JSON is relatively light.

We’ve already learned how to store and retrieve Ion objects with QLDB, so this exercise will cover loading a JSON string and converting it to Ion with the Ion Java library.

We’ll start where we left off in the last section. The App.java file should be open in the Cloud9 IDE.

Create a new method for this exercise in the App.java file by adding the code below.

private static void readFromJsonTargetedReplacement() throws Exception {
}

Modify the main() method to call our new method for this exercise instead of the method for the last exercise. The main() method should now look like this:

public static void main(String[] args) throws Exception {
    //buildAndWriteIon();
    //readAndUpdateIon();
    readFromJsonTargetedReplacement();
}

Add the line below to the readFromJsonTargetedReplacement() method. This declares a string variable that contains our JSON data. We’re going to load our JSON data from a string. However, the APIs we’ll use to load the JSON data also support java.io.Reader, java.io.File, and other input sources.

String jsonInput = "{\"PersonId\":\"987654321\",\"FirstName\":\"Mary\",\"LastName\":\"Smith\",\"MoneyInWallet\":143.39,\"DateOfBirth\":\"1979-10-15\",\"NumberOfLegs\":2,\"LikesGreenBeans\":true,\"ThingsInPocket\":[\"phone\"],\"HomeAddress\":{\"Street1\":\"400 N. Broadway\",\"City\":\"Yonkers\",\"State\":\"NY\",\"Zip\":\"10705\"}}";

Next we’ll initialize the IonSystem as before.

IonSystem ionSys = IonSystemBuilder.standard().build();

Next, we’ll create a new IonLoader from our initialized IonSystem.

IonLoader loader = ionSys.newLoader();

The IonLoader does all of the hard work of parsing and loading our JSON data for us, and it gives it to us in our familiar IonValue object hierarchy, though indirectly.

IonDatagram gram = loader.load(jsonInput);

The load() method on IonLoader actually gives us an IonDatagram object, which is a container of Ion values, so we’ll have to do a little bit of work to extract our document from it. The code below grabs the first IonValue out of the datagram and casts it to an IonStruct for us to use. Real application code should do some error checking and validation of the contents of the datagram, but this works for example purposes.

IonStruct personDocument = (IonStruct) gram.get(0);

Add the following code to pretty-print our Person document as it is after loading from JSON.

System.out.println(personDocument.toPrettyString());

Run the program and view the output. Note that the “DateOfBirth” field is formatted as a string.

{
  PersonId:"987654321",
  FirstName:"Mary",
  LastName:"Smith",
  MoneyInWallet:143.39,
  DateOfBirth:"1979-10-15",
  NumberOfLegs:2,
  LikesGreenBeans:true,
  ThingsInPocket:[
    "phone",
    "lipstick"
  ],
  HomeAddress:{
    Street1:"400 N. Broadway",
    City:"Yonkers",
    State:"NY",
    Zip:"10705"
  }
}

Now that we have our JSON data parsed and loaded into the familiar Ion objects we’ve been using, we can do some targeted field conversions from JSON to proper Ion. Our input JSON contains a string value to represent our Person’s date of birth. Let’s convert that string date into a proper IonTimestamp.

First, get the date string from our person document. Again, we’re doing less error- and type-checking than we would normally do in a real application. We know the format of the date, so we’ll do a quick-and-dirty split of the string into date segments and we’ll convert those segments into integers. We’ll use these integer values to construct a Timestamp object.

Once we have our Timestamp object that we converted from the date string, we can replace the string value in our Person document with its IonTimestamp representation for storage in QLDB.

String dobStr = ((IonString) personDocument.get("DateOfBirth")).stringValue();
String[] parts = dobStr.split("-");

int year = Integer.parseInt(parts[0]);
int month = Integer.parseInt(parts[1]);
int day = Integer.parseInt(parts[2]);

personDocument.put("DateOfBirth").newTimestamp(Timestamp.forDay(year, month, day));

Now that we’ve converted the date of birth field to an Ion timestamp type, let’s display it to the console and compare it to the original version. Add the following line of code to the end of the readFromJsonTargetedReplacement().

System.out.println(personDocument.toPrettyString());

Running the program produces the following results:

{
  PersonId:"987654321",
  FirstName:"Mary",
  LastName:"Smith",
  MoneyInWallet:143.39,
  NumberOfLegs:2,
  LikesGreenBeans:true,
  ThingsInPocket:[
    "phone",
    "lipstick"
  ],
  HomeAddress:{
    Street1:"400 N. Broadway",
    City:"Yonkers",
    State:"NY",
    Zip:"10705"
  },
  DateOfBirth:1979-10-15
}

As you can see, the date of birth is no longer a string. By converting the string date to a native Ion timestamp, the date of birth can be treated as an actual date/time value in QLDB. PartiQL can sort the date of birth field as a timestamp, date/time comparisons work correctly, and PartiQL’s date/time functions can be used to format output.

Converting back to JSON

Now that we’ve converted JSON input data into Ion, let’s convert Ion back to JSON. Since Ion is a super-set of JSON and offers data types that are not available in JSON, converting Ion to JSON may require some “down-converting”. The Ion Cookbook describes how down-conversion works for each data type.

Add the code below to the bottom of the readFromJsonTargetedReplacement() method. We start by creating a java.lang.StringBuilder. This will be where our JSON data is “written” to.

An IonWriter will do the work of formatting our Ion data as JSON and writing it to our StringBuilder. We create the IonWriter using an IonTextWriterBuilder. We create an IonWriter that can perform JSON down-converting and pretty-printing by calling json() and withPrettyPrinting() on the IonTextWriterBuilder and, finally, build().

Now that we have an IonWriter, we can call writeTo() on our IonValue, passing in the IonWriter that will convert the IonValue to a formatted JSON string and we’ll print that to the console.

StringBuilder stringBuilder = new StringBuilder();
try (IonWriter jsonWriter = IonTextWriterBuilder.json().withPrettyPrinting().build(stringBuilder)) {
    personDocument.writeTo(jsonWriter);
}
System.out.println(stringBuilder.toString());

Run the program and observe the JSON-formatted output. Note that the date has been converted back to a string. Also note how the “MoneyInWallet” decimal field has been formatted.

{
  "PersonId":"987654321",
  "FirstName":"Mary",
  "LastName":"Smith",
  "MoneyInWallet":14339e-2,
  "NumberOfLegs":2,
  "LikesGreenBeans":true,
  "ThingsInPocket":[
    "phone",
    "lipstick"
  ],
  "HomeAddress":{
    "Street1":"400 N. Broadway",
    "City":"Yonkers",
    "State":"NY",
    "Zip":"10705"
  },
  "DateOfBirth":"1979-10-15"
}

Our complete readFromJsonTargetedReplacement() method looks like this.

private static void readFromJsonTargetedReplacement() throws Exception {
    String jsonInput = "{\"PersonId\":\"987654321\",\"FirstName\":\"Mary\",\"LastName\":\"Smith\",\"MoneyInWallet\":143.39,\"DateOfBirth\":\"1979-10-15\",\"NumberOfLegs\":2,\"LikesGreenBeans\":true,\"ThingsInPocket\":[\"phone\", \"lipstick\"],\"HomeAddress\":{\"Street1\":\"400 N. Broadway\",\"City\":\"Yonkers\",\"State\":\"NY\",\"Zip\":\"10705\"}}";

    IonSystem ionSys = IonSystemBuilder.standard().build();
    IonLoader loader = ionSys.newLoader();
    IonDatagram gram = loader.load(jsonInput);
    IonStruct personDocument = (IonStruct) gram.get(0);

    System.out.println(personDocument.toPrettyString());
    String dobStr = ((IonString) personDocument.get("DateOfBirth")).stringValue();
    String[] parts = dobStr.split("-");

    int year = Integer.parseInt(parts[0]);
    int month = Integer.parseInt(parts[1]);
    int day = Integer.parseInt(parts[2]);

    personDocument.put("DateOfBirth").newTimestamp(Timestamp.forDay(year, month, day));        

    System.out.println(personDocument.toPrettyString());

    StringBuilder stringBuilder = new StringBuilder();
    try (IonWriter jsonWriter = IonTextWriterBuilder.json().withPrettyPrinting().build(stringBuilder)) {
        personDocument.writeTo(jsonWriter);
    }
    System.out.println(stringBuilder.toString());    
}

If you are having trouble getting your program to run, click here to download the complete App.java file for this lab.