Convert JSON Schema / Format to Schema / Format that can be read from SQL Server
-
First, I want to apologise for the shear volume of JSON code posted here.
I would like to convert the following JSON Schema into a format (preferably CSV) that can be queried with SQL Server.
I often use an SQL application called dbForge to do the conversion, but its failing.
{
"name": "FACT_WORK_RECORD",
"description": "FACT_WORK_RECORD",
"version": "1.2",
"updateDttm": "03/02/2022M",
"SCDType":4,
"mappings": [
{
"ELLIPSE": {
"method": "ellipseItem",
"tables": [
{
"database": "adsd_foundation",
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"primaryKey": [
{
"column": "WORK_ORDER"
}
]
},
{
"database": "adsd_foundation",
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"primaryKey": [
{
"column": "WORK_ORDER"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "WORK_ORDER",
"transform": "#1"
},
"right": {
"column": "WORK_ORDER",
"transform": "#1"
},
"operator": "="
}
]
}
},
{
"database": "adsd_data_mart",
"schema": "AZ_DM",
"table": "DIM_DATE",
"primaryKey": [
{
"column": "D_DATE_KEY"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "MCAL_DAY_DT",
"transform": "date(#1)"
},
"right": {
"column": "CLOSED_DT",
"transform": "to_date(#1, 'yyyyMMdd')"
},
"operator": "="
}
]
}
},
{
"database": "adsd_data_mart",
"schema": "AZ_DM",
"table": "DIM_WORK_ORDER",
"primaryKey": [
{
"column": "D_WORK_ORDER_KEY"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "WORK_ORDER",
"transform": "#1"
},
"right": {
"column": "WORK_ORDER",
"transform": "#1"
},
"operator": "="
}
]
}
}
],
"columns": [
{
"column": "F_WORK_RECORD_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "autoGenerate"
},
{
"column": "SYSTEM_OF_RECORD",
"type": "varchar",
"length": 24,
"allowNulls": "No",
"mapType": "staticValue",
"value": "ELLIPSE"
},
{
"column": "D_WORK_ORDER_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "map",
"transform":"nvl(#1,0)",
"source": {
"schema": "AZ_DM",
"table": "DIM_WORK_ORDER",
"column": "D_WORK_ORDER_KEY",
"type": "NUMBER",
"precision": 15,
"scale": 0
}
},
{
"column": "D_DATE_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "map",
"transform":"nvl(#1,0)",
"source": {
"schema": "AZ_DM",
"table": "DIM_DATE",
"column": "D_DATE_KEY",
"type": "NUMBER",
"precision": 15,
"scale": 0
}
},
{
"column": "ACTUAL_COST_INV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_COST_INV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_COST_RLOC",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_COST_RLOC",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_REVENUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_REVENUE",
"type": "NUMBER"
}
},
{
"column": "DIR_REVENUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "DIR_REVENUE",
"type": "NUMBER"
}
},
{
"column": "INDIRECT_INV_AMOUNT",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "INDIRECT_INV_AMT",
"type": "NUMBER"
}
},
{
"column": "NO_TASKS_COMPL",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "N",
"mapType": "map",
"transform":"cast(#1 as int)",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "NO_TASKS_COMPL",
"type": "VARCHAR2"
}
},
{
"column": "PC_COMPLETE",
"type": "NUMERIC",
"precision": 5,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "PC_COMPLETE",
"type": "NUMBER"
}
},
{
"column": "PLAN_STAT_VALUE",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "PLAN_STAT_VAL",
"type": "NUMBER"
}
},
{
"column": "QUOTE_VALUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "QUOTE_VALUE",
"type": "NUMBER"
}
},
{
"column": "TODO_COUNT",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "TODO_COUNT",
"type": "NUMBER"
}
},
{
"column": "TODO_COUNT_OPEN",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "TODO_COUNT_OPEN",
"type": "NUMBER"
}
},
{
"column": "UNITS_COMPLETE",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_COMPLETE",
"type": "NUMBER"
}
},
{
"column": "UNITS_INV_CHARGE",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_INV_CHGE",
"type": "NUMBER"
}
},
{
"column": "UNITS_REQUIRED",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_REQUIRED",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_DURATION_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_DUR_HRS",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_REV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_MATERIAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_MAT_REV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_OTHER_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_OTHER_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_OTHER_REV",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_LABOUR_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_OTH_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_DURATION_HRS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_DUR_HRS",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_HRS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_MATERIAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_MAT_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_OTHER_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_OTHER_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_OTHER_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_TOTAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_TOTAL_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_TOTAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_TOTAL_REV",
"type": "NUMBER"
}
}
]
}
}
]
}I know its a lot to ask, so if someone could even get me started that would also be great.
-
It's not clear exactly what you are trying to achieve, but this is a start, showing how you handle JSON in T-SQL. If your desire is for something more complex, please provide desired output based on the sample JSON provided.
DECLARE @j NVARCHAR(MAX)
= N'{
"name": "FACT_WORK_RECORD",
"description": "FACT_WORK_RECORD",
"version": "1.2",
"updateDttm": "03/02/2022M",
"SCDType":4,
"mappings": [
{
"ELLIPSE": {
"method": "ellipseItem",
"tables": [
{
"database": "adsd_foundation",
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"primaryKey": [
{
"column": "WORK_ORDER"
}
]
},
{
"database": "adsd_foundation",
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"primaryKey": [
{
"column": "WORK_ORDER"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "WORK_ORDER",
"transform": "#1"
},
"right": {
"column": "WORK_ORDER",
"transform": "#1"
},
"operator": "="
}
]
}
},
{
"database": "adsd_data_mart",
"schema": "AZ_DM",
"table": "DIM_DATE",
"primaryKey": [
{
"column": "D_DATE_KEY"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "MCAL_DAY_DT",
"transform": "date(#1)"
},
"right": {
"column": "CLOSED_DT",
"transform": "to_date(#1, ''yyyyMMdd'')"
},
"operator": "="
}
]
}
},
{
"database": "adsd_data_mart",
"schema": "AZ_DM",
"table": "DIM_WORK_ORDER",
"primaryKey": [
{
"column": "D_WORK_ORDER_KEY"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "WORK_ORDER",
"transform": "#1"
},
"right": {
"column": "WORK_ORDER",
"transform": "#1"
},
"operator": "="
}
]
}
}
],
"columns": [
{
"column": "F_WORK_RECORD_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "autoGenerate"
},
{
"column": "SYSTEM_OF_RECORD",
"type": "varchar",
"length": 24,
"allowNulls": "No",
"mapType": "staticValue",
"value": "ELLIPSE"
},
{
"column": "D_WORK_ORDER_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "map",
"transform":"nvl(#1,0)",
"source": {
"schema": "AZ_DM",
"table": "DIM_WORK_ORDER",
"column": "D_WORK_ORDER_KEY",
"type": "NUMBER",
"precision": 15,
"scale": 0
}
},
{
"column": "D_DATE_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "map",
"transform":"nvl(#1,0)",
"source": {
"schema": "AZ_DM",
"table": "DIM_DATE",
"column": "D_DATE_KEY",
"type": "NUMBER",
"precision": 15,
"scale": 0
}
},
{
"column": "ACTUAL_COST_INV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_COST_INV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_COST_RLOC",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_COST_RLOC",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_REVENUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_REVENUE",
"type": "NUMBER"
}
},
{
"column": "DIR_REVENUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "DIR_REVENUE",
"type": "NUMBER"
}
},
{
"column": "INDIRECT_INV_AMOUNT",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "INDIRECT_INV_AMT",
"type": "NUMBER"
}
},
{
"column": "NO_TASKS_COMPL",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "N",
"mapType": "map",
"transform":"cast(#1 as int)",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "NO_TASKS_COMPL",
"type": "VARCHAR2"
}
},
{
"column": "PC_COMPLETE",
"type": "NUMERIC",
"precision": 5,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "PC_COMPLETE",
"type": "NUMBER"
}
},
{
"column": "PLAN_STAT_VALUE",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "PLAN_STAT_VAL",
"type": "NUMBER"
}
},
{
"column": "QUOTE_VALUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "QUOTE_VALUE",
"type": "NUMBER"
}
},
{
"column": "TODO_COUNT",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "TODO_COUNT",
"type": "NUMBER"
}
},
{
"column": "TODO_COUNT_OPEN",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "TODO_COUNT_OPEN",
"type": "NUMBER"
}
},
{
"column": "UNITS_COMPLETE",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_COMPLETE",
"type": "NUMBER"
}
},
{
"column": "UNITS_INV_CHARGE",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_INV_CHGE",
"type": "NUMBER"
}
},
{
"column": "UNITS_REQUIRED",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_REQUIRED",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_DURATION_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_DUR_HRS",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_REV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_MATERIAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_MAT_REV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_OTHER_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_OTHER_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_OTHER_REV",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_LABOUR_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_OTH_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_DURATION_HRS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_DUR_HRS",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_HRS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_MATERIAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_MAT_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_OTHER_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_OTHER_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_OTHER_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_TOTAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_TOTAL_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_TOTAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_TOTAL_REV",
"type": "NUMBER"
}
}
]
}
}
]
}';
SELECT Name = JSON_VALUE(@j, '$.name')
,Description = JSON_VALUE(@j, '$.description')
,Version = JSON_VALUE(@j, '$.version'); -
Hey Phil,
Thanks for reaching out.
My apologies for not be clear.
I copied and pasted your modified code in my dbForge SQL application and I got the following results:
This somehow doesn't look right.
I should have pointed out that this JSON code was given to me to without any detailed explanation as to what it ought to look like.
I'm just going on the code.
-
Once again, I'm being a numbty - I think you make me nervous hahahahahah
Anyway, I see you've just selected Name and Description for my benefit, as it's not in the original code.
Therefore, can you let me know how it would look you did a select * please.
-
JSON's infinite flexibility is also its downfall in cases like this. There is no SELECT * with JSON data, because the data can be very hierarchical.
If you paste the JSON into a 'visualiser' (http://jsoneditoronline.org/#left=local.takeda&right=local.safepu, for example), you can see the hierarchy:
I expanded out a single node, so you can see that there multiple parent-child hierarchies in there.
Can you therefore see that getting a single SELECT query to give you all of this data in a reasonable form is practically impossible?
-
I recommend that you use the visualiser to decide on which columns to include in one or more row/column datasets you wish to extract and take it from there.
-
You have Json nested at least 9-levels deep with something like 31 different objects/attributes.
What do you want to return and how? Do you want the data flattened (in which case you'll get lots of duplicates at higher levels)? Do you want to return multiple results sets of more normalized data?
What are going to do with this data? It looks, somewhat ironically, like SQL relationships and/or mapping transformed into JSON, presumably for some tool. Are you trying to reverse-engineer this reverse-engineering?
-
ratbak
Sorry for the delayed response
What do you want to return and how? Do you want the data flattened (in which case you'll get lots of duplicates at higher levels)? Do you want to return multiple results sets of more normalized data?
I would like the data flattened.
It looks, somewhat ironically, like SQL relationships and/or mapping transformed into JSON, presumably for some tool.
I think you hit the nail on the head here. I believe it to transformed into JSON to be viewed with Visual Studio.
Hi Phil
I recommend that you use the visualiser to decide on which columns to include in one or more row/column datasets you wish to extract and take it from there.
I will check it out.
-
Ratpak
ratbak wrote:Do you want the data flattened (in which case you'll get lots of duplicates at higher levels)? Do you want to return multiple results sets of more normalized data?
Are you able to help with that?
- carlton 84646 wrote:
Ratpak
ratbak wrote:Do you want the data flattened (in which case you'll get lots of duplicates at higher levels)? Do you want to return multiple results sets of more normalized data?
Are you able to help with that?
You need to look at the data and decide on the rows and columns you wish to extract, bearing in mind all of the relationships and hierarchies which you can see there. Ideally, post an example of what you want to see (just a screenshot from something you've mocked up in Excel would suffice).
Once you are at that point, we can help.
-
Hi Phil,
I have the tables and columns that I would like to extract, see attached. Ideally, I would like the tables to extracted as individual CSVs.
This request might need someone experienced with Excel - as I can't imagine someone actually copying and pasting the tables and columns into individual csv tables. For example, there is a table called AZ_FND_MSF010, which has multiple columns with each column having its own attributes e.g. DataType. I would need tables and column(with attributes) extracted into a single CSV
Anyway, is this someone you could help me with?
-
For now, forget about the Excel bit. There are several ways of getting your query results out, once the query has been written.
If I, or someone else, show you how to get the first few columns, that should get you on the way to getting this. It's going to get quite long and intricate, I'm afraid.
Where does 'TABLE_CATALOG' come from, by the way? How about ORDINAL_POSITION?
-
Hi Phil,
That would be great.
You can forget about the TABLE_CATALOG
The ordinal_position represents the place a column appears in a table, which we can forget as I'm trying to work with SQL
Thanks
-
Please try this to get you started:
DROP TABLE IF EXISTS #SomeJSON;
CREATE TABLE #SomeJSON
(
J NVARCHAR(MAX) NOT NULL
);
INSERT #SomeJSON
(
J
)
VALUES
(N'{
"name": "FACT_WORK_RECORD",
"description": "FACT_WORK_RECORD",
"version": "1.2",
"updateDttm": "03/02/2022M",
"SCDType":4,
"mappings": [
{
"ELLIPSE": {
"method": "ellipseItem",
"tables": [
{
"database": "adsd_foundation",
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"primaryKey": [
{
"column": "WORK_ORDER"
}
]
},
{
"database": "adsd_foundation",
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"primaryKey": [
{
"column": "WORK_ORDER"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "WORK_ORDER",
"transform": "#1"
},
"right": {
"column": "WORK_ORDER",
"transform": "#1"
},
"operator": "="
}
]
}
},
{
"database": "adsd_data_mart",
"schema": "AZ_DM",
"table": "DIM_DATE",
"primaryKey": [
{
"column": "D_DATE_KEY"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "MCAL_DAY_DT",
"transform": "date(#1)"
},
"right": {
"column": "CLOSED_DT",
"transform": "to_date(#1, ''yyyyMMdd'')"
},
"operator": "="
}
]
}
},
{
"database": "adsd_data_mart",
"schema": "AZ_DM",
"table": "DIM_WORK_ORDER",
"primaryKey": [
{
"column": "D_WORK_ORDER_KEY"
}
],
"join": {
"type": "LEFT OUTER",
"table": "ADS_FND_MSF620",
"conditions": [
{
"left": {
"column": "WORK_ORDER",
"transform": "#1"
},
"right": {
"column": "WORK_ORDER",
"transform": "#1"
},
"operator": "="
}
]
}
}
],
"columns": [
{
"column": "F_WORK_RECORD_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "autoGenerate"
},
{
"column": "SYSTEM_OF_RECORD",
"type": "varchar",
"length": 24,
"allowNulls": "No",
"mapType": "staticValue",
"value": "ELLIPSE"
},
{
"column": "D_WORK_ORDER_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "map",
"transform":"nvl(#1,0)",
"source": {
"schema": "AZ_DM",
"table": "DIM_WORK_ORDER",
"column": "D_WORK_ORDER_KEY",
"type": "NUMBER",
"precision": 15,
"scale": 0
}
},
{
"column": "D_DATE_KEY",
"type": "int",
"allowNulls": "No",
"mapType": "map",
"transform":"nvl(#1,0)",
"source": {
"schema": "AZ_DM",
"table": "DIM_DATE",
"column": "D_DATE_KEY",
"type": "NUMBER",
"precision": 15,
"scale": 0
}
},
{
"column": "ACTUAL_COST_INV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_COST_INV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_COST_RLOC",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_COST_RLOC",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_REVENUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "ACT_REVENUE",
"type": "NUMBER"
}
},
{
"column": "DIR_REVENUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "DIR_REVENUE",
"type": "NUMBER"
}
},
{
"column": "INDIRECT_INV_AMOUNT",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "INDIRECT_INV_AMT",
"type": "NUMBER"
}
},
{
"column": "NO_TASKS_COMPL",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "N",
"mapType": "map",
"transform":"cast(#1 as int)",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "NO_TASKS_COMPL",
"type": "VARCHAR2"
}
},
{
"column": "PC_COMPLETE",
"type": "NUMERIC",
"precision": 5,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "PC_COMPLETE",
"type": "NUMBER"
}
},
{
"column": "PLAN_STAT_VALUE",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "PLAN_STAT_VAL",
"type": "NUMBER"
}
},
{
"column": "QUOTE_VALUE",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "QUOTE_VALUE",
"type": "NUMBER"
}
},
{
"column": "TODO_COUNT",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "TODO_COUNT",
"type": "NUMBER"
}
},
{
"column": "TODO_COUNT_OPEN",
"type": "NUMERIC",
"precision": 10,
"scale": 0,
"allowNulls": "Y",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "TODO_COUNT_OPEN",
"type": "NUMBER"
}
},
{
"column": "UNITS_COMPLETE",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_COMPLETE",
"type": "NUMBER"
}
},
{
"column": "UNITS_INV_CHARGE",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_INV_CHGE",
"type": "NUMBER"
}
},
{
"column": "UNITS_REQUIRED",
"type": "NUMERIC",
"precision": 9,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF620",
"column": "UNITS_REQUIRED",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_DURATION_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_DUR_HRS",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_LABOUR_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_LAB_REV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_MATERIAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_MAT_REV",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_OTHER_COST",
"type": "NUMBER"
}
},
{
"column": "ACTUAL_OTHER_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "ACT_OTHER_REV",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_LABOUR_HOURS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "CALCULATED_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "CALC_OTH_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_DURATION_HRS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_DUR_HRS",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_EQUIPMENT_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_EQUIP_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_HRS",
"type": "NUMERIC",
"precision": 11,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_HRS",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_LABOUR_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_LAB_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_MATERIAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_MAT_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_MATERIAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_MAT_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_OTHER_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_OTHER_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_OTHER_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_OTHER_REV",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_TOTAL_COST",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_TOTAL_COST",
"type": "NUMBER"
}
},
{
"column": "ESTIMATED_TOTAL_REV",
"type": "NUMERIC",
"precision": 21,
"scale": 2,
"allowNulls": "N",
"mapType": "map",
"source": {
"schema": "AZ_FH_ELLIPSE",
"table": "ADS_FND_MSF621",
"column": "EST_TOTAL_REV",
"type": "NUMBER"
}
}
]
}
}
]
}' );
--_________________________________________________________________________________________________________________________________________________________________
SELECT Name = JSON_VALUE(sj.J, '$.name')
,Description = JSON_VALUE(sj.J, '$.description')
,Version = JSON_VALUE(sj.J, '$.version')
,c1.ColumnName
,c1.Length
,c1.Type
,c1.AllowNulls
FROM #SomeJSON sj
OUTER APPLY
OPENJSON(sj.J, '$.mappings[0].ELLIPSE.columns')
WITH
(
ColumnName VARCHAR(100) '$.column'
,Length VARCHAR(100) '$.length'
,Type VARCHAR(100) '$.type'
,AllowNulls VARCHAR(10) '$.allowNulls'
) c1; -
Hi Phil,
You have once again come through.
Thanks man
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply