JSON & XML cell viewer
functionalOpen JSON or XML cells as tidy, explorable structures instead of a wall of punctuation.
A column holding a JSON or XML document is unreadable in a grid row, it arrives as one long line that runs off the edge of the cell. SQLly notices when a value is actually a document and gives you a proper reader for it.
How a cell is recognised
Detection looks at the cell's raw value - the real data, before display formatting or column masking - and does not rely on the column's declared type. A value counts as a document when it both starts the right way and parses:
- JSON - the trimmed value begins with
{or[and parses as valid JSON. - XML - the trimmed value begins with
<and parses as well-formed XML. - YAML - the value opens with a
---document marker, or has at least two top-levelkey: valuelines plus more structure (a third key, a nested block, or a list item), every top-level line is YAML-shaped, and it parses. A sentence with a colon in it, or a two-line note, stays text. - Markdown - at least two lines with a fenced code block, a table, or two different kinds of Markdown (headings, lists, block quotes, links, bold text, inline code). A lone heading or a short list stays text.
Requiring a successful parse is deliberate: a string that merely starts with a
brace is left alone rather than offered as a document you cannot open. This is
why a nvarchar column holding JSON is recognised, while a genuine
json column holding malformed text is not.
Opening the viewer
- Right-click › Open Structured Data Viewer. Always available, and the route for binary and geometry cells too. (Double-clicking a cell copies its value instead.)
- The Cell Details panel. Shows the same viewer inline for whichever cell is selected, so you can walk the grid with the document open beside it. Open it from a cell's right-click menu (Edit Cell › Cell Details), with ⌘⌥I (CtrlAltI on Windows and Linux), or from the command palette (Toggle Cell Details Panel). Pin keeps it on one cell while you move around.
Inside the viewer
- Pretty-printed. The document is re-indented on the way in. JSON object keys are sorted, so the same document always reads the same way and two rows can be compared line by line.
- Syntax highlighted in Raw mode, a scrollable view of the formatted text.
- Tree mode - collapsible nodes with a live search box and per-node copy, for finding one field in a document too long to scroll. JSON, XML and YAML all get it.
- Preview mode for Markdown, the text rendered with its headings, lists, tables and code blocks; switch to Raw for the highlighted source.
Editing a value in the Cell Details panel
When the result can be edited - a single-table query with a key, on a connection that allows changes - the panel's Value view has an Edit button. It opens a roomy multi-line editor on the cell, which is far easier than typing a long description or a JSON document into a grid cell.
- Apply (or ⌘Enter, CtrlEnter on Windows and Linux) stages the new value exactly like any other grid edit: the row gets the staged marker, and nothing is written until you review and Save (or discard) the staged changes. Esc or Cancel throws the draft away.
- NULL and empty are different. Applying an empty editor stages an empty string; Set NULL stages NULL.
- The value is checked for its column the way the row editor checks a field (a whole number for an integer column, a valid date for a date column, and so on); if it does not fit, the status bar says why and your draft stays open. For a
jsonorjsonbcolumn, text that is not valid JSON is still staged, with a warning under the editor. - Long values load in full first. A cell that arrived as a shortened preview cannot be edited until the panel has fetched the complete value.
- When editing is not possible the panel says why instead of showing Edit, for example a query over several tables, a read-only connection, a masked or computed column, or a binary value (change those with Load from File or the hex viewer).
- Pinned panels edit the pinned cell, even after you have moved on in the grid.
Other kinds of cell content
The same viewer handles the rest of what does not fit a grid cell: images (PNG, JPEG, GIF, WebP, BMP are recognised by their magic bytes and rendered), binary (shown as a hex dump with an ASCII gutter), and geometry values.