Today we will try to learn about MongoDb $in clause on array.
Though Mysql does not have native array datatype it is possible to store array
element as comma seperated string.it is not possible always emulate all
functionality of MongoDb in Mysql and vice versa let us give it a try.
Ground Up:
We have Mongo Query:
If collection already exist delete it db.inventory.drop()
db.inventory.insertMany([
{ _id:1, item: 'Erasers', quantity: 15, tags: [ 'school', 'home' ] },
{ _id:2 , item: 'Books', quantity: 5, tags: [ 'school', 'storage', 'home' ] },
{ _id:3,item: 'Paper', quantity: 10, tags: [ 'Printer'] },
{ _id:4,item: 'Pencil', quantity: 4, tags: [ 'school'] }
])
Equivalent Mysql Table:
If table already exist drop it
drop table inventory;
create table inventory(
id int auto_increment primary key,
item varchar(50),
quantity int,
tags varchar(250)
);
INSERT INTO inventory(item,quantity,tags) values('Erasers',15,'school, home');
INSERT INTO inventory(item,quantity,tags) values('Books',5,'school,storage,
home');
INSERT INTO inventory(item,quantity,tags) values('Paper',10,'Printer');
INSERT INTO inventory(item,quantity,tags) values('Pencil',4,'School');
$in on array Element:
Here is a mongo query to find all documents where tag value contain either one
of the "school" & "home" may be additional element but thats allowed.
db.inventory.find( { tags: { $in: [ "school","home" ] } } )
output:
[
{ _id: 1, item: 'Erasers', quantity: 15, tags: [ 'school', 'home' ] },
{
_id: 2,
item: 'Books',
quantity: 5,
tags: [ 'school', 'storage', 'home' ]
},
{ _id: 4, item: 'Pencil', quantity: 4, tags: [ 'school' ] }
]
Mysql Query:
We cant straight forward do this lets get bit crazy.
select
id,
item,
quantity,
tags,
INSTR(tags,'school')
from
inventory
where
INSTR(tags,'school') = 1 or INSTR(tags,'home') > 0
Output:
+----+---------+----------+----------------------+----------------------+
| id | item | quantity | tags | INSTR(tags,'school') |
+----+---------+----------+----------------------+----------------------+
| 1 | Erasers | 15 | school, home | 1 |
| 2 | Books | 5 | school,storage, home | 1 |
| 4 | Pencil | 4 | School | 1 |
+----+---------+----------+----------------------+----------------------+
$in on non array Element:
Example 1:
Mongo Query:
db.inventory.find( { item: { $in: [ "Books","Paper" ] } })
Output:
[
{
_id: 2,
item: 'Books',
quantity: 5,
tags: [ 'school', 'storage', 'home' ]
},
{ _id: 3, item: 'Paper', quantity: 10, tags: [ 'Printer' ] }
]
Mysql Query:
select id,item,quantity,tags from inventory where
item in ('books','paper');
Output:
+----+-------+----------+----------------------+
| id | item | quantity | tags |
+----+-------+----------+----------------------+
| 2 | Books | 5 | school,storage, home |
| 3 | Paper | 10 | Printer |
+----+-------+----------+----------------------+
Example 2:
Mongo Query:
db.inventory.find( { quantity: { $in: [ 5, 15 ] } })
Output:
[
{ _id: 1, item: 'Erasers', quantity: 15, tags:
[ 'school', 'home' ] },
{
_id: 2,
item: 'Books',
quantity: 5,
tags: [ 'school', 'storage', 'home' ]
}
]
MySQL query:
select id,item,quantity,tags from inventory where quantity in (5,15);
Output:
+----+---------+----------+----------------------+
| id | item | quantity | tags |
+----+---------+----------+----------------------+
| 1 | Erasers | 15 | school, home |
| 2 | Books | 5 | school,storage, home |
+----+---------+----------+----------------------+
using $in in update clause:
Mongo Query:
db.inventory.updateMany(
{ tags: { $in: [ "home"] } },
{ $set: { exclude: false } }
)
In mysql you are not supposed to update table structure in update query,so we
can't dynamically add new column here. But somewhat similar where we are
emulating $in in current update example.
MySQL Query:
Update inventory set quantity = quantity +1 where INSTR(tags,"home") > 0
Note :
PostgreSQL, Microsoft SQL Server (MSSQL), and Oracle have native array data
types unlike MySQL.It might get added.
No comments:
Post a Comment