Tuesday, July 18, 2017

Using find() with the MySQL Document Store

The Video

The find() function for the MySQL Document Store is a very powerful tool and I have just finished a handy introductory video. By the way -- please let me have feed back on the pace, the background music, the CGI special effects (kidding!), and the amount of the content.

The Script

For those who want to follow along with the videos, the core examples are below. The first step is to connect to a MySQL server to talk to the world_x schema (Instructions on loading that schema at the first link above).

\connect root@localhost/world_x

db is an object to points to the world_x schema. To find the records in the countryinfo collection, use db.countryinfo.find(). But that returns 237 JSON documents, too many! So lets cut it down to one record by qualifying that we only want the record where the _id is equal to USA, db.countryinfo.find(‘_id = “USA”’)

Deeper Level items in the Document

You can reach deeper level items by providing the path to the object such as db.countryinfo.find(‘geography.Continent = “North America”). Be sure to remember Case Sensitivity!!

Limits, Offsets, and Specifications

It is very simple to place restrictions on the amount of output by post pending .limit(5).skip(6). And you can specify which parameters meet you specification by using find(‘GNP > 8000000’)

Limiting Fields

But what if you do not want all the document but just a few certain fields. Then post pend .fields([“Name”, “GNP”]) to find().

And once again you can dig deeper into the document with specifying the path, such as.fields([“Name”, “GNP”, “geography.Continent”]).sort(“GNP”).

Sorting

Yes, you can easily sort the output from find() by adding .sort(“GNP”,”Name”) at the end.

More Complex Searches

Of course you can make the find() function perform more complex dives into the data such as db.countryinfo.find(‘GNP> 500000 and IndepYear > 1500”).

Parameter Passed Values

And find of parameter passed values will be happy to find they have not been forgotten. db.countryinfo.find(“Name = :country”).bind(“Country”,”Canada”)