Converting JSON-LD into database structures
Last updated
Last updated
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.
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:
If you are going to write an interpreter, you’ll want a minimum of three fields:
Schema – this will hold the schema you are going to work with.
Non-nested schema – this will hold a translation of the schema into a simple JSON array.
DB tables – this will hold a list of all of the database tables you’ll need to create.
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.
Below we will show all the initial variable setups we are using and explain each.
Initial Value
Explanation
~json = json;
This is just taking the json passed in and putting in into a local variable for readability.
~path = path ;
Take the json path string and put it into a local variable for readability.
~extendedPath = Case ( IsEmpty ( ~path ) ; "" ; ~path & "." ) ;
Add a dot to the string for passing into next pass of recursion, unless string was not passed in.
Now we need a few loop control variables.
Initial Value
Explanation
~keys = JSONListKeys(~json ; "");
This calls a standard function to get our list of JSON keys from the object.
~keyCount = Case(Left(~keys ; 1) = "?" or IsEmpty (~keys) ; 0 ; ValueCount (~keys));
Get the count of the keys for looping.
~n = 1 ;
This is the loop control variable.
Now we need to add a few more to initialize variables that are reset during each loop iteration.
~thisKey = "";
The current key we are on.
~thisValue = "";
The current value associated with the current key.
~isTable = "";
Boolean indicating whether the current key is a table name.
~isSet = "";
Boolean indicating whether the current value is a @set array.
~isArrayElement = ";
Boolean indicating whether the current value is an element of an indexed array.
~isJSON = "";
Boolean indicating whether the current value is a JSON object.
~result = ""
The result which will eventually be passed out of the function.
~fields = "" ;
The fields in the current table.
~extendedPath = Case ( IsEmpty ( ~path ) ; "" ; ~path & "." ) ;
The full path of the table, including parent tables, separated by periods “.”.
~pathList = Substitute ( ~path ; "." ; ¶ ) ;
A list of all of the paths.
~lastInPath = GetValue ( ~pathList ; ValueCount ( ~pathList ) ) ;
The very last line of the path list.
~thisTable = Case (
IsEmpty ( ~path ) and Left ( JSONGetElement ( ~json ; "@set" ) ; 1 ) ≠ "?" ; JSONGetElement ( ~json ; "@type" );
not IsEmpty ( JSONGetElement ( ~json ; "@set" ) ) and Left ( JSONGetElement ( ~json ; "@set" ) ; 1 ) ≠ "?" ; JSONGetElement ( ~json ; "@type" ) ;
GetValue ( ~pathList ; ValueCount ( ~pathList ) ) ) ;
The current table being examined during this function’s run.
~parentTable = Case (
IsEmpty ( ~lastInPath ) ; "" ;
~lastInPath = ~thisTable and ValueCount ( ~pathList ) > 1 ; GetValue ( ~pathList ; ValueCount ( ~pathList ) - 1 ) ;
~lastInPath = ~thisTable ; "" ;
~lastInPath
) ;
The current table’s parent.
~thisPath = Case (
~parentTable = ~thisTable; ~path ;
~path = ~thisTable ; ~path ;
~lastInPath = ~thisTable ; ~path ;
~extendedPath & ~thisTable
) ;
The current table’s path.
~baseJSON = JSONSetElement ( "" ;
[ "table" ; ~thisPath ; JSONString ]
)
The heading of each of the resulting arrays. This could also include parent table and other information.
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 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.
~thisKey = GetValue ( ~keys ; ~n ) ;
~thisValue = JSONGetElement ( ~json ; ~thisKey ) ;
~isTable = Left ( JSONGetElement ( ~thisValue ; "@set" ) ; 1 ) ≠ "?" and not IsEmpty ( JSONGetElement ( ~thisValue ; "@set" ) ) ;
~isSet = ~thisKey = "@set" ;
~isArrayElement = IsEmpty (
Substitute ( ~thisKey ;
[ 0 ; "" ] ;
[ 1 ; "" ] ;
[ 2 ; "" ] ;
[ 3 ; "" ] ;
[ 4 ; "" ] ;
[ 5 ; "" ] ;
[ 6 ; "" ] ;
[ 7 ; "" ] ;
[ 8 ; "" ] ;
[ 9 ; "" ]
)
) ;
These values are here for handling the various aspects of the loop iteration.
~isJSON = Left ( JSONFormatElements ( ~thisValue ) ; 1 ) ≠ "?" and not IsEmpty ( ~thisValue ) ;
If there is nothing but numerical digits, assume we’ve got a number.
~nextIteration = Case (
~isJSON ; ConvertToNonNestedJSON ( ~thisPath ; ~thisValue ) ;
//else
""
) ;
If we are going to have a next iteration, go ahead and get it.
~nextIterationFields = Case (
~isJSON; ConvertToNonNestedJSON ( ~thisPath ; ~thisValue ) ;
""
) ;
~fields = Case (
not IsEmpty ( ~nextIterationFields ) ;
Let ( [
~error1 = Left (JSONFormatElements ( ~fields ) ; 1 ) ≠ "[" and not IsEmpty ( ~fields ) ;
~error2 = Left (JSONFormatElements ( ~nextIterationFields ) ; 1 ) ≠ "[" and not IsEmpty ( ~nextIterationFields );
~combinedArrays = Left ( ~fields ; Length ( ~fields ) - 1 ) & "," & Right ( ~nextIterationFields ; Length ( ~nextIterationFields ) - 1 )
];
Case (
~error1 or ~error2 ; "error" ;
IsEmpty ( ~fields ) ; ~nextIterationFields ;
IsEmpty ( ~nextIterationFields ) ; ~fields ;
~combinedarrays
)
) ;
Let ( [
#theArray = Case (
IsEmpty ( ~fields )
or
~fields = "{}" ; "[]" ;
~fields
) ;
#index = Case ( IsEmpty ( ~fields ) ; 0 ; ValueCount ( JSONListKeys ( ~fields ; "" ) ) ) ;
#error = Case ( Left ( #index ; 1 ) = "?" ; "Invalid Array" ) ;
#thisField = JSONSetElement ( ~baseJSON ;
[ "field" ; ~thisKey ; JSONString ] ;
[ "type" ; ~thisValue ; JSONString ]
)
];
Case (
~thisKey = "@type" or ~thisKey = "@context" ; ~fields ;
JSONSetElement ( #theArray ; #index ; #thisField ; JSONObject )
)
)
);
Get any fields passed back by the next iteration. The result is passed back as json with lines and fields keys.
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.
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:
parent_table
sub_table
field_name
field_type
Currencies
country_fk
Integer
Currencies
id
id
Currencies
name
String
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:
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.
parent_table
sub_table
field_name
field_type
Currency
CurrencyCodes
id
Integer
Currency
CurrencyCodes
currency_code
String
Currency
CurrencyCodes
currency_fk
Integer
This allows us to create a table structure that links the Currency table to its subtable CurrencyCodes, as shown below:
What results is an array of each of the fields with its table, object identifier (optional), and the field type, as shown below:
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:
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.
