Forum Discussion
Parsing JSON Using a Schema
You will probably need to have a version that you can make minor modifications to for each schema. You can create a master schema map with an alignment to the required transformations.
jennratten Thanks for sharing this pbix. I've had a look through but can't see where you are using a JSON schema to parse a JSON string into columns. Not sure if that's because I don't have credentials for some of the datasources.
In my example I have a table with column "Schema", this holds the strings of the JSON schemas for all the forms that are used. A simple example of a string is like this:
[
{
"type": "Date",
"icon": "calendar",
"colour": "#84aced",
"label": "",
"description": "",
"readOnly": false,
"required": false,
"placeholder": "",
"hideExpression": "",
"settings": {
"minDate": "",
"maxDate": ""
},
"id": "Date1",
"valid": true
},
{
"type": "Location",
"icon": "street-view",
"colour": "#fca52a",
"label": "",
"description": "",
"readOnly": false,
"required": false,
"placeholder": "",
"hideExpression": "",
"settings": {
"options": ""
},
"id": "Location1",
"valid": true
}
]
In another table, I have the records of all the forms submitted, and the values submitted are represented as a JSON string following the schema of the form used and are stored in a column. The results string could look like this for the schema above:
{
"Date1": "2019-01-23T00:00:00+00:00"
}
Notice how it doesn't contain the Location, as that's not a mandatory field in the form. So if I used PQs Parse JSON function, it would only parse the "Date1" field, and if a new form got submitted with the "Location1" field, the query wouldn't catch it when the dataset is refreshed in the service.
I'm looking for a way to extract all the fields from the schema (they all have the 'id' tag) and use them as column names when parsing the JSON results. Hope that makes sense!
Hello again - Yes, this makes sense. Thanks for providing some sample data. Please add the script below as a new blank query. You can grab the list of fields expected from the schema and the use it to expand the records returned in the forms json like this.
SCRIPT
let
// JSON Schema
Source =
{
[
type = "Date",
settings = [
minDate = "",
maxDate = ""
],
id = "Date1",
valid = true
],
[
type = "Location",
settings = [
options = ""
],
id = "Location1",
valid = true
]
},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "icon", "colour", "label", "description", "readOnly", "required", "placeholder", "hideExpression", "settings", "id", "valid"}, {"type", "icon", "colour", "label", "description", "readOnly", "required", "placeholder", "hideExpression", "settings", "id", "valid"}),
#"Expanded settings" = Table.ExpandRecordColumn(#"Expanded Column1", "settings", {"minDate", "maxDate", "options"}, {"settings.minDate", "settings.maxDate", "settings.options"}),
Schema = Table.TransformColumnTypes(#"Expanded settings",{{"type", type text}, {"icon", type text}, {"colour", type text}, {"label", type any}, {"description", type any}, {"readOnly", type logical}, {"required", type logical}, {"placeholder", type any}, {"hideExpression", type any}, {"settings.minDate", type any}, {"settings.maxDate", type any}, {"settings.options", type any}, {"id", type text}, {"valid", type logical}}),
// Sample Data
Data =
{
[
Date1 = "2019-01-23T00:00:00+00:00"
],
[
Date1 = "2019-01-23T00:00:00+00:00"
],
[
Date1 = "2019-01-23T00:00:00+00:00"
],
[
Date1 = "2019-01-23T00:00:00+00:00",
Location1 = "ABC"
]
},
#"Converted to Table1" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandRecords = Table.ExpandRecordColumn (
#"Converted to Table1",
"Column1",
// List of fields to expand
Schema[id]
)
in
ExpandRecordsRESULT
Thanks jennratten . This looks like a good solution for my example. The trouble I have is, in the real database, there are hundreds of different form templates, all with different levels of nesting and different field names. So I won't be able to type those all out in a query. I think given the "id" field is consistent across all form templates, that should make it easier. But what i'm not sure about is how to handle all the different nesting.
You will probably need to have a version that you can make minor modifications to for each schema. You can create a master schema map with an alignment to the required transformations.