Schema::TYPE_JSON fields aren’t saved to the database as JSON

[joshuabaker]

Description

I’m building a custom field using the JSON schema type and, no matter what I do, I can’t get the value to save in the database as raw JSON without it being encoded.

Currently data is encoded as "{\"foo\": 1}" and not {"foo": 1}, which prohibits any queries on that field (e.g. foo is always null for select field_test, JSON_EXTRACT(field_test, '$.foo') as foo from content).

I’ve tried a few things, including various encoding/decoding in both the normalizeValue and serializeValue methods. It looks like this line always overrides this though.

Steps to reproduce

1. Create a custom field using Schema::TYPE_JSON for getContentColumnType
2. Save valid JSON to the field

Additional info

• Craft version: 3.5.17.1
• PHP version: 7.4.12
• Database driver & version: MySQL 5.7.32

[brandonkelly]

Craft doesn’t currently support JSON columns, since MySQL didn’t get support for them until 5.7.8, but Craft supports as far back as 5.5.0. If/when we bump our MySOL requirement, we can add support for it.

In the meantime, you’ll just need to JSON-decode your values from normalizeValue(), similar to this:

cms/src/fields/Table.php

Lines 373 to 376 in bfe9a55

	public function normalizeValue($value, ElementInterface $element = null)
	{
	if (is_string($value) && !empty($value)) {
	$value = Json::decodeIfJson($value);

[joshuabaker]

Ah, that would explain it. Thanks for the quick response, @brandonkelly.

[brandonkelly]

Craft 4 is going to require MySQL 5.7.8+, so I’ve just added JSON support for it: #9089

[brandonkelly]

Turns out MariaDB doesn’t fully support JSON columns, so I’ve had to roll back Craft’s own use of it (26ed987), however it’s still supported if any modules want to use it (and don’t have to worry about supporting MariaDB).

[joshuabaker]

That’s a shame. Thanks for the update. 👍🏻

[Mosnar]

@brandonkelly For reference, could you let us know what issues you ran into with compatibility? I see that the JSON column type is currently just an alias for LONGTEXT, but naively reading the MariaDB docs, it doesn't sound like it would be much of an issue. I'm guessing you found a weird caveat?

[joshuabaker]

Reading the docs, I think that JSON is just an alias for LONGTEXT in MariaDB.

The JSON type in MySQL stores the JSON object in a compact form, not as LONGTEXT as in MariaDB. This means that row based replication will not work for JSON types from MySQL to MariaDB.

MariaDB can't directly access MySQL's JSON format.

Guessing this creates migration pains moving between the database systems?

[brandonkelly]

The issue is that once the column is created, there’s no trace of it being a “json” column. show create table just says it’s longtext. Which means you lose all of Yii/PDO’s JSON schema features, like being able to save an array directly to it without JSON-encoding it.