jsonsql-serverleft-joincross-apply

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


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.


Solution

  • 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