Handling JSON Data in Rel

JSON is one of the most popular formats for data interchange today. It is widely used not only due to its simplicity and interoperability, but also because it allows users to easily represent (mostly hierarchical) relations between data.

At RelationalAI, we are excited to announce the support of JSON data through the load_json and json_string relations.

Importing JSON Data from a URL or File

You can use load_json to easily import your JSON data into our Relational Knowledge Graph Management System (RKGMS). You need to provide either a file name or URL where the JSON data will be loaded from.

Currently, AWS S3 (s3://) and Azure (azure://) URLs are supported for importing JSON data.

Consider the following sample JSON data:

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

You can easily load that data from a sample JSON file from S3 as follows:

def result = load_json[
    "s3://relationalai-documentation-public/csv-import/person.json"
]

def config[:path] = "s3://relationalai-documentation-public/csv-import/person.json"
def output = load_json[config]

You can also specify a local file name instead of an S3 URL and the functionality will be identical. After loading, the JSON data are stored within the result relation as follows:

For cases when a connection to a URL is more complex i.e., where credentials may be required, you can still use load_json with a configuration relation (e.g., config) that encodes integration information. The config relation, which can be given any name, needs to specify the :path of the file to be imported and any integration parameters through :integration.

Here is an example using toy credentials for loading a JSON file from Azure:

def config[:path] = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"

def config[:integration, :provider] = "azure"
def config[:integration, :credentials, :azure_sas_token] = "sv=2014-02-14&sr=b&si=TestPolicy&sig=o%2B5%2F0C%2BLm7tWWftNKvQEGKHlSt%2Bfs8No7FZkUk5T%2Bv0%3D"

def json = load_json[config]

Finally, load_json allows you to load data directly from a JSON string. This is similarly done using a config relation, by specifying :data instead of :path.

Here is an example for loading data from a JSON string:

def config:data =
"""
{
    "first_name": "John",
    "last_name": "Smith",
    "children's ages": [10, 12, 3]
}
"""

def result = load_json[config]
def output = result

Note that when a configuration relation is passed to load_json, it should specify either :path or :data, but not both. You can also use parse_json with a string directly, without needing to use a config relation.

Converting a Rel Relation to JSON

You can also convert data from an existing relation to a JSON string. This is done using json_string, which gives you the string representation of a relation that encodes JSON data.

Here is an example that creates the relation json_relation and represents it as a JSON string:

def json_relation[:name] = "Amira"
def json_relation[:age] = 32
def json_relation[:height] = missing
def json_relation[:pets, :[], 1] = "dog"
def json_relation[:pets, :[], 2] = "rabbit"

def result = json_string[json_relation]

In the end, result contains the JSON string:

{
  "name": "Amira",
  "age": 32,
  "height": null,
  "pets": [
    "dog",
    "rabbit"
  ]
}

You can find more information about importing, exporting, and managing JSON data in our JSON how-to guide.