xPDO query with where on JSON field

General
1

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?

2

Filtering like that isn’t supported unfortunately.

3

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.

4

It works! :slight_smile:
thanks for quick tip

5

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