Skip to content
REL
HOW-TO GUIDES
Data Import and Export
JSON Export

JSON Export

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

Goal

By following this guide, you will learn how to export JSON (opens in a new tab) data into a database using Rel.

This guide complements the JSON Import and the Working With JSON Data guides, where you can find all the relevant information about supported JSON data types and representations as well as how to work with them.

See the Data Import and Export guides for more information on how to import and export other types of data.

Exporting JSON Data

The main way to export JSON data from the system is through the built-in Rel relation export_json, which only supports data represented using the data-defined schema (opens in a new tab).

💡

Exporting data using the general schema is not currently supported.

The export process begins when used with the control relation export:

def config = ...
def export = export_json[config]

The relation config — which is an arbitrary name — is used to define all JSON export configurations. For details on all the available options, see Export Options.

Getting Started

Consider the following example that exports the relation my_json to the cloud:

🔎

Unlike importing, exporting JSON data to local files is not currently supported.

def my_json[:name] = "John"
def my_json[:age] = 12
def my_json[:date] = 2012-01-01
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = my_json
end
 
def export = export_json[config]

This is the output file:

{
    "name":"John",
    "age":12,
    "date": "2012-01-01"
}

When exporting, you must specify the path and data export options. See the config relation in the example above. Credentials for writing access to the path location might also be needed. See the Accessing the Cloud guide for more details.

Export Options

The export options are the following:

OptionDescription
pathA URL for the JSON data.
dataThe data to be exported. It can be a relation or a JSON formatted string value.
integrationCredentials — if needed — to access the data.
indentIndentation when writing the output data. If not specified, all data are written in a single line to the output.
🔎

Exporting compressed JSON data is not currently supported.

Path

The option path is a string that specifies the location and name of the export file. Currently, this string can point to azure://... (Microsoft Azure) or s3://... (Amazon S3) URLs. See the Accessing the Cloud guide for more details.

Here’s an example:

module config
    def path = "azure://myaccount.blob.core.windows.net/container/file.json"
end

Data

The option data is a string specifying the actual data to be exported. It can be a relation or a JSON formatted string value.

⚠

The path option takes precedence over the data option. If both are present, the data option is ignored.

module config
    def data = "a,b,c\n1,2,3\n4,5,6"
end

Integration

You may need to specify credentials for exporting private JSON data in cloud storage, using the option integration. See the Accessing the Cloud guide for more details.

Indentation

When exporting, the data might not be well-formatted. You can enhance the format by using the indent option.

For instance, the following code specifies using four-space indentation for formatting the output data:

def my_json[:name] = "John"
def my_json[:age] = 12
def my_json[:date] = 2012-01-01
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = my_json
    def indent = 4
end
 
def export = export_json[config]

This output file looks like this:

{
    "name": "John",
    "age": 12,
    "date": "2012-01-01"
}

Export Examples

This section presents the most common JSON export use cases.

Exporting Nested Data

You can export nested JSON data. For example, consider the following data, where you have a car object inside the overall person object:

def json[:name] = "John"
def json[:age] = 35
def json[:car, :brand] = "Mazda"
def json[:car, :color] = "red"
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = json
    def indent = 4
end
 
def export = export_json[config]

This this the output file:

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

You can have additional nesting. Here’s 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
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = json
    def indent = 4
end
 
def export = export_json[config]

This gives the following 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

JSON supports arrays. This section covers how to export them.

Consider the following example where you have an array called cars containing 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
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = json
    def indent = 4
end
 
def export = export_json[config]

The :[] marker is required to ensure that the JSON data appear as an array in the output. Executing the code above gives you the following 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 also have arrays inside arrays. Here’s an 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
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = json
    def indent = 4
end
 
def export = export_json[config]

This gives the following output:

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

Exporting Time Data Types

Rel supports various data types, including time-related data types that enable you to specify dates, specific points in time, and periods.

🔎

Rel’s time-related data types are exported using their string representation.

When exporting a Date, DateTime, or time periods as part of your JSON data, a string representation is used following the ISO-8601 format (opens in a new tab).

The following table summarizes how these Rel time-related data types translate into their corresponding string representation:

Rel Data TypeString RepresentationExample (n=2)
DateYYYY-MM-DD"2012-01-01"
DateTime"YYYY-MM-DDThh:mm:ss<timezone>""2012-01-01T12:00:00Z"
^Year[n]"PnY""P2Y"
^Month[n]"PnM""P2M"
^Week[n]"PnW""P2W"
^Day[n]"PnD""P2D"
^Hour[n]"PTnH""PT2H"
^Minute[n]"PTnM""PT2M"
^Second[n]"PTnHS""PT2S"
^Millisecond[n]"PT0.00nS""PT0.002S"
^Microsecond[n]"PT0.00000nS""PT0.000002S"
^Nanosecond[n]"PT0.00000000nS""PT0.000000002S"

For example, consider the relation my_year that contains some Year value:

// read query
 
def my_year = ^Year[5]
 
def output:rel = my_year
def output:json = json_string[my_year]

The output shows both: the string representation in the ISO-8601 format when the data type is transformed to JSON as well as the Rel representation. The same transformation happens when exporting it:

def my_json[:year] = ^Year[5]
 
module config
    def path = "azure://myaccount.blob.core.windows.net/sascontainer/myfile.json"
    def data = my_json
    def indent = 4
end
 
def export = export_json[config]

This gives the following output:

{
    "year": "P5Y"
}

Export Errors

Currently, you can only export JSON data using the data-defined schema via export_json. Exporting using the general schema triggers an error.

Exporting to local files or exporting compressed data is not currently supported and returns an error.

In such cases, the whole transaction is aborted with an error message and the data are not exported. The data export process is atomic. This means that if there is an error in the data, the system does not attempt to export any of the data.

See the Loading Errors guide for more details.

See Also

For more information on supported JSON data types and formats and how to work with them, see the Working With JSON Data concept guides.

See the JSON Import guide for importing JSON and the Data Import and Export guides for more information on how to import and export other types of data.

Was this doc helpful?