MySQL · JSON · GUI · results grid

Edit MySQL 8 JSON columns in a GUI

pimvdmolen.nl · · ~5 min read

MySQL 8 stores JSON as a typed binary document, not as free-form text. When you edit a cell in a results grid, the client still has to send a value the server accepts. One bad character and you get ERROR 3140 (22032): Invalid JSON text instead of a saved row. This guide shows how to edit those columns safely from a desktop GUI, and how to avoid the edits that look fine in the grid but corrupt the document.

Confirm the column is really JSON

Run a quick check before you trust the grid:

SHOW COLUMNS FROM orders LIKE 'meta';
SELECT id, JSON_TYPE(meta), JSON_VALID(meta) FROM orders LIMIT 5;

JSON_TYPE should return OBJECT, ARRAY, or a scalar type. JSON_VALID must be 1 for every non-NULL row you plan to touch. If the column is LONGTEXT or TEXT with JSON-looking content, the server will not validate writes the same way. MariaDB often aliases JSON to LONGTEXT, so treat MariaDB grids as text-with-conventions unless SELECT VERSION() proves otherwise.

Open the table in your client, or run a narrow SELECT with a primary key. Wide SELECT * grids make it easy to click the wrong cell and write the wrong document.

Edit the cell without turning JSON into a string

Most desktop clients show a JSON column as a single string in the results grid. That is fine for display. Trouble starts when you paste a value that is already quoted, or when the client wraps your edit in extra quotes.

Safe patterns:

  1. Select the JSON cell and open the inline editor or value editor your GUI provides.
  2. Paste a raw document such as {"status":"paid","items":[1,2]} with double quotes, no trailing comma.
  3. Clear the cell only if you intend SQL NULL. Do not type the four letters NULL inside quotes, and do not leave an empty string unless the column allows it and your app expects "".
  4. Apply the row change, then re-read the same primary key.

Dangerous patterns that show up often: trailing commas after the last property, single quotes around keys, JavaScript-style comments, smart quotes from a document editor, and pasting a value that already includes outer quotes so MySQL stores a JSON string instead of an object.

After a save, verify with:

SELECT id, meta, JSON_EXTRACT(meta, '$.status') FROM orders WHERE id = 42;

If JSON_EXTRACT returns a quoted string when you expected an object path, you double-encoded the document.

Prefer path updates for small changes

Replacing the entire document works when you redesign the shape. For a one-key change, send a path update so sibling keys stay intact:

UPDATE orders
SET meta = JSON_SET(meta, '$.status', 'refunded', '$.refunded_at', UTC_TIMESTAMP())
WHERE id = 42;

UPDATE orders
SET meta = JSON_REMOVE(meta, '$.tmp_flag')
WHERE id = 42;

Many GUIs let you run that in the SQL editor while you keep the grid open for inspection. Partial updates beat hand-editing a 4 KB blob when you only need one field.

Approach Best for Main risk
Grid cell replace Small docs, full rewrite Wipe keys, invalid JSON, double-encoding
JSON_SET / JSON_REPLACE One or a few keys Wrong path (creates nested keys by accident)
JSON_REMOVE Dropping keys Path typos leave stale data
App migration script Bulk reshape Skipping a WHERE clause

Use Safe Lock or a dry run before production writes

On a live table, preview the statement your GUI will send. TabulaSQL, a free desktop client for MySQL and MariaDB, includes a results grid with inline edit and Safe Lock so you can read the generated UPDATE before it runs. Download it from https://tabulasql.com/download if you want that preview step in a local MIT-licensed app.

Whatever client you use, keep a recovery path:

  • Edit on a copy table first: CREATE TABLE orders_meta_fix AS SELECT id, meta FROM orders WHERE id = 42;
  • Keep the previous document in your clipboard or in a scratch note until JSON_VALID returns 1 on the live row.
  • Scope writes with the primary key. Never apply a grid edit after you sorted or filtered in a way that hides the key column.

If the server rejects the write, read the error position. MySQL points at the first bad character. Fix that character, do not keep retrying the same paste.

Spot corruption after the fact

Common failure modes and how they look:

  • Invalid text: write fails immediately with 3140. Nothing saved.
  • Double-encoded string: write succeeds, but JSON_TYPE(meta) is STRING and paths like $.status miss.
  • Partial wipe: write succeeds, but keys disappeared because you pasted a smaller object.
  • SQL NULL overwrite: the cell shows empty or NULL and your app treats missing config as defaults.

Catch those with a short audit query:

SELECT id
FROM orders
WHERE meta IS NOT NULL
  AND (
    JSON_VALID(meta) = 0
    OR JSON_TYPE(meta) = 'STRING'
    OR JSON_EXTRACT(meta, '$.status') IS NULL
  );

Adjust the path check to a key your domain always keeps. Fix rows with a controlled UPDATE rather than another blind grid paste.

When you need a free MySQL GUI with a results grid, Safe Lock, and no account signup, TabulaSQL fits that niche. Pair grid edits with JSON_SET for surgical changes, and keep JSON_VALID in your muscle memory before you close the tab.

Frequently asked questions

MySQL 8 validates JSON on write. Trailing commas, single quotes, comments, or the text NULL instead of JSON null trigger error 3140 (Invalid JSON text). Fix the document, then apply again.

Use JSON_SET, JSON_REPLACE, or JSON_REMOVE for small key changes. Replace the whole cell only when the shape changes a lot. Partial updates shrink the chance of wiping sibling keys.

Yes. Connect to MySQL 8, open the table or run a SELECT, edit the JSON cell in the results grid, then use Safe Lock to preview the UPDATE before you commit.