Developer utility module for Magento 2.4.x that creates database views aggregating EAV entity data with attribute values in JSON format.

• Magento: 2.4.x
• PHP: 8.1+
• Database: MySQL 5.7+ or MariaDB 10.2.3+
  • Requires MySQL JSON function support

composer require --dev mage-os/module-eav-debug-views bin/magento setup:upgrade

Combines catalog_product_entity with all EAV attributes aggregated as JSON.

Columns:

• All catalog_product_entity columns (entity_id, sku, type_id, etc.)
• eav_attributes (JSON) - All EAV attribute values from decimal, datetime, int, text, varchar tables

Example Query:

SELECT entity_id, sku, type_id, JSON_PRETTY(eav_attributes) as attributes FROM dev_product WHERE sku = 'my-product-sku';

Extract Specific Attributes:

SELECT entity_id, sku, JSON_EXTRACT(eav_attributes, '$.name') as name, JSON_EXTRACT(eav_attributes, '$.price') as price, JSON_EXTRACT(eav_attributes, '$.status') as status FROM dev_product WHERE entity_id = 1;

Combines catalog_category_entity with EAV attributes.

Columns:

• All catalog_category_entity columns (entity_id, path, level, etc.)
• eav_attributes (JSON) - All EAV attribute values from decimal, datetime, int, text, varchar tables

Example Query:

SELECT entity_id, parent_id, path, level, JSON_EXTRACT(eav_attributes, '$.name') as name, JSON_EXTRACT(eav_attributes, '$.is_active') as is_active FROM dev_category WHERE level = 2;

Combines customer_entity with EAV attributes.

Columns:

• All customer_entity columns (entity_id, firstname, lastname, email, etc.)
• eav_attributes (JSON) - All EAV attribute values from decimal, datetime, int, text, varchar tables

Example Query:

SELECT entity_id, email, firstname, lastname, JSON_PRETTY(eav_attributes) as custom_attributes FROM dev_customer WHERE email LIKE '%@example.com';

Combines customer_address_entity with EAV attributes.

Columns:

• All customer_address_entity columns (entity_id, firstname, lastname, street, city, etc.)
• eav_attributes (JSON) - All EAV attribute values from decimal, datetime, int, text, varchar tables

Example Query:

SELECT entity_id, parent_id, city, country_id, JSON_PRETTY(eav_attributes) as custom_attributes FROM dev_address WHERE parent_id = 1;

Quick reference for product attribute metadata.

Columns:

• All eav_attribute columns (attribute_id, attribute_code, etc.)
• All catalog_eav_attribute columns (is_searchable, is_filterable, used_in_product_listing, etc.)
• attribute_sets (JSON) - All attribute sets and groups the attribute is assigned to, including IDs, names, and sort order.
• @TODO: Add eav_options with all option IDs and values for DB-stored select and multiselect-type attributes.

Example Query:

SELECT attribute_id, attribute_code, backend_type, frontend_input, is_filterable, is_searchable, position, attribute_sets FROM dev_product_attribute WHERE is_filterable=1 ORDER BY attribute_code;

All EAV views aggregate all store_id values into a single JSON object per entity.

Store-specific attribute keys use the format attribute_code:store_id (e.g., name:1, name:2). Global attributes (store_id = 0) use just the attribute_code (e.g., name, sku).

Example - Querying store-specific values:

-- Get product with global and store-specific names SELECT entity_id, sku, JSON_EXTRACT(eav_attributes, '$.name') as global_name, JSON_EXTRACT(eav_attributes, '$.\"name:1\"') as store_1_name, JSON_EXTRACT(eav_attributes, '$.\"name:2\"') as store_2_name FROM dev_product WHERE sku = 'my-product'; -- See all attribute values including store-specific SELECT entity_id, sku, JSON_PRETTY(eav_attributes) as all_attributes FROM dev_product WHERE entity_id = 1;

For technical reasons, we can't sort attributes alphabetically. Scoped values for an attribute may appear anywhere within the JSON. (MySQL does not support sorting values within JSON_OBJECTAGG(...) in ONLY_FULL_GROUP_BY mode.)

If filtering by attribute values, be careful about the amount of records processed.

Fast:

-- Uses entity table index SELECT * FROM dev_product WHERE entity_id = 123; SELECT * FROM dev_product WHERE sku = 'ABC123';

Not fast:

-- Full table scan with JSON parsing SELECT * FROM dev_product WHERE JSON_EXTRACT(eav_attributes, '$.status') = 1;

• NOT materialized - Data is queried live from base tables
• NOT indexed - Uses base table indexes via entity_id
• CTE overhead - 5 subqueries per entity type
• JSON aggregation - Processing cost on SELECT

Recommendation: Use for ad-hoc debugging queries, not high-frequency production queries.

bin/magento module:uninstall MageOS_EavDebugViews --remove-data

This command:

1. Drops all module views from the database
2. Removes module from setup_module table
3. Removes module code (if installed via composer)

-- See all attributes for a specific product SELECT entity_id, sku, JSON_PRETTY(eav_attributes) FROM dev_product WHERE sku = 'problematic-sku';

-- Find disabled products SELECT entity_id, sku, JSON_EXTRACT(eav_attributes, '$.status') as status FROM dev_product HAVING status = 2;

-- What custom attributes exist for products? SELECT attribute_code, frontend_input, is_required FROM dev_product_attribute WHERE is_user_defined = 1;

-- View category tree with names SELECT entity_id, level, path, JSON_EXTRACT(eav_attributes, '$.name') as name, JSON_EXTRACT(eav_attributes, '$.is_active') as active FROM dev_category WHERE level BETWEEN 1 AND 3 ORDER BY path;

Open Software License (OSL-3.0)

Issues and pull requests welcome on GitHub.

This is a community-maintained developer utility. No support or warranty implied. Use at your own risk.

For bugs or feature requests, please open an issue in the GitHub repository.