xPDO query with where on JSON field

Can anyone tell me how to make a proper xPDO query with ‘where’ targeting JSON field?

Example: I placed ‘firstName’ as an extra user field stored in ‘modUserProfile.extended’

Now I want to get user by his first name = ‘Adam’
$users = $modx->getObject(‘modUserProfile’, array(‘extended.firstName’=>‘Adam’));
but this doesn’t work.
What is the correct syntax for pointing JSON field object in that case?

Filtering like that isn’t supported unfortunately.

You should be able to use the MySQL function JSON_EXTRACT for this:

$c = $modx->newQuery('modUserProfile');
$c->where('JSON_EXTRACT(extended, "$.firstName") = "Adam"');
$user = $modx->getObject('modUserProfile', $c);

But maybe it is a better idea to create a new database table and store the data in its own column. An extra like ClassExtender could help with that.

It works! :slight_smile:
thanks for quick tip

Inefficient but it works. One of the huge issues of the current era.