0

I want to create One table model using declarative schema having one column type as json.

    <?xml version="1.0" ?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table comment="levosoft_partpicker_imagetags Table" engine="innodb" name="levosoft_partpicker_imagetags" resource="default">
        <column comment="Entity Id" identity="true" name="imagetags_id" nullable="false" padding="6" unsigned="true" xsi:type="smallint"/>
        <constraint referenceId="PRIMARY" xsi:type="primary">
            <column name="imagetags_id"/>
        </constraint>
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="false"
                default="0" comment="Entity ID"/>
        <column name="tags_json" nullable="true" xsi:type="json"/>
        <index referenceId="INDEX_PARTPICKER_PRODUCT" indexType="btree">
            <column name="entity_id"/>
        </index>
        <constraint xsi:type="foreign" referenceId="TEXTMIMEDI_PAARTPICKER_IMAGETAGS_CAT_PRD_ENTT_ENTT_ID"
                    table="levosoft_partpicker_imagetags" column="entity_id" referenceTable="catalog_product_entity"
                    referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>

I captured following error:-

/var/www/public_html/vendor/magento/framework/Setup/Declaration/Schema/SchemaConfig.php:82:
string(412) "The XML in file "/var/www/public_html/app/code/Levosoft/Partpicker/etc/db_schema.xml" is invalid:
Element 'column', attribute '{http://www.w3.org/2001/XMLSchema-instance}type': The QName value 'json' of the xsi:type attribute does not resolve to a type definition.
Line: 9

The error shows that yet magento had not added support for json type.

Any proper work around? Other then I `alter type later via sql`

fmi: To check declarative schema is working properly you have to run following command:

php bin/magento setup:db-declaration:generate-whitelist --module-name=Textmimedia_Partpicker

If no error in script it will generate whitelist file else no error will be displayed & file not generated. To check its error you have to update following code in core file (/public_html/vendor/magento/framework/Setup/Declaration/Schema/SchemaConfig.php) for testing:

public function getDeclarationConfig()
    {
        try {
        $schema = $this->schemaFactory->create();
        $data = $this->readerComposite->read(FileResolverByModule::ALL_MODULES);
        $this->declarativeSchemaBuilder->addTablesData($data['table']);
        $schema = $this->declarativeSchemaBuilder->build($schema);
        return $schema;
        } catch (\Exception $e) {
            var_dump($e->getMessage());exit;
        }
    }

SOLUTION EXPECTED IS: HOW TO ADD THIS OPTION IN MAGENTO2.3

4

Magento does not support json type out of the box. You can check all supported types at vendor/magento/framework/Setup/Declaration/Schema/etc/schema.xsd

actually you don't need json type, ofc it's much more easier make all checks of data consistency on db side, but you can create a column with LONGTEXT and before save, add verification on php level (cast to/from json) or use specific mysql json functions https://dev.mysql.com/doc/refman/5.7/en/json.html

4
  • Completely agree with data suggestion Apr 16 '20 at 23:35
  • Completely Disagree with your point. I know this when this field is not their we do like that but this is not proper work around. In past also face a lot issues due to longtext or blob field type to store json. If Mysql is giving this option then this option should be in Mageto Latest versions as well. Apr 17 '20 at 6:43
  • purpose of this question is to add PROPER Customization for this issue to add JSON field into the magento2. Not the other work around. thanks Apr 17 '20 at 6:50
0

Note: It seems this is now supported in Magento 2.4.0.

So something like this possibly:

<column xsi:type="json" name="json" nullable="true" comment="Json"/>

JSON field support MySQL 5.7 supports the native JSON data type: https://dev.mysql.com/doc/refman/5.7/en/json.html. Magento 2.4.0 now supports using JSON fields with a declarative schema. See: https://devdocs.magento.com/guides/v2.4/release-notes/backward-incompatible-changes/#json-field-support

Note: This of course depends on DB support. MariaDB 10.2.7 has a JSON alias for LONGTEXT, https://mariadb.com/kb/en/json-data-type/

Unfortunately I have not managed to get it to work yet as the DB version we are using at present is a little bit older so will just use text for now...

1
0

JSON format is supported in Magento 2.4.0 I tested it myself:

 <table name="xxxx">
        <column xsi:type="json" name="xxxxx" comment="Comment for xxx field"/>
    </table>
1
0

I have created a patch for 2.3 based on the commits used for 2.4

Here are the commits: https://github.com/magento/magento2/pull/25479

here is a link to the patch:

https://gist.github.com/willboudle/4fb62bac2ed895bb0172202e6288ee74

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.