Loopback 4: How to query an array of objects

Question

I have been unable to query objects based on a property in an array of objects.

I am trying to query all orders that have the event with id 7:

  const orders = await this.orderRepository.find({where: {events: {elemMatch: {'id': event.id}}}});

The above gives me the following error:

 ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''{\"id\":\"7\"}

If i try the following filter, I always get an empty array back:

{where: {events: {like: '%id%'}}}

What is the correct approach for Loopback 4?

UPDATE:

I am using MySQL 8.0.

This is the definition of events in my order model:

@property({
  type: 'array',
  itemType: 'object',
  required: false,
})
events: CartItem[] | null;

Could you share your Order Model definition so that we may confirm your column mappings? — ggordon, Oct 12 '20 at 22:11

ggordon · Accepted Answer · 2020-10-12T22:14:30.583

Solution

Since you are using the MySQL loopback connector to connect to your MySQL database, currently this connector treats both String/JSON as VARCHAR. As such, you could try the following modification to like


{where: {events: {like: '%id:'+7+'%'}}}

or

const orders = await this.orderRepository.find({
    where: {
        events: {
            like: '%id:'+event.id+'%'
        }
    }
});

or using regular expressions

const orders = await this.orderRepository.find({
    where: {
        events: {
            regexp: '.*id:'+event.id+'.*'
        }
    }
});

const orders = await this.orderRepository.find({
    where: {
        events: {
            regexp: new RegExp(".*id:"+event.id+".*")
        }
    }
});

in an attempt to match the json pattern {id:7,name:'Event 7'} where in this case the value inside id could be 7.

Assumptions

Based on your question and the mysql error shown, the following assumptions were made:

Schema (MySQL v5.7)

create table samples(id int primary key auto_increment, events varchar(400));

insert into samples(events) values 
('[{id:3,name:\"Boscobel\"},{id:4,name:\"Rays\"}]'), 
('[{id:7,name:\"Boscobel 7\"},{id:8,name:\"Rays 8\"}]');

Should Receive Results

Query #1

select * from samples where events like '%id\:7%';

| id  | events                                          |
| --- | ----------------------------------------------- |
| 2   | [{id:7,name:"Boscobel 7"},{id:8,name:"Rays 8"}] |

Query #2

select * from samples where events like '%id:7%';

| id  | events                                          |
| --- | ----------------------------------------------- |
| 2   | [{id:7,name:"Boscobel 7"},{id:8,name:"Rays 8"}] |

Should Not Receive Results

Query #3

select * from samples where events like '%id\:70%';

There are no results to be displayed.

Query #4

select * from samples where events like '%id:200%';