If seems that filtering over joined table is not working, and query for the right table supports only text-search clause
prepare tables:
drop table if exists customers;
drop table if exists orders;
create table customers(name text, email text, address text);
create table orders(product text, customer_id int, quantity int, order_date string, tags multi, details json);
insert into customers values (1, ‘Alice Johnson’, ‘alice@example.com’, ‘123 Maple St’), (2, ‘Bob Smith’, ‘bob@example.com’, ‘456 Oak St’), (3, ‘Carol White’, ‘carol@example.com’, ‘789 Pine St’), (4, ‘John Smith’, ‘john@example.com’, ‘15 Barclays St’);
insert into orders values (1, ‘Laptop Computer’, 1, 1, ‘2023-01-01’, (101,102), ‘{“price”:1200,“warranty”:“2 years”}’), (2, ‘Smart Phone’, 2, 2, ‘2023-01-02’, (103), ‘{“price”:800,“warranty”:“1 year”}’), (3, ‘Tablet Device’, 1, 1, ‘2023-01-03’, (101,104), ‘{“price”:450,“warranty”:“1 year”}’), (4, ‘Monitor Display’, 3, 1, ‘2023-01-04’, (105), ‘{“price”:300,“warranty”:“1 year”}’);
For SQL query I receive one record
select * from customers
join orders on customers.id = orders.customer_id
where match ('alice')
and orders.quantity = 1
and orders.order_date = '2023-01-01' ;
But JSON API query returns 2 hit records and it seems that my filters over ‘quantity’ and ‘order_date’ is not working. Is it a bug or I do something in wrong way?
JSON query example
query = {
"table": "customers",
"query": {"query_string": "alice"},
"limit": 10,
"join": [
{
"type": "inner",
"table": "orders",
"query": {"bool": {"must": [{"equals": {"quantity": 1}}, {"equals": {"order_date": "2023-01-01"}}]}},
"on": [
{
"left": {"table": "customers", "field": "id"},
"operator": "eq",
"right": {"table": "orders", "field": "customer_id"},
}
],
}
],
}