I want to filter custom collections with JSON field filters. In the below image, there are 2 fields category and product. They have JSON value.
I am able to get collection by Direct SQL query by below code.
$connection = $this->resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION); $table=$this->resource->getTableName('table_name'); $data = $connection->fetchRow('SELECT * FROM ' . $table. ' where JSON_CONTAINS(product,\'["2"]\')');
Below is the output
Array ( [xyz_id] => 19 [xyz_with] => Syncro System [category] => ["2", "3"] [product] => [["1", "2"]] )
But I want to filter this collection by Magento way like below example
$collection->addFieldToFilter('product', ['eq' ->> $id]);
Please let me know how can I do this by Magento way.