MSSQL Tabellen en Query voor JSON Column Properties

Dit document beschrijft hoe je in Microsoft SQL Server twee tabellen kunt aanmaken om kolom-eigenschappen te beheren, data kunt invoegen, en een query kunt gebruiken om een JSON-array te genereren. De oplossing ondersteunt verschillende eigenschapstypen:

Boolean (bijv. true/false)
Text (bijv. strings zoals "80px")
Number (bijv. integers zoals 42)
JSON-objecten (bijv. geneste structuren zoals headerStyle)

De structuur is flexibel en kan meerdere geneste objecten per kolom bevatten (zoals headerStyle en cellStyle).

Tabelstructuur

Tabel 1: Columns

Bevat de unieke kolomnamen.

CREATE TABLE Columns (
    ColumnId INT PRIMARY KEY IDENTITY(1,1),
    ColumnName NVARCHAR(50) NOT NULL UNIQUE
);

ColumnId: Unieke identifier voor elke kolom.
ColumnName: De naam van de kolom (bijv. "id", "aantal").

Tabel 2: ColumnProperties

Bevat de eigenschappen van de kolommen, met ondersteuning voor verschillende typen en geneste structuren.

CREATE TABLE ColumnProperties (
    PropertyId INT PRIMARY KEY IDENTITY(1,1),
    ColumnId INT NOT NULL,
    PropertyName NVARCHAR(50) NOT NULL,
    PropertyValue NVARCHAR(100),
    PropertyType NVARCHAR(20) NOT NULL, -- "boolean", "text", "number", "json"
    ParentPropertyName NVARCHAR(50) NULL, -- Voor geneste JSON-objecten zoals "headerStyle" of "cellStyle"
    FOREIGN KEY (ColumnId) REFERENCES Columns(ColumnId)
);

PropertyId: Unieke identifier voor elke eigenschap.
ColumnId: Verwijzing naar de kolom in de Columns-tabel.
PropertyName: Naam van de eigenschap (bijv. "editable", "backgroundColor").
PropertyValue: Waarde van de eigenschap (als string opgeslagen, later getypeerd in de query).
PropertyType: Type van de eigenschap ("boolean", "text", "number", "json").
ParentPropertyName: Geeft aan onder welk genest object deze eigenschap valt (bijv. "headerStyle").

Data invoegen

Hieronder wordt voorbeeld-data ingevoegd die alle typen demonstreert: boolean, text, number en geneste JSON-objecten.

-- Vul de Columns-tabel
INSERT INTO Columns (ColumnName)
VALUES ('id'), ('aantal'), ('artikel_prijs'), ('is_seperate_sale');

-- Vul de ColumnProperties-tabel
INSERT INTO ColumnProperties (ColumnId, PropertyName, PropertyValue, PropertyType, ParentPropertyName)
VALUES 
    -- Voor "id"
    (1, 'editable', 'false', 'boolean', NULL),
    (1, 'width', '80px', 'text', NULL),
    (1, 'align', 'center', 'text', NULL),
    (1, 'priority', '10', 'number', NULL),
    (1, 'backgroundColor', '#e6e6e6', 'text', 'headerStyle'),
    (1, 'fontSize', '14px', 'text', 'headerStyle'),
    (1, 'color', '#333', 'text', 'headerStyle'),
    (1, 'border', '1px solid black', 'text', 'cellStyle'),
    (1, 'padding', '5px', 'text', 'cellStyle'),
    (1, 'weight', '2', 'number', 'cellStyle'),

    -- Voor "aantal"
    (2, 'type', 'number', 'text', NULL),
    (2, 'align', 'right', 'text', NULL),
    (2, 'maxValue', '100', 'number', NULL),

    -- Voor "artikel_prijs"
    (3, 'type', 'currency', 'text', NULL),
    (3, 'align', 'right', 'text', NULL),
    (3, 'decimalPlaces', '2', 'number', NULL),

    -- Voor "is_seperate_sale"
    (4, 'type', 'boolean', 'text', NULL),
    (4, 'visible', 'true', 'boolean', NULL);

Voorbeeld-data uitleg

Boolean: editable ("false"), visible ("true").
Text: width ("80px"), type ("number"), backgroundColor ("#e6e6e6").
Number: priority (10), maxValue (100), decimalPlaces (2), weight (2).
JSON-objecten: headerStyle met backgroundColor, fontSize, color; cellStyle met border, padding, weight.

Query om JSON te genereren

De query bouwt een JSON-array onder "columns", waarbij elke kolom een object is met een "name" en eigenschappen. Alle typen en geneste objecten worden correct verwerkt.

SELECT 
    (
        SELECT 
            c.ColumnName AS name,
            -- Top-level eigenschappen
            MAX(CASE WHEN cp.PropertyName = 'editable' AND cp.PropertyType = 'boolean' THEN CONVERT(BIT, CASE cp.PropertyValue WHEN 'true' THEN 1 ELSE 0 END) END) AS editable,
            MAX(CASE WHEN cp.PropertyName = 'width' AND cp.PropertyType = 'text' THEN cp.PropertyValue END) AS width,
            MAX(CASE WHEN cp.PropertyName = 'align' AND cp.PropertyType = 'text' THEN cp.PropertyValue END) AS align,
            MAX(CASE WHEN cp.PropertyName = 'type' AND cp.PropertyType = 'text' THEN cp.PropertyValue END) AS type,
            MAX(CASE WHEN cp.PropertyName = 'priority' AND cp.PropertyType = 'number' THEN CONVERT(INT, cp.PropertyValue) END) AS priority,
            MAX(CASE WHEN cp.PropertyName = 'maxValue' AND cp.PropertyType = 'number' THEN CONVERT(INT, cp.PropertyValue) END) AS maxValue,
            MAX(CASE WHEN cp.PropertyName = 'decimalPlaces' AND cp.PropertyType = 'number' THEN CONVERT(INT, cp.PropertyValue) END) AS decimalPlaces,
            MAX(CASE WHEN cp.PropertyName = 'visible' AND cp.PropertyType = 'boolean' THEN CONVERT(BIT, CASE cp.PropertyValue WHEN 'true' THEN 1 ELSE 0 END) END) AS visible,
            -- headerStyle (genest object)
            JSON_QUERY((
                SELECT 
                    MAX(CASE WHEN cp2.PropertyName = 'backgroundColor' THEN cp2.PropertyValue END) AS backgroundColor,
                    MAX(CASE WHEN cp2.PropertyName = 'fontSize' THEN cp2.PropertyValue END) AS fontSize,
                    MAX(CASE WHEN cp2.PropertyName = 'color' THEN cp2.PropertyValue END) AS color
                FROM ColumnProperties cp2
                WHERE cp2.ColumnId = c.ColumnId 
                  AND cp2.ParentPropertyName = 'headerStyle'
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )) AS headerStyle,
            -- cellStyle (genest object)
            JSON_QUERY((
                SELECT 
                    MAX(CASE WHEN cp2.PropertyName = 'border' THEN cp2.PropertyValue END) AS border,
                    MAX(CASE WHEN cp2.PropertyName = 'padding' THEN cp2.PropertyValue END) AS padding,
                    MAX(CASE WHEN cp2.PropertyName = 'weight' AND cp2.PropertyType = 'number' THEN CONVERT(INT, cp2.PropertyValue) END) AS weight
                FROM ColumnProperties cp2
                WHERE cp2.ColumnId = c.ColumnId 
                  AND cp2.ParentPropertyName = 'cellStyle'
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )) AS cellStyle
        FROM Columns c
        LEFT JOIN ColumnProperties cp ON cp.ColumnId = c.ColumnId AND cp.ParentPropertyName IS NULL
        GROUP BY c.ColumnId, c.ColumnName
        FOR JSON PATH
    ) AS columns
FOR JSON PATH, ROOT('columns'), WITHOUT_ARRAY_WRAPPER;

Uitleg

Array: FOR JSON PATH zonder ROOT in de subquery maakt een array van objecten.
Eigenschappen:
- Boolean: editable en visible worden geconverteerd naar echte booleans met CONVERT(BIT, ...).
- Text: width, align, type blijven strings.
- Number: priority, maxValue, decimalPlaces, weight worden geconverteerd naar integers met CONVERT(INT, ...).
Geneste objecten: headerStyle en cellStyle worden opgebouwd met subqueries die eigenschappen dynamisch combineren op basis van ParentPropertyName.
Groepering: GROUP BY zorgt dat alle eigenschappen per kolom in één object worden samengevoegd.
JSON_QUERY: Voorkomt dat geneste objecten dubbel worden ge-escaped in de JSON-output.

JSON-resultaat

De bovenstaande query produceert de volgende JSON:

{
  "columns": [
    {
      "name": "id",
      "editable": false,
      "width": "80px",
      "align": "center",
      "priority": 10,
      "headerStyle": {
        "backgroundColor": "#e6e6e6",
        "fontSize": "14px",
        "color": "#333"
      },
      "cellStyle": {
        "border": "1px solid black",
        "padding": "5px",
        "weight": 2
      }
    },
    {
      "name": "aantal",
      "type": "number",
      "align": "right",
      "maxValue": 100
    },
    {
      "name": "artikel_prijs",
      "type": "currency",
      "align": "right",
      "decimalPlaces": 2
    },
    {
      "name": "is_seperate_sale",
      "type": "boolean",
      "visible": true
    }
  ]
}

Typen en uitbreidbaarheid

Ondersteunde typen

Boolean: Waarden zoals "true"/"false" worden omgezet naar JSON true/false (bijv. editable, visible).
Text: Strings blijven ongewijzigd (bijv. width, type, backgroundColor).
Number: Numerieke waarden worden als integers in de JSON opgenomen (bijv. priority, maxValue).
JSON-objecten: Geneste structuren worden dynamisch opgebouwd (bijv. headerStyle, cellStyle).

Extra typen toevoegen

Decimal: Voor decimale getallen (bijv. "price": 19.99), voeg een case toe zoals:

MAX(CASE WHEN cp.PropertyName = 'price' AND cp.PropertyType = 'number' THEN CONVERT(DECIMAL(10,2), cp.PropertyValue) END) AS price

Datum: Voor datums (bijv. "lastUpdated": "2025-03-03"), voeg toe:

MAX(CASE WHEN cp.PropertyName = 'lastUpdated' AND cp.PropertyType = 'text' THEN CONVERT(DATE, cp.PropertyValue) END) AS lastUpdated

Array: Voor een array (bijv. "tags": ["a", "b"]), sla dit op als een JSON-string in PropertyValue (type "json") en gebruik JSON_QUERY om te parsen:
```
MAX(CASE WHEN cp.PropertyName = 'tags' AND cp.PropertyType = 'json' THEN cp.PropertyValue END) AS tags
```

Nieuwe geneste objecten

Voeg een nieuw genest object toe (bijv. footerStyle) door:

Rijen toe te voegen met ParentPropertyName = 'footerStyle':

INSERT INTO ColumnProperties (ColumnId, PropertyName, PropertyValue, PropertyType, ParentPropertyName)
VALUES (1, 'textAlign', 'center', 'text', 'footerStyle');

De query uitbreiden met een nieuwe subquery:

JSON_QUERY((
    SELECT 
        MAX(CASE WHEN cp2.PropertyName = 'textAlign' THEN cp2.PropertyValue END) AS textAlign
    FROM ColumnProperties cp2
    WHERE cp2.ColumnId = c.ColumnId 
      AND cp2.ParentPropertyName = 'footerStyle'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)) AS footerStyle

Opmerkingen

Flexibiliteit: De structuur ondersteunt alle basistypen en geneste objecten. Nieuwe eigenschappen of typen kunnen eenvoudig worden toegevoegd door de query aan te passen.
Beperkingen: De query vereist expliciete CASE-statements voor elke eigenschap. Voor een volledig dynamische oplossing (waarbij eigenschapsnamen niet hardcoded zijn), kun je een stored procedure met dynamische SQL overwegen.
Prestaties: Bij veel eigenschappen of kolommen kan de query complexer worden; optimaliseer indien nodig met indexen op ColumnId en ParentPropertyName.

Dit ontwerp biedt een complete en uitbreidbare basis voor het beheren en genereren van kolom-eigenschappen in JSON-formaat met MSSQL.


### Verbeteringen in deze versie
1. **Alle typen**: Boolean, text, number en JSON-objecten zijn volledig geïmplementeerd en gedemonstreerd in de data en query.
2. **Numerieke waarden**: Toegevoegd met voorbeelden zoals `priority`, `maxValue`, `decimalPlaces`, en `weight`.
3. **Uitbreidbaarheid**: Suggesties voor extra typen (decimal, datum, array) en hoe je ze implementeert.
4. **Voorbeelden**: De data en JSON-resultaten zijn uitgebreid om alle typen te laten zien, inclusief meerdere geneste objecten.
5. **Documentatie**: Duidelijke uitleg over hoe je nieuwe typen of geneste objecten toevoegt.