Add collection filter from json field

Question

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.

Answer 1

You do it like this:

 $collection->getSelect()->where('JSON_CONTAINS(product, \'[?]\')', 2);

Answer 2

$select = $collection->getSelect();
$select->where('JSON_CONTAINS(product, ?)', '["2"]');