GoogleCloudPlatform
Support for Nested Schema in MongoDB To BQ template
IHAC where we are reading some of the MongoDB collection with nested schema. We see that existing templates fails to handle the nested schema , here is the mogodb schema
{ "$schema": "https://json-schema.org/draft/2020-12/schema", "type": "object", "required": [ "_id", "documenttype", "name" ], "properties": { "_id": { "type": "string" }, "_realmMasterData": { "type": "string" }, "_schemaVersion": { "$ref": "#/$defs/Double" }, "accountingunits": { "anyOf": [ { "type": "array", "items": { "type": "object", "required": [ "code", "description", "saleslocation" ], "properties": { "code": { "type": "string" }, "description": { "type": "string" }, "saleslocation": { "type": "string" }, "subsegment": { "type": "string" }, "subsubsegment": { "type": "string" } } } }, { "type": "null" } ] }, "businesslines": { "type": "array", "items": { "type": "string" } }, "businessprocess": { "type": "string" }, "cityofassignments": { "type": "null" }, "coordinates": { "anyOf": [ { "type": "null" }, { "type": "object", "required": [ "mapquadrant" ], "properties": { "mapquadrant": { "anyOf": [ { "type": "object", "required": [ "latitude", "longitude" ], "properties": { "latitude": { "type": "integer" }, "longitude": { "type": "integer" } } }, { "type": "null" } ] } } } ] }, "country": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" } } }, "countryofassignments": { "type": "array", "items": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" }, "states": { "type": "array", "items": { "type": "object", "required": [ "cities", "name" ], "properties": { "cities": { "type": "array", "items": { "type": "string" } }, "name": { "type": "string" } } } } } } }, "createdby": { "type": "string" }, "createddate": { "$ref": "#/$defs/Date" }, "documenttype": { "type": "string" }, "erpsystem": { "type": "string" }, "externalopslocations": { "type": "array", "items": { "type": "object", "required": [ "opslocationcode", "source" ], "properties": { "opslocationcode": { "type": "string" }, "source": { "type": "string" } } } }, "externalsystemmapping": { "type": "array", "items": { "type": "object", "required": [ "segment", "systemname" ], "properties": { "segment": { "type": "string" }, "systemname": { "type": "string" } } } }, "fdpdeploymentstatus": { "type": "string" }, "flmbusinessprocess": { "type": "string" }, "functionallocation": { "type": "array", "items": { "type": "object", "required": [ "code", "name", "subsegmentcode" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" }, "subsegmentcode": { "type": "string" } } } }, "hrorgunits": { "anyOf": [ { "type": "array", "items": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" } } } }, { "type": "null" } ] }, "incidentreportingnodes": { "type": "array", "items": { "type": "object", "required": [ "code", "description", "saleslocation", "subbusinessline", "subsubbusinessline" ], "properties": { "code": { "type": "string" }, "description": { "type": "string" }, "saleslocation": { "type": "string" }, "subbusinessline": { "type": "string" }, "subsubbusinessline": { "type": "string" } } } }, "isdcenabled": { "type": "boolean" }, "isdeleted": { "type": [ "null", "boolean" ] }, "isremotedistrict": { "type": "boolean" }, "issapdeployed": { "type": "boolean" }, "issharedpool": { "type": "boolean" }, "lastmodifiedby": { "type": "string" }, "lastmodifieddate": { "$ref": "#/$defs/Date" }, "legacypath": { "type": "string" }, "legalentities": { "type": "array", "items": { "type": "string" } }, "maintenancelocations": { "type": "array", "items": { "type": "object", "required": [ "code", "description", "isprimary", "saleslocation", "subsegment", "subsubsegment" ], "properties": { "code": { "type": "string" }, "description": { "type": "string" }, "isprimary": { "type": "boolean" }, "saleslocation": { "type": "string" }, "subsegment": { "type": "string" }, "subsubsegment": { "type": "string" } } } }, "managementcountries": { "type": "array", "items": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" } } } }, "name": { "type": "string" }, "ofsstores": { "anyOf": [ { "type": "array", "items": { "type": "object", "required": [ "accountingunit", "code", "companycode", "name", "status" ], "properties": { "accountingunit": { "type": "string" }, "code": { "type": "string" }, "companycode": { "type": "string" }, "name": { "type": "string" }, "status": { "type": "string" } } } }, { "type": "null" } ] }, "path": { "type": [ "string", "null" ] }, "plant": { "type": "array", "items": { "type": "object", "required": [ "code", "isprimary", "name" ], "properties": { "addresses": { "anyOf": [ { "type": "array", "items": { "type": "object", "required": [ "_id", "city", "country", "isprimary", "name", "postalcode", "state", "street" ], "properties": { "_id": { "type": "string" }, "city": { "type": "string" }, "country": { "type": "string" }, "isprimary": { "type": "boolean" }, "name": { "type": "string" }, "planttaxid": { "type": "string" }, "postalcode": { "type": "string" }, "state": { "type": "string" }, "street": { "type": "string" } } } }, { "type": "null" } ] }, "code": { "type": "string" }, "companycode": { "type": [ "string", "null" ] }, "isprimary": { "type": "boolean" }, "name": { "type": "string" }, "sourcelocation": { "anyOf": [ { "type": "array", "items": { "type": "object", "required": [ "code", "description" ], "properties": { "code": { "type": "string" }, "description": { "type": "string" } } } }, { "type": "null" } ] }, "subsegment": { "type": "array", "items": { "type": "string" } } } } }, "remoteopslocations": { "anyOf": [ { "type": "array", "items": { "type": "object", "required": [ "code", "isprimary", "name" ], "properties": { "code": { "type": "string" }, "isprimary": { "type": "boolean" }, "name": { "type": "string" } } } }, { "type": "null" } ] }, "resourceplanningnode": { "type": "array", "items": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" } } } }, "saleslocations": { "anyOf": [ { "type": "array", "items": { "type": "string" } }, { "type": "null" } ] }, "salesorg": { "anyOf": [ { "type": "object", "required": [], "properties": { "distributionchannel": { "type": "string" }, "division": { "type": "string" }, "legalentity": { "type": "string" }, "legalentityname": { "type": [ "string", "null" ] } } }, { "type": "null" } ] }, "subbusinessline": { "type": "array", "items": { "anyOf": [ { "type": "string" }, { "type": "object", "required": [ "businessprocess", "code", "name", "subsubbusinessline" ], "properties": { "businessprocess": { "type": "string" }, "code": { "type": "string" }, "name": { "type": "string" }, "subsubbusinessline": { "type": "array", "items": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" } } } } } } ] } }, "subgeomarket": { "type": "array", "items": { "type": "string" } }, "subsegment": { "type": "array", "items": { "anyOf": [ { "type": "string" }, { "type": "object", "required": [ "code", "name", "subsubsegment" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" }, "subsubsegment": { "type": "array", "items": { "type": "object", "required": [ "code", "name" ], "properties": { "code": { "type": "string" }, "name": { "type": "string" } } } } } } ] } }, "tags": { "type": "array", "items": { "type": "string" } }, "timezone": { "type": "object", "required": [ "displayname", "standardname" ], "properties": { "displayname": { "type": "string" }, "standardname": { "type": "string" } } }, "uom": { "type": "string" }, "wkeid": { "type": "string" }, "xrefid": { "type": "string" } }, "$defs": { "Double": { "oneOf": [ { "type": "number" }, { "type": "object", "properties": { "$numberDouble": { "enum": [ "Infinity", "-Infinity", "NaN" ] } } } ] }, "Date": { "type": "object", "properties": { "$date": { "type": "string", "format": "date-time" } }, "required": [ "$date" ], "additionalProperties": false } } }
