RelationalAI is named a Cool Vendor in the May 2022 Gartner Cool Vendors in Augmented Data Management report.

Handling JSON Data in Rel

Martin Bravenboer and Abdul Zreika

24 May 2022

less than a minute read

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"
]

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:

Relation: result

:address:city"Seattle
:address:state"WA"
:first_name"John"
:last_name"Smith
:phone:[]1:number 206456
:phone:[]1:type"home"
:phone:[]2:number 206123
:phone:[]2:type"work"

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

After loading, the relation result contains the JSON data:

Relation: result

:last_name"Smith"
:children's ages:[]110
:children's ages:[]212
:children's ages:[]33
:first_name"John"

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.

Related Posts

Get Early Access

Join our community, keep up to date with the latest developments in our monthly newsletter, and get early access to RelationalAI.