How can I use regex in a migration query condition? I have data in a table that looks like this:

|-------------------------------------------------------|
|ID|              Data                                  |
|--|----------------------------------------------------|
| 1|{"key":"value",...,"image":"1234567",..."key:value"}|
| 2|{"key":"value",...,"image":"",..."key:value"}       |
| 3|{"key":"value",...,"image":"9876543",..."key:value"}|


In my migration I only want to import items that have an image ID in the Data column.

Previously I was doing this in my migration class query() method:

class myMigration extends SqlBase {

  public function query() {
    return $this-&gt;select('data_table', 'd')
      -&gt;distinct()
      -&gt;fields('d', ['ID', 'Data'])
      -&gt;condition('Data', '%' . db_like('"image":') . '%', 'LIKE');
  }

  public function fields() {

    $fields = [
      'ID' =&gt; t('ID'),
    ];

    return $fields;
  }

  public function getIds() {
    return [
      'ID' =&gt; [
        'type' =&gt; 'string',
      ],
    ];
  }


This was also selecting rows where the image value was empty in the Data column.

This query in SQL works for selecting the correct data, but I cannot get it to work with the query() method in my migration:

SELECT ID, Data    
FROM data_table
WHERE Data REGEXP '"image":"\d*[^&gt;"]';
I was able to use REGEXP operator in a normal condition() method, although it is not stated in the documentation.

The answer was found on this example.

Solution:

  public function query() {
    return $this-&gt;select('data_table', 'd')
      -&gt;distinct()
      -&gt;fields('d', ['ID', 'Data'])
      -&gt;condition('Data', '"image":"\d*[^&gt;"]', 'REGEXP');
  }