I have a table with a column holding the schema (key, type) of a JSON object, and another table with a column holding instances of objects described by that schema.
It can happen that the object instance is missing some of the JSON schema properties, so I want to display NULL as value of that key.
For instance, having the schema
[
{"property": "label", "type": "string"},
{"property": "id", "type": "number"}
]
and the object instance
'{"label__": "undefined label", "id": 111}
I want to transform the JSONs into a vertical table of Key/Value pairs (JKey column is just for info - also, as you can notice, "label__" key and its value are missing, since the schema takes priority):
FProp FType JKey JVal
---------------------------------
id number id 111
label string null null
Below is an SQL that I created, left joining the schema with the values, but it does not seem to work. It only outputs the rows that match the property names.
DECLARE @schema NVARCHAR(MAX) = '
{
"fields": [
{"property": "label", "type": "string"},
{"property": "id", "type": "number"}
]
}
';
WITH JsonSchemas AS (
SELECT 1 AS SChemaId, @schema AS JSNDef
),
FieldsJsonValues AS (
SELECT 1 AS FieldInstanceId, 1 as SchemaId,
'{"label": "some label", "id": 1}' AS Val
UNION ALL
SELECT 2 , 1, '{"label__": "undefined label", "id": 2}'
),
SchemasTable AS (
SELECT S.SchemaId, JField.FProp, JField.FType
FROM JsonSchemas S
CROSS APPLY OPENJSON (S.JSNDef, '$.fields') AS JFieldsArr
CROSS APPLY OPENJSON (JFieldsArr.Value)
WITH
(
FProp NVARCHAR(20) N'$.property',
FType NVARCHAR(MAX) N'$.type'
) AS JField
),
FieldsValues AS (
SELECT V.FieldInstanceId, V.SchemaId, KVPValues.[key] AS JKey,
KVPValues.Value as JVal
FROM FieldsJsonValues V
CROSS APPLY OPENJSON (V.Val) KVPValues
)
SELECT ST.*, FV.FieldInstanceId, FV.JKey, FV.JVal
FROM SchemasTable ST
LEFT JOIN FieldsValues FV
ON ST.FProp = FV.JKey AND FV.SchemaId = ST.SChemaId
ORDER BY FieldInstanceId
What am I missing ?
Changing the LEFT JOIN into a RIGHT JOIN shows the correct number of rows, but obviously it outputs the schema properties as NULL.
SQL Fiddle here.
I think you need an additional LEFT JOIN
in the SchemasTable
CTE:
DECLARE @schema NVARCHAR(MAX) = '
{
"fields": [
{"property": "label", "type": "string"},
{"property": "id", "type": "number"}
]
}
';
WITH JsonSchemas AS (
SELECT 1 AS SChemaId, @schema AS JSNDef
),
FieldsJsonValues AS (
SELECT 1 AS FieldInstanceId, 1 AS SchemaId, '{"label": "some label", "id": 1}' AS Val
UNION ALL
SELECT 2 , 1, '{"label__": "undefined label", "id": 2}'
),
SchemasTable AS (
SELECT S.SchemaId, FV.FieldInstanceId, JField.FProp, JField.FType
FROM JsonSchemas S
CROSS APPLY OPENJSON (S.JSNDef, '$.fields') WITH (
FProp NVARCHAR(20) N'$.property',
FType NVARCHAR(MAX) N'$.type'
) AS JField
LEFT JOIN FieldsJsonValues FV ON S.SchemaId = FV.SchemaId
),
FieldsValues AS (
SELECT V.FieldInstanceId, V.SchemaId, KVPValues.[key] AS JKey, KVPValues.Value as JVal
FROM FieldsJsonValues V
CROSS APPLY OPENJSON (V.Val) KVPValues
)
SELECT ST.FProp, ST.FType, FV.JKey, FV.JVal
FROM SchemasTable ST
LEFT JOIN FieldsValues FV ON ST.SChemaId = FV.SchemaId AND ST.FieldInstanceId = FV.FieldInstanceId AND ST.FProp = FV.JKey
ORDER BY ST.FieldInstanceId
Result:
FProp | FType | JKey | JVal |
---|---|---|---|
label | string | label | some |
id | number | id | 1 |
id | number | id | 2 |
label | string |