Data I/O: JSON Import and Export

This how-to guide demonstrates how to import and export JSON data using Rel.

Goal

The goal of this how-to guide is to familiarize the reader with Rel’s importing and exporting functionalities for data in the JSON format (opens in a new tab). After reading this guide you should expect to know how to import and export files of JSON data using the different available options.

You may also find the CSV Import and CSV Export how-to guides relevant since they cover how to import and export other types of data.

Relational Representation of JSON

When representing JSON data relationally, you can typically think of it as a tree. For example, consider the following JSON data for a person:

{
  "first_name": "John",
  "last_name": "Smith",
  "address": { "city": "Seattle",
               "state": "WA" },
  "phone": [
    { "type": "home",
      "number": "206-456" },
    { "type": "work",
      "number": "206-123" }
  ]
}

You can view this data as a tree:

In Rel, all data are expressed as relations (either derived relations or base relations), preferably in the highly normalized Graph Normal Form. You will usually want to store imported data as base relations, and then define views and other derived relations as derived relations based on the base relation data. See Working With Base Relations for more details.

Defining the previous JSON data directly in Rel — instead of importing it — looks like this:

// read query
 
def json[:first_name] = "John"
def json[:last_name] = "Smith"
def json[:address, :city] = "Seattle"
def json[:address, :state] = "WA"
def json[:phone, :[], 1, :type] = "home"
def json[:phone, :[], 1, :number] = "206-456"
def json[:phone, :[], 2, :type] = "work"
def json[:phone, :[], 2, :number] = "206-123"
 
def output = json

In Rel, the JSON keys turn into relation names, starting with :. Arrays are relations as well, using :[] as the relation name. Positional information within an array is indicated explicitly with an integer argument, as shown in the last four lines above. This is discussed in more detail in a later section about Exporting Arrays.

Importing JSON Data

The RelationalAI RKGS supports importing JSON data natively through the use of load_json.

Consider the following simple JSON file as a first example. This example has a flat structure (i.e., no nesting of data, no objects, no arrays) with all the primitive data types discussed earlier:

{
    "integer" : 123,
    "float" : 10.12,
    "scientific" : 10E+02,
    "string" : "hello world",
    "mynull" : null
}

file: simple.json (opens in a new tab)

You can now import this simple file within the json relation as follows:

// read query
 
def config[:path] = "azure://raidocs.blob.core.windows.net/csv-import/simple.json"
def output = load_json[config]

As the previous example shows, some type conversions happen automatically when you load JSON data. For example, the null value is converted to missing while the scientific notation is converted to a Float64.

More elaborate examples of JSON data are also supported. Here is an example that has nested data and arrays:

{
  "first_name": "John",
  "last_name": "Smith",
  "address": { "city": "Seattle",
               "state": "WA" },
  "phone": [
    { "type": "home",
      "number": 206456 },
    { "type": "work",
      "number": 206-123 }
  ]
}

file: person.json (opens in a new tab)

You can also read this as follows:

// read query
 
def config[:path] = "azure://raidocs.blob.core.windows.net/csv-import/person.json"
def output = load_json[config]

You can observe that phone is now an array, while the nested data, for example, city within address, are represented with the additional column names within the json relation.

Importing Local JSON Files

You can read JSON data from a string instead of a file as follows:

// read query
 
def config[:data] = """
      {"name": "John", "phone": [{"no.":"1234"}, {"no.":"4567"}]}
      """
def output = load_json[config]

Combined with the RAI notebook upload functionality, you can use this to import local JSON files into Rel:

1. Upload the file as a string to a named relation, for example, mydata.

2. Use def config:data = mydata, and set other configuration options as needed.

3. Do load_json[config].

Importing From the Cloud

You can import JSON files from the cloud using load_json[config]. Refer to Accessing the Cloud for further details.

Compression

You can import compressed JSON files. See Supported Data Files for more details.

Querying JSON Data

Now you can query the imported JSON data. For example:

// read query
 
def config[:path] = "azure://raidocs.blob.core.windows.net/csv-import/person.json"
def my_data = load_json[config]
 
def output = table[my_data:phone[:[]]]

The output returns all the imported data regarding phone numbers. See Relational Representation of JSON for more details on how the JSON data are stored and can be accessed in the RKGS.

Exporting JSON Data

You can start by learning how to export JSON data. Similar to exporting CSV data, this is done by defining the control relation export using export_json. Here is a simple example for exporting:

def json[:name] = "John"
def json[:age] = 12
def config:path = cloud_URI
def config:data = json
 
def export = export_json[config]

Similar to when you export to CSV files, you must specify the path and data parameters in config. Note that, again similar to exporting CSV data, the configuration should include credentials that give write access to the URI where you want to write the data (cloud_URI).

The contents of the output file should be:

{"name":"John","age":12}

Given a relation R, you can obtain the JSON representation with json_string[R] and preview it in a RAI notebook using view_json[R]:

// read query
 
def json[:name] = "John"
def json[:age] = 12
def output = json_string[json]

Export Options

The data are exported, however, they are not nicely formatted. You can do this through the indent parameter that you can pass to export_json. For example, the following code tells the system that you want to use four spaces when formatting the output data:

def json[:name] = "John"
def json[:age] = 12
 
def export = export_json[(:path, cloud_URI); (:data, json); (:indent, 4)]

When checking the output file, the content now looks like this:

{
    "name": "John",
    "age": 12
}

When using export_json, you can specify the following parameters, all of which are optional:

Exporting Nested Data

You have so far explored exporting some data in the JSON format which were essentially all at the same level, i.e., there was no nesting of data. You will now examine how to export data that are nested in a more elaborate way. For example, consider the following data, where you have an object inside another object. More specifically, you have a car object in addition to the top-level person object.

def json[:name] = "John"
def json[:age] = 35
def json[:car, :brand] = "Mazda"
def json[:car, :color] = "red"
 
def export = export_json[(:path, cloud_URI; :data, json; :indent, 4)]

If you take a look at the output file, you see the following:

{
    "name": "John",
    "car": {
        "brand": "Mazda",
        "color": "red"
    },
    "age": 35
}

You can observe that the car object is properly outputted. You can also have more objects and additional nesting. Here is a more elaborate example:

def json[:name] = "John"
def json[:age] = 35
 
def json[:car1, :brand] = "Mazda"
def json[:car1, :color] = "red"
def json[:car1, :engine, :size] = 2000
def json[:car1, :engine, :cylinders, :setup] = "inline"
def json[:car1, :engine, :cylinders, :number] = 6
 
def json[:car2, :brand] = "Jaguar"
def json[:car2, :color] = "green"
def json[:car2, :engine, :size] = 3000
def json[:car2, :engine, :cylinders, :setup] = "V"
def json[:car2, :engine, :cylinders, :number] = 8
 
def export = export_json[(:path, cloud_URI; :data, json; :indent, 4)]

This gives the output:

{
    "name": "John",
    "car1": {
        "brand": "Mazda",
        "color": "red",
        "engine": {
            "size": 2000,
            "cylinders": {
                "number": 6,
                "setup": "inline"
            }
        }
    },
    "age": 35,
    "car2": {
        "brand": "Jaguar",
        "color": "green",
        "engine": {
            "size": 3000,
            "cylinders": {
                "number": 8,
                "setup": "V"
            }
        }
    }
}

Exporting Arrays

In the last example, you exported two cars, using two different keys, car1 and car2. JSON, however, supports arrays for this purpose. You will now learn how to export arrays. Instead of car1 and car2, you have an array called cars containing the two objects:

def json[:name] = "John"
def json[:age] = 35
 
// note the :[] marker here
def json[:cars, :[], 1, :brand] = "Mazda"
def json[:cars, :[], 1, :color] = "red"
def json[:cars, :[], 1, :engine, :size] = 2000
def json[:cars, :[], 1, :engine, :cylinders, :setup] = "inline"
def json[:cars, :[], 1, :engine, :cylinders, :number] = 6
 
def json[:cars, :[], 2, :brand] = "Jaguar"
def json[:cars, :[], 2, :color] = "green"
def json[:cars, :[], 2, :engine, :size] = 3000
def json[:cars, :[], 2, :engine, :cylinders, :setup] = "V"
def json[:cars, :[], 2, :engine, :cylinders, :number] = 8
 
def export = export_json[(:path, cloud_URI; :data, json; :indent, 4)]

The :[] marker might seem redundant, but it is needed to output the JSON data properly as an array. Executing the above code, you obtain the output:

{
    "name": "John",
    "cars": [
        {
            "brand": "Mazda",
            "color": "red",
            "engine": {
                "size": 2000,
                "cylinders": {
                    "number": 6,
                    "setup": "inline"
                }
            }
        },
        {
            "brand": "Jaguar",
            "color": "green",
            "engine": {
                "size": 3000,
                "cylinders": {
                    "number": 8,
                    "setup": "V"
                }
            }
        }
    ],
    "age": 35
}

You can have arrays inside arrays in JSON, and Rel also supports that. Here is a simple example:

def json[:arr, :[], 1, :arr1, :[], 1] = 11
def json[:arr, :[], 1, :arr1, :[], 2] = 12
 
def json[:arr, :[], 2, :arr2, :[], 1] = 21
def json[:arr, :[], 2, :arr2, :[], 2] = 22
 
def export = export_json[(:path, cloud_URI; :data, json; :indent, 4)]

This gives output containing two arrays within an array:

{
    "arr": [
        {
            "arr1": [
                11,
                12
            ]
        },
        {
            "arr2": [
                21,
                22
            ]
        }
    ]
}

In the previous examples, you exported the data by manually associating an index for each object within the cars array. However, this might not be optimal when you deal with large or variable-size data. To implicitly do so in a more general way, Rel requires adding indices to symbols. You can do that by sorting the hashes of the symbols:

def brands = "Mazda"; "Jaguar"
def brand_ids = transpose[ sort[brands] ]
 
def json[:name] = "John"
def json[:age] = 35
 
def json = :cars, :[], brand_ids["Mazda"], :brand, "Mazda"
def json = :cars, :[], brand_ids["Mazda"], :color, "red"
def json = :cars, :[], brand_ids["Mazda"], :engine, :size, 2000
 
def json = :cars, :[], brand_ids["Jaguar"], :brand, "Jaguar"
def json = :cars, :[], brand_ids["Jaguar"], :color, "green"
def json = :cars, :[], brand_ids["Jaguar"], :engine, :size, 3000
 
def export = export_json[(:path, cloud_URI); (:data, json); (:indent, 4)]

The relation brand_ids contains the indexes associated to each object within the cars array, i.e., a car brand in this example. See The Standard Library for further details about the sort function.

Rel JSON Utils

The Standard Library’s json_string[R] returns the JSON string representing the relation R.

The view_json[R] utility from the display library displays the relation R as a JSON object.

Appendix: The JSON Data Format

JSON (opens in a new tab) (JavaScript Object Notation) is a popular open standard file and data exchange format. Many applications use JSON to exchange data. JSON is a data format that is also human-readable. Its structure can optionally follow a strictly enforced schema (JSON schema) defining the format and structure of the data.

JSON supports the following data types:

Here is a small example of a JSON array:

[ 1, "23", {"next": 45}]

And here is a small example of a JSON object that includes another object:

{
    "name" : "john",
    "birthdate": {
        "day": 10,
        "month": 12,
        "year": 1970
    }
}

Summary

You have learned how to import JSON data in Rel using the load_json functionality. By following the examples, you are now able to import JSON data into your RKGS database, including local files and files in cloud storage. You can also export JSON data, including arrays and nested data.