Converting JSON-LD into database structures

If the JSON-LD schema is written correctly, you can create a system to interpret the schema and create a table map of the schema. To prove this theory, we’ve created an open Filemaker addon that you can freely add to any Filemaker database to do just that.

Let’s go through the process. We will start with a very simple schema, Currency, as documented at https://grcschema.org/Currency.

Recording the structure

Even though obvious, the first place you’ll need to start is gathering the schema data itself. Websites such as Schema.org and GRCSchema.org have well-defined JSON-LD schemas. Below is the Currency schema from GRCSchema.org.

Normally, websites such as these have various options of looking at the schema, such as this one that presents schema, structure, and visualized. You can skip any visualization, and the structure view provides more information than you’ll need. So we’ll go with the sample schema, as shown here:

{
 "@context": "http://grcschema.org/",
 "@type": "Currency",
 "CoreMetaData": {
 "@type": "CoreMetaData",
 "date_created": "Date",
 "date_modified": "Date",
 "created_by": "Integer",
 "modified_by": "Integer",
 "live_status": "Boolean",
 "checksum": "Integer",
 "validated": "Boolean"
 },
 "CurrencyCodes": {
 "@type": "CurrencyCodes",
 "@set": [
 {
 "@type": "CurrencyCode",
 "id": "Integer",
 "currency_code": "String",
 "currency_fk": "Integer"
 }
 ]
 },
 "CurrencyCountries": {
 "@type": "CurrencyCountries",
 "@set": [
 {
 "@type": "CurrencyCountry",
 "name": "String",
 "id": "Integer",
 "country_fk": "Integer",
 "currency_fk": "Integer"
 }
 ]
 },
 "CurrencyNames": {
 "@type": "CurrencyNames",
 "@set": [
 {
 "@type": "CurrencyName",
 "id": "Integer",
 "currency_name": "String",
 "currency_fk": "Integer"
 }
 ]
 },
 "CurrencySymbols": {
 "@type": "CurrencySymbols",
 "@set": [
 {
 "@type": "CurrencySymbol",
 "id": "Integer",
 "currency_symbol": "Char",
 "currency_fk": "Integer"
 }
 ]
 },
 "id": "Integer"
}

Creating the tables and fields to hold the data

If you are going to write an interpreter, you’ll want a minimum of three fields:

  1. Schema – this will hold the schema you are going to work with.

  2. Non-nested schema – this will hold a translation of the schema into a simple JSON array.

  3. DB tables – this will hold a list of all of the database tables you’ll need to create.

The methodology

We explain the methodology for each step below. The methodology uses a custom While function to step through each line of the schema that repeats logic while the condition is true, then returns the result. The format of the While function is shown below:

While ( [ initialVariable ] ; condition ; [ logic ] ; result )

The parameters for the While function are:

  • initialVariable - variable definitions that will be available to use in the following parameters.

  • condition - a Boolean expression evaluated before each loop iteration. While True, the loop repeats. When False, the loop stops.

  • logic - variable definitions that are evaluated each time the loop is repeated.

  • result - an expression that is returned when the loop stops.

The initial variables

Below we will show all the initial variable setups we are using and explain each.

Now we need a few loop control variables.

Now we need to add a few more to initialize variables that are reset during each loop iteration.

The condition logic

The condition logic for this is pretty simple. Keep looping through the json text until you get to the last line.

~n ≤ ~keyCount ;

The logic used for interpretation

The logic used for the interpretation is based off of what we find in each row of the json being passed. Each of the main things to draw from the JSON are described below.

Handling of the loop iterations

Interpreting @type as a table name

The second element of the JSON-LD schema should be the @type keyword. The @type keyword as the second element in the schema represents the primary table name that should be created. It is linked to the schema by a project ID.

Therefore, there is no special code necessary to determine the primary table name.

For every field in the primary table, this information will be stored in the field parent_table.

Interpreting "@set": [ at the top level as an array

If, at the primary level an @set keyword is found, that denotes what follows will be returned as an array of information with each of the keys denoting the individual fields within the array.

While Currency doesn’t have this pattern, the JSON for returning the stub-list of all Currency records does (see https://grcschema.org/Currencies).

{

"@context": "http://grcschema.org/",

"@type": "Currencies",

"@set": [

{

"country_fk": "Integer",

"id": "Integer",

"name": "String"

}

]

}

The properties are then used to create the field names (field_name) in our Fields table. The field_type is derived from the element’s type.

Your system should then go through each of the element’s keys and create records in your Fields table, as shown below:

Interpreting "propertynamegoeshere" : { as an object

Within our example of Currency, you can see that the 4th line of the schema text is:

"CoreMetaData" : {

This pattern represents a grouped object. In many databases (ours included) the fields of the grouped object are treated as any other field and the is recorded as an object but not turned in to a field in-and-of itself.

In layouts, however, grouped objects are represented as a field set, as shown below:

Interpreting "propertynamegoeshere": { "@set": [ as a subtable

In the example below we can see that within Currency, right after CoreMetaData we have a subtable of CurrencyCodes that follow this pattern.

"CurrencyCodes": {

"@set": [

{

"id": "Integer",

"currency_code": "String",

"currency_fk": "Integer"

}

]

},

That’s our cue that each currency can be assigned multiple currency codes. And hence, a subtable should be created. Another hint, by the way, is that the array shows both an id property and a currency_fk property so that the two tables can be linked together.

In filling out our interpretation database, CurrencyCodes becomes subtable and also tells us that each of the fields below it belong to it.

This allows us to create a table structure that links the Currency table to its subtable CurrencyCodes, as shown below:

The result is a simple JSON array of fields, tables, and field types

What results is an array of each of the fields with its table, object identifier (optional), and the field type, as shown below:

{
 "field" : "checksum",
 "object" : "CoreMetaData",
 "table" : "Currency",
 "type" : "Integer"
},

Once you have converted the JSON-LD into this simple array, you can easily create scripts to re-intrepret the array into SQL table commands, like the one shown below:

CREATE TABLE "Currency" (
"checksum" int,
"created_by" int,
"date_created" date,
"date_modified" date,
"live_status" int,
"modified_by" int,
"validated" int,
"id" int
)

Sample files

We have some sample files to help you through this.

ConvertToNonNested.txt – this is the custom function described above.

SchemaToTableConverter.fmp12 – this is a Filemaker database that is completely open and takes you through each of the steps we’ve described above.

Last updated