JSON

JSON and SQL/JSON

MemCP supports native JSON values through a typed BSON-backed runtime representation. Both the MySQL and PostgreSQL syntax frontends expose constructors, path access, mutation, containment, aggregation, and relational expansion. JSON is useful for attributes whose shape genuinely varies; stable fields used for joins, constraints, or frequent filtering are often clearer as ordinary typed columns.

Creating and reading JSON

MySQL-style constructors and paths include JSON_OBJECT, JSON_ARRAY, JSON_EXTRACT, JSON_VALUE, and the ->/->> operators.

<syntaxhighlight lang="sql"> SELECT JSON_OBJECT('name', 'Ada', 'roles', JSON_ARRAY('admin', 'author')); SELECT profile->>'$.name' AS name FROM users WHERE JSON_EXTRACT(profile, '$.active') = true; </syntaxhighlight>

PostgreSQL syntax supports json/jsonb casts and operators such as ->, ->>, #>, #>>, @>, and <@, together with PostgreSQL-style build and path functions.

Updating and aggregating

Use JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, array mutation, or merge functions to produce an updated document. JSON values are immutable expressions: an UPDATE assigns the returned value back to the column.

<syntaxhighlight lang="sql"> UPDATE users SET profile = JSON_SET(profile, '$.last_login', CURRENT_TIMESTAMP) WHERE id = 42;

SELECT team_id, JSON_ARRAYAGG(name) FROM users GROUP BY team_id; </syntaxhighlight>

Object aggregates, JSON_TABLE, PostgreSQL json_array_elements, and object-key expansion turn documents into relational rows or collect rows into documents. Their exact accepted syntax differs between the MySQL and PostgreSQL endpoints; test queries against the selected frontend.

Indexing and compatibility

Frequently used path expressions can participate in computed-expression optimization and adaptive indexing. Keep the path expression stable and inspect the physical plan with EXPLAIN PHYSICAL; an accepted JSON predicate does not by itself guarantee an index.

JSON numbers, NULL, SQL NULL, missing paths, duplicate object keys, and scalar-versus-container results have compatibility-sensitive semantics. Validate them when migrating from MySQL or PostgreSQL. The executable regression suites tests/sql/expressions/json-functions.yaml and postgresql-json-functions.yaml are the most precise inventory for the current commit.

See Supported SQL, Migration from MySQL and PostgreSQL, SQL over REST, and Data Auto Sharding and Auto Indexing.