SQL Server - LEFT JOIN two JSON objects on a common property

Question

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.

Answer 1

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