Skip to content

Schema mapping: bind cells and columns to JSON schema properties #1

Open
Open
Schema mapping: bind cells and columns to JSON schema properties#1

Description

@simontaurus
simontaurus
opened on May 26, 2026

Goal

Let users map columns or cells to properties of an OSL JSON schema (e.g. Category:Item), so a spreadsheet can be treated as a tabular view of typed records, similar to how Excel lets you pick cell refs in a formula.

Builds on two features already in the codebase (Univer-migration branch):

  • Label overlay: cells holding a UUID render as the page's human label.
  • Autocompletion: configurable per-range SMW live-query popup; selecting a result writes the page's UUID.

What mapping looks like to the user

  1. User opens the editor and clicks a new toolbar button "Schema mapping".
  2. Dialog appears with two halves:
    • Left: tree of properties from one or more selected Category schemas (e.g. Category:Item with label, description, quantity.value, quantity.unit).
    • Right: live preview of which columns or cells are already mapped.
  3. To map a property: click it in the tree, then click a cell or column header in the sheet. The cell gets a colored corner marker and a tooltip with the property name.
  4. Each mapping persists in metadata.schemaMappings on the workbook.

Data model (proposal)

{
  "metadata": {
    "schemaMappings": {
      "sheetId-0": {
        "categories": ["Category:Item"],
        "bindings": [
          { "cell_range": "B:B", "propertyPath": "label" },
          { "cell_range": "C:C", "propertyPath": "quantity.value" },
          { "cell_range": "D:D", "propertyPath": "quantity.unit" }
        ]
      }
    }
  }
}
  • cell_range uses the same A1 syntax as the autocompletion feature. Named cell_range (not range) because the schema's own range annotation already exists for property reference filtering, and we want to keep them distinct.
  • propertyPath is dot-notation through the schema's nested objects and is unique inside the schema — fully identifies the target. The SMW property identifier travels in the schema's JSON-LD @context, no need to duplicate it here.
  • Compound properties (e.g. quantity = value + unit) are expressed as two bindings sharing a parent path; the relationship is clear from the paths themselves.

Integration with existing features

  • Autocompletion: when a mapped property has a range or subclassof_range in its schema annotation (accepts a UUID reference), the mapping should auto-configure an autocompletion entry for that cell_range. The Category quick-pick already wires SMW queries from a category; same plumbing applies.
  • Label overlay: any cell mapped to a UUID-typed property automatically gets label rendering.
  • Storage: extend DataAdapter.toStorageFormat() and the load path to round-trip metadata.schemaMappings.

Schema source

OSL stores schemas in the jsonschema slot of Category:* pages. Existing utilities to reuse:

  • mwjson.api.getPage() / getFilePage() for fetching slot content
  • mwjson.schema class for parsing, dereferencing $ref, walking allOf, traversing properties
  • The postMessage bridge between iframe and parent (parent owns wiki API calls)

UX subquestions to settle before coding

  • Tree view: list all properties flat, or grouped by sub-object?
  • Required vs optional properties: visual indication?
  • Multi-category: union the property sets, or sheets-per-category?
  • Mapping mode: click-property-then-click-cell, or drag-and-drop?
  • Visual marker for mapped cells: corner triangle (already used for labels), different color?
  • Unmap UX: right-click on a mapped column?

Follow-up: export-to-JSON

Once mappings exist, add an "Export as JSON" button that walks the sheet row by row, builds JSON objects matching the schema, and either downloads them or creates wiki pages directly. Can be a separate issue.

Acceptance criteria

  • Schema mapping dialog opens from a toolbar button while editing
  • User can pick one or more Category schemas (autocomplete from the categories namespace)
  • Properties of selected schemas appear in a navigable tree
  • Clicking a property and then a cell creates a mapping
  • Mapped cells get a distinct visual indicator
  • Mappings persist across save and load
  • Removing a mapping is reversible
  • When a UUID-typed property is mapped, the corresponding cell_range automatically gains an autocompletion config

Activity

  1. simontaurus commented on May 27, 2026

    @simontaurus
    simontaurus
    on May 27, 2026
    ContributorAuthor

    Proposal: OO-LD schema, partial flatten, Excel and JSON-editor hybrid, with free placement

    The generalisation that ties this issue, #2, and the array work together:

    OO-LD schema, partially flattened, Excel plus JSON-editor hybrid UI. Excel handles flat scalar leaves. A JSON-editor popup/modal handles whatever is left under each flattening boundary (nested objects, residual arrays, autocomplete fields). Schema paths (root.property[0]....) can be placed anywhere on the sheet, so the whole machinery reuses every existing schema definition: validation, defaults, RDF mapping via the JSON-LD @context, autocompletion via range / subclassof_range, descriptions, titles, language variants.

    Built on mwjson.util.flattenSchema / flatten / unflatten. The proposals below align propertyPath and the binding shape so that mapping is just "tell us which schema path lives at which spreadsheet cell", nothing more.

    This issue covers the framework, i.e. the data model the user manipulates by hand via the mapping dialog. Issue #2 then describes the template generator that produces a default mapping automatically from one or more category schemas.

    Free placement

    A mapping is just cell_range paired with propertyPath. Consequences:

    • Columns can be in any order, not just schema order.
    • A workbook can mix paths from multiple schemas in arbitrary positions.
    • A path can appear at a single cell (B2), a column (B:B), a row (2:2), a rectangle (B2:D2), or any A1 selection.
    • The same row can carry only the subset of properties the user wants on this sheet; the rest stay unmapped.

    The mapping carries metadata (flatten_options) and per-binding layout metadata (block for grouped-blocks visual grouping, see #4); it never assumes the sheet's geometry.

    Hiding properties

    There is no hidden flag on a binding. Hiding a property from the sheet is just not creating a binding for it. The property still exists in the record (filled with the schema's default, autogenerated, or left absent if optional), and mwjson.util.flatten / unflatten round-trip unaffected paths through untouched.

    Typical reasons to leave a property unmapped:

    • optional fields the user doesn't need on this particular sheet,
    • required fields that have a default in the schema (the default fills in automatically on export),
    • autogenerated fields like uuid or created_at (filled by the wiki layer on save),
    • properties of a different category that this sheet doesn't care about.

    Removing a mapping later (delete the binding) effectively hides the column without losing any underlying record data; the property still round-trips through flatten / unflatten if any of the other bindings happen to set or read it. Re-adding a binding for the same propertyPath later is non-destructive.

    The auto-generator in #2 uses this same mechanism: rather than emitting a hidden flag, it simply skips bindings for properties it would otherwise emit.

    Example layouts

    For a category with name, label: [{text, lang}], subclass_of: [Category:Category], metadata: {source, imported_at}, the same workbook metadata can drive very different sheet layouts. The four below all use the same binding shape and the same flatten / unflatten round-trip.

    Standard tabular layout (one record per row)

    A B C D E F G
    1 name label[0].text label[0].lang label[1].text label[1].lang subclass_of metadata
    2 Foo Hello en Hallo de Category:Material {source: "import.csv"}
    3 Bar Hi en Category:Tool {source: "manual"}

    Bindings (excerpt):

    [
  { "cell_range": "A2:A1000", "propertyPath": "name" },
  { "cell_range": "B2:B1000", "propertyPath": "label[0].text" },
  { "cell_range": "C2:C1000", "propertyPath": "label[0].lang" },
  { "cell_range": "F2:F1000", "propertyPath": "subclass_of" },
  { "cell_range": "G2:G1000", "propertyPath": "metadata" }
]

    The cell editor for each cell is derived from the resolved sub-schema (see below): name is a string scalar, subclass_of is an array<uuid> so it opens a multi-select picker or the generic json-editor (user preference), metadata is an object so it opens a json-editor modal.

    Pivot layout (one record per column)

    A B C
    1 name Foo Bar
    2 label[0].text Hello Hi
    3 label[0].lang en en
    4 subclass_of Category:Material Category:Tool

    Bindings:

    [
  { "cell_range": "B1:Z1", "propertyPath": "name" },
  { "cell_range": "B2:Z2", "propertyPath": "label[0].text" },
  { "cell_range": "B3:Z3", "propertyPath": "label[0].lang" },
  { "cell_range": "B4:Z4", "propertyPath": "subclass_of" }
]

    Single-record sheet (one row of values driving a calculation)

    A B
    1 name Foo
    2 mass.value 12.5
    3 mass.unit kg
    4 density.value =B2/3.14

    Bindings:

    [
  { "cell_range": "B1", "propertyPath": "name" },
  { "cell_range": "B2", "propertyPath": "mass.value" },
  { "cell_range": "B3", "propertyPath": "mass.unit" },
  { "cell_range": "B4", "propertyPath": "density.value" }
]

    Multi-section sheet

    A B C
    1 === Materials ===
    2 name mass.value mass.unit
    3 Steel 7.85 kg
    4 Aluminium 2.70 kg
    5
    6 === Devices ===
    7 name manufacturer serial
    8 Pump-1 Acme ABC-001

    The mapping has two sets of bindings, one for each section, against the two relevant category schemas. The "===" header rows are plain cells that carry no binding.

    Cell editor derived from the resolved sub-schema

    The binding does not carry a render field. The cell editor is derived from the sub-schema at propertyPath, resolved on demand by walking the loaded category schema (see resolveSubSchema below):

    • sub-schema type string, number, integer, boolean, null: scalar cell (Excel side). The schema's format annotation drives a widget pick: date -> date picker, date-time -> datetime picker, email -> email validation, time -> time picker, etc. enum -> dropdown. range / subclassof_range -> autocompletion against an SMW query (existing plumbing).
    • sub-schema type object or array: cell holds the sub-tree, double-click opens a json-editor popup/modal driven by that sub-schema. The same plumbing (validation, autocompletion inside the modal, defaults) the rest of OSL already uses applies.

    For the array case, the json-editor has several presentation choices that don't change the data model:

    • array<uuid> or array<{$ref}>: render as multi-select chips in the cell, popup is the same chip picker the rest of OSL uses.
    • array<string|number>: render as delimited text (comma) in the cell, popup is a simple list editor.
    • array<complex object>: render as a "sub-sheet" link in the cell, popup is the linked sheet.
    • fallback: generic json-editor on the array's items schema.

    Whether to apply the specialised presentation or fall back to the generic json-editor is a UI preference, not a property of the binding. A workbook-level ui_preferences section (or global user setting) can choose, e.g. "always use multi-select chips for array<uuid>", with the json-editor as the universal fallback if the user prefers consistency.

    Path syntax (canonical flattenSchema output)

    With the default options (array_index_notation: "[0]", notation: "dot"):

    • name: scalar
    • quantity.value, quantity.unit: object leaves (object always recurses)
    • label[0].text, label[0].lang, label[1].text, label[1].lang: array expanded with array_length: { "label[*]": 2 }
    • subclass_of: array kept as-is when array_length["subclass_of[*]"] is undefined; the cell holds the whole array
    • metadata: object whose path appears in flatten_options.stop_at_paths; the cell holds the whole sub-object

    Same paths the issue #2 generator emits, same paths an OSL Property form would build, same paths the JSON-LD @context resolves to RDF properties. A record moves freely between form and sheet without translation.

    resolveSubSchema

    // Walk the loaded category schema along propertyPath and return the sub-schema
// at that path. Strips array indices ("label[0].text" walks "label", then ".items", then "text").
function resolveSubSchema(rootSchema, propertyPath, flattenOptions) {
  // Use flattenOptions.array_index_notation to detect/strip indices.
  // Follow object properties; on encountering an array segment, descend into `items`.
  // Resolve $ref / allOf along the way (reuse mwjson.schema dereferencing).
  // Return the schema fragment that describes whatever lives in the cell.
}

    Advantages:

    • The Category schema stays the single source of truth (matches RDF/@context, validation, defaults, autocompletion). Editing the Category page propagates to every workbook that binds to it.
    • Workbook metadata stays small and stable across schema evolution.
    • No risk of stale embedded sub-schemas drifting from the upstream definition.

    Trade-off: opening a cell editor needs the category schema loaded. Already the case for autocompletion and label resolution, so no new network dependency. If the schema is unreachable the cell falls back to a raw JSON editor with a warning.

    Binding shapes

    Scalar leaf (Excel side of the hybrid):

    { "cell_range": "B:B", "propertyPath": "name" }

    Grouped-block leaf (still Excel side, just visually grouped, see #4):

    {
  "cell_range": "C:C", "propertyPath": "label[0].text",
  "block": [{ "groupId": "label", "pathPattern": "label[*]", "index": 0, "subProp": "text", "bannerColor": "#e3f2fd" }]
}

    Flattening boundary (cell holds a sub-tree, json-editor by default):

    { "cell_range": "G:G", "propertyPath": "subclass_of" }
    { "cell_range": "I:I", "propertyPath": "metadata" }

    That is the entire binding shape. cell_range, propertyPath, and optionally block for grouped-blocks layout (see #4). Everything else is derived.

    Sheet-level flatten config

    {
  "metadata": {
    "schemaMappings": {
      "sheetId-0": {
        "categories": ["Category:Item"],
        "flatten_options": {
          "array_index_notation": "[0]",
          "notation": "dot",
          "array_length": {
            "label[*]": 2,
            "subclass_of[*]": null,
            "tags[*]": null
          },
          "stop_at_paths": ["metadata"]
        },
        "bindings": [ ... ]
      }
    }
  }
}
    • array_length: number: array is flattened to that depth, leaf bindings emitted per index.
    • array_length: null (or absent): array stays as array; the path itself is a binding.
    • stop_at_paths: object subtrees re-collapsed after flattenSchema ran (the upstream function always recurses into objects).

    A separate ui_preferences block (workbook or user level) can choose specialised vs. generic renderers per sub-schema shape; it is orthogonal to the bindings.

    Round-trip helpers (already exist)

    Export row to record:

    const flatRow = {};
for (const b of bindings) flatRow[b.propertyPath] = sheet.getRange(b.cell_range).getValue();
const record = mwjson.util.unflatten(flatRow, flatten_options);

    Values at boundary paths are whole sub-trees (arrays/objects); unflatten places them at the right path. The json-editor modal commits sub-trees back into the cell as JS objects/arrays, so no serialisation tricks needed.

    Load record to row:

    const flatRecord = mwjson.util.flatten(record, '', flatten_options);
for (const b of bindings) sheet.getRange(b.cell_range).setValue(flatRecord[b.propertyPath]);

    For boundary bindings, flatten will not recurse past the boundary; the whole sub-tree lands in one cell.

    RDF and @context reuse

    A schema mapping is by construction also an RDF mapping. Each propertyPath is a JSON path, and JSON-LD's @context (already present on every OSL category schema) maps each path to an RDF predicate. Once a sheet has bindings, exporting the rows as RDF triples is mechanical:

    for (const row of rows) {
  const flatRow = {};
  for (const b of bindings) flatRow[b.propertyPath] = sheet.getRange(rowIdx, ...).getValue();
  const record = mwjson.util.unflatten(flatRow, flatten_options);
  const jsonld = { '@context': schema['@context'], ...record };
  // hand off to jsonld.toRDF() or write to wiki page
}

    No separate RDF-mapping layer for the sheet, it is the same mapping the form, the API, and the wiki page already use.

    Required and default

    flattenSchema propagates required (up to minItems for expanded array items) and distributes defaults. For boundary cells, the resolved sub-schema's own required / default apply inside the JSON-editor modal. Required leaves get a visual marker; defaults pre-fill empty cells.

    Open questions

    • Should the editor validate propertyPath strings against the current flattenSchema(schema, options) output at load time? Catches drift between sheet and schema.
    • When array_length["label[*]"] drops from 3 to 2, do we delete the orphan columns automatically, or warn first?
    • How does resolveSubSchema cache across cell edits? Memoise per (category, propertyPath) and invalidate on category-schema reload?
    • Where do ui_preferences live (workbook, user, both)? And which presentation defaults ship out of the box?
    • How are the JSON-editor modals styled in the iframe context: reuse mwjson's existing form widget, or a lighter dedicated component?
    • Cross-reference: auto-placement and the boundary-choice heuristic are in Template generator: pre-fill a sheet from one or more Category schemas #2; visual grouping of flattened leaves is in Grouped column blocks for arrays of small objects (label, alias, ...) with nesting support #4.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions