Forum Discussion
Helper IIIParsing 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.
Super UserHello 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
Helper IIIThanks 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.
- jennratten
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.