Lecture2 - NoSQL, JSON, MongoDB copy.pdf

Exercise 2: Read a JSON text { "book": [ { "id": "language": "edition": "author": }, { "id": "language": "edition": "author": } ] } "01", "Java", "third", "Herbert Schildt" What is the data type? A, object B, array C, name-value pair D, value E, string F, number G, Boolean H, null "07", "C++", "second", "E.Balagurusamy" A JSON text example 28 Exercise 2: Read a JSON text { "book": [ { "id": "language": "edition": "author": }, { "id": "language": "edition": "author": } ] } "01", "Java", "third", "Herbert Schildt" What is the data type? A, object B, array C, name-value pair D, value E, string F, number G, Boolean H, null "07", "C++", "second", "E.Balagurusamy" A JSON text example 29 Exercise 2: Read a JSON text { "book": [ { "id": "language": "edition": "author": }, { "id": "language": "edition": "author": } ] } 01, "Java", "third", "Herbert Schildt" What is the data type? A, object B, array C, name-value pair D, value E, string F, number G, Boolean H, null "07", "C++", "second", "E.Balagurusamy" A JSON text example 30 Exercise 2: Read a JSON text { "book": [ { "id": "language": "edition": "author": }, { "id": "language": "edition": "author": } ] } "01", "Java", "third", Null What is the data type? A, object B, array C, name-value pair D, value E, string F, number G, Boolean H, null "07", "C++", "second", "E.Balagurusamy" A JSON text example 31 <html> <head> <title>Creation of array object in javascript using JSON</title> <script language="javascript" > document.writeln("<h2>JSON array object</h2>"); var books = { "Pascal" : [ { "Name" : "Pascal Made Simple", "price" : 700 }, { "Name" : "Guide to Pascal", "price" : 400 } ], "Scala" : [ { "Name" : "Scala for the Impatient", "price" : 1000 }, { "Name" : "Scala in Depth", "price" : 1300 } ] } Books Object - Pascal Array - Scala Array var i = 0 document.writeln("<table border='2'><tr>"); for(i=0;i<books.Pascal.length;i++) { document.writeln("<td>"); document.writeln("<table border='1' width=100 >"); document.writeln("<tr><td><b>Name</b></td><td width=50>" + books.Pascal[i].Name+"</td></tr>"); document.writeln("<tr><td><b>Price</b></td><td width=50>" + books.Pascal[i].price +"</td></tr>"); document.writeln("</table>"); document.writeln("</td>"); } for(i=0;i<books.Scala.length;i++) { document.writeln("<td>"); document.writeln("<table border='1' width=100 >"); document.writeln("<tr><td><b>Name</b></td><td width=50>" + books.Scala[i].Name+"</td></tr>"); document.writeln("<tr><td><b>Price</b></td><td width=50>" + books.Scala[i].price+"</td></tr>"); document.writeln("</table>"); document.writeln("</td>"); } document.writeln("</tr></table>"); </script> </head> <body> </body> </html> 32 MongoDB JS ON COMPS381F 33 What is MongoDB? ❑ MongoDB • • a document database (NoSQL) a data management platform (cloud) ❑ MongoDB document • • • a record in MongoDB is a document the documents are stored in BSON format (binary JSON) a document is composed of field-value pairs Reference: What is MongoDB? https://www.mongodb.com/docs/manual/ MongoDB Document. https://www.mongodb.com/docs/manual/introduction/ 34 Collections in MongoDB ❑ Collections • • MongoDB stores documents in collections. Collections are analogous to tables in relational databases. 35 Documents in a collection ❑ All documents have _id field with unique values (primary key) Generated automatically by MongoDB Blog Title Description By URL Tags Likes 0..* Comment User Message DateCreated Like Example: document structure of a blog site: 36 Documents in a collection ❑ All documents have _id field with unique values (primary key) ❑ Documents in the same collection can have dynamic schema • • Different documents can have different fields or structure Common fields in different documents may hold different types of data Another document can have different fields of structure Blog Title Description By URL Tags Likes 0..* Comment User Message DateCreated Like Example: document structure of a blog site: 37 Databases in MongoDB ❑ A MongoDB server typically has multiple databases ❑ Each database includes a set of collections ❑ A collection exists within a single database Source: https://www.researchgate.net/figure/SQL-vs-MongoDB-terms_fig4_340622952 38 CONNECT TO A MONGODB SERVER There are 3 ways to connect to a MongoDB server (cluster) ▪ The mongo shell (via a Linux terminal) ▪ Robo 3T (a GUI client written in Java) ▪ Node.js app (more about this in a few weeks!) Attention: When you try to do MongoDB command exercises on your PC, you can also try any command line tools you can find from MongoDB. More details at https://www.mongodb.com/try/download/shell 39 BASIC MONGODB COMMANDS: DATABASE Show databases ▪ show dbs Create database ▪ use DATABASE_NAME Drop (remove) currently connected database ▪ db.dropDatabase() 40 BASIC MONGODB COMMANDS: COLLECTION Show collections ▪ show collections Create collections (in the currently connected DB) ▪ db.createCollection("mycollection") Drop (remove) collections ▪ db.mycollection.drop() 41 Advanced MongoDB JS ON Commands COMPS381F 42 Advanced MongoDB Commands ❑ Documents CRUD operations • Create • Read • Update • Delete ❑ Document Aggregation ❑ Data Modeling 43 MongoDB CRUD - Create ❑ Create or insert a document to a collection db.Collection.insertOne({document1}) db.Collection.insertMany([{document1},{document2},…]) • If a collection does not exist, then it will create a new collection in the database. • Or you can use an additional command db.createCollection(‘Coll ection’) to create a new collection • More examples at https://www.mongodb.com/docs/m anual/tutorial/insert-documents/ 44 MongoDB CRUD - Create Example: insert an array of documents into a collection: db.mycol.insertOne([ { title: 'MongoDB Overview', description: 'MongoDB is no sql database', by: 'tutorials point', url: 'http://www.tutorialspoint.com', tags: ['mongodb', 'database', 'NoSQL'], likes: 100 }, { title: 'NoSQL Database', description: 'NoSQL database doesn't have tables', by: 'tutorials point', url: 'http://www.tutorialspoint.com', tags: ['mongodb', 'database', 'NoSQL'], likes: 20, comments:[ { user:'user1', message: 'My first comment', dateCreated: new Date(2013,11,10,2,35), like: 0 }] } ]) 45 MongoDB CRUD - Read ❑ Read operations retrieve documents from a collections db.Collection.find({query}) • Query for documents by specifying equality conditions db.Collection.find({<field1>:<value1>}) E.g., selecting all documents from the inventory collection where status equals "A": db.inventory.find({status:“A”}) 46 MongoDB CRUD - Read ❑ Query criteria • Specify AND conditions, use db.Collection.find( {<field1>:<value1>,<field2>:<value2>,…}) • When finding multiple fields by separating them by ‘,’, then MongoDB treats it And condition. E.g., selecting all documents from the inventory collection where status equals "A“ and qty is 30: db.inventory.find({status:“A”, qty:30}) 47 MongoDB CRUD - Read ❑ Query criteria - operator • Specify a query filter document with operators db.Collection.find( {<field1>:{<operator1>:[<value1>,<value1>,…]}}) E.g., retrieving all documents from the inventory collection where status equals either "A" or "D": db.inventory.find({status:{$in:[“A”,”D”]}}) 48 MongoDB CRUD - Read ❑ Query criteria - operator • Specify logical $or operator db.Collection.find( {$or:[{<field1>:<value1>},{<field2>:<value2>}]}) E.g., retrieving all documents in the inventory collection where the status equals either "A" or qty is less than 30: db.inventory.find( {$or:[{status:”A”},{qty:{$lt:30}}]} ) 49 MongoDB CRUD - Read ❑ Query criteria – more query operators • Comparison Name $eq $gt $gte $in $lt $lte $ne $nin Description Matches values that are equal to a specified value. Matches values that are greater than a specified value. Matches values that are greater than or equal to a specified value. Matches any of the values specified in an array. Matches values that are less than a specified value. Matches values that are less than or equal to a specified value. Matches all values that are not equal to a specified value. Matches none of the values specified in an array. Name $not Description Inverts the effect of a query expression and returns documents that do not match the query expression. Joins query clauses with a logical NOR returns all documents that fail to match both clauses. Joins query clauses with a logical OR returns all documents that match the conditions of either clause. • Logical $nor $or For more operators, see https://www.mongodb.com/docs/manual/reference/operator/query/ 50 MongoDB CRUD - Read ❑ Query behavior • • • All queries in MongoDB address a single collection. Modify the query to impose limits, skips, and sort orders. Specify a sort() to return documents following a sort order. More details about modifiers at https://www.mongodb.com/docs/v4.2/reference/operator/query-modifier/ 51 MongoDB CRUD - Read ❑ Project behavior • • Project document is used to return specified documents. By default, the _id field is included in the results. To suppress the _id field from the result set, specify _id: 0 in the projection document. 52 MongoDB CRUD - Read ❑ Project behavior 53 MongoDB CRUD - Read ❑ Project behavior 54 MongoDB CRUD - Read ❑ Project behavior 55 MongoDB CRUD - Read ❑ Project behavior • To find some fields that contain arrays, use projection operator: $elemMatch db.schools.find( { zipcode: "63109" }, { students: { $elemMatch: { school: 102 } } } ) { _id: 1, zipcode: "63109", students: [ { name: { name: { name: ] } { _id: 2, zipcode: "63110", students: [ { name: { name: ] } { _id: 3, zipcode: "63109", students: [ { name: { name: ] } { _id: 4, zipcode: "63109", students: [ { name: { name: ] } "john", school: 102, age: 10 }, "jess", school: 102, age: 11 }, "jeff", school: 108, age: 15 } "ajax", school: 100, age: 7 }, "achilles", school: 100, age: 8 }, "ajax", school: 100, age: 7 }, "achilles", school: 100, age: 8 }, "barney", school: 102, age: 7 }, "ruth", school: 102, age: 16 }, More examples at https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch/ 56 Read (Regular expressions) ❑ Query an embedded/nested documents • Specify a query condition on fields in an embedded/nested document, use dot notation ({“field.nestedfield”:value}) { _id: 100, type: "food", item: "xyz", qty: 25, price: 2.5, ratings: [ 5, 8, 9 ], memos: [ { memo: "on time", by: "shipping" }, { memo: "approved", by: "billing" } ] } { E.g., db.Collection.find({“memos.0.by”:”shipping”}) E.g., db.Collection.find({“memos.by”:”shipping”}) _id: 101, type: "fruit", item: "jkl", qty: 10, price: 4.25, ratings: [ 5, 9 ], memos: [ { memo: "on time", by: "payment" }, { memo: "delayed", by: "shipping" } ] } More examples at https://www.mongodb.com/docs/manual/tutorial/query-embedded-documents/ 57 MongoDB CRUD - Read ❑ Frequently used expressions (optional*) • Find documents with any strings matches a query condition Syntax Notes /string/ Matches any strings that contain ‘s t r i n g ’ db.Collection.find({name:/ s t r i n g / }) /^s t r i n g / Matches any strings that begin with ‘to’ db.Collection.find({name:/ ^ s t r i n g / }) /s t r i n g $/ Matches any strings that ends with ‘ct’ db.Collection.find({name:/ s t r i n g $ / }) /t[1-9]/ Matches t1,t2,…,t9 db.Collection.find({name:/ t [ 1 - 9 ] / }) /^t[a-z]*/ Matches any strings that begin with ‘t’ and endswith any number of alphabets db.Collection.find({name:/ ^ t [ a - z ] * / }) *Just for your reference, this part won’t appear in the final exam/test. 58 MongoDB CRUD – Read Index • Using Indexes in MongoDB can improve the efficiency of read operations by reducing the amount of data that query operations need to process. • The index stores the value of a specific field or set of fields, ordered by the value of the field. • The ordering of the index entries supports efficient equality matches and range-based query operations. • MongoDB can return sorted results by using the ordering in the index. More examples at https://www.mongodb.com/docs/manual/indexes/ 59 MongoDB CRUD - Read Index Example1: default _id index • MongoDB creates a unique index on the _id field during the creation of a collection. • The _id index prevents clients from inserting two documents with the same value for the _id field. Note: • Your application must ensure the uniqueness of the values in the _id field to prevent errors. 60 MongoDB CRUD - Read Index Example2: The following diagram illustrates a query that selects and orders the matching documents using an index: 61 MongoDB CRUD - Update ❑ Update operations modify existing documents in a collection. db.Collection.update( {criteria},{action},{option} ) More details at https://www.mongodb.com/docs/manual/reference/method/db.collection.update/ 62 MongoDB CRUD - Update Append to an array: db.students.update( { _id: 1 }, { $push: { scores: 89 } } ) Example: { _id: 1, name: “John Dole”, scores: [80, 23, 90], … } append 89 63 MongoDB CRUD - Update Remove from an array: db.students.update( { _id: 1 }, { $pull: { scores: 23} } ) 64 MongoDB CRUD - Update ❑ Use save operations to replace existing documents db.COLLECTION_NAME.save({ { _id:XXX }, Document to be replaced { NEW_DOCUMENT } Replacement Document }) Note: NEW_DOCUMENT is a JSON object { name/value pair 1, name/value pair 2, ... name/value pair n } 65 MongoDB CRUD - Delete ❑ Delete operations remove documents from a collection. db.Collection.remove( {criteria} ) 66 MongoDB – Aggregation ❑ Aggregation operations • To process multiple documents and return computed results. • E.g., group values from multiple documents together. • E.g., perform operations on the grouped data to return a single result. • E.g., analyze data changes over time. ❑Aggregation pipelines • An aggregation pipeline consists of one or more stages to process multiple documents • Aggregation pipelines run with the db.Collection.aggregate()method do not modify documents in a collection, unless the pipeline contain a stage. 67 MongoDB – Aggregation Example1: The following aggregation pipeline example contains two stages and returns the total amount of all cust documents with status A grouped by cust_id. 68 http://docs.mongodb.org/manual/meta/aggregation-quick-reference/#aggregation-expressions MongoDB – Aggregation Example2: Giving a collection named ‘zipcodes’ in which the document stores the following fields { "_id": "10280", "city": "NEW YORK", "state": "NY", "pop": 5574, "loc": [ -74.016323, 40.710537 ] } • The _id field holds the zip code as a string. • The city field holds the city name. A city can have more than one zip code associated with it as different sections of the city can each have a different zip code. • The state field holds the two letter state abbreviation. • The pop field holds the population. • The loc field holds the location as a latitude longitude pair. 69 MongoDB – Aggregation (a) Using a two-stage aggregation operation to return states with Populations above 10 Million db.zipcodes.aggregate( [ { $group: { _id: "$state", totalPop: { $sum: "$pop" } } }, { $match: { totalPop: { $gte: 10*1000*1000 } } } ] ) SELECT state, SUM(pop) AS totalPop FROM zipcodes GROUP BY state HAVING totalPop >= (10*1000*1000) { "_id" : "AK", "totalPop" : 550043 } 70 MongoDB – Aggregation (a) Using a two-stage aggregation operation to return average city population by state db.zipcodes.aggregate( [ { $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } }, { $group: { _id: "$_id.state", avgCityPop: { $avg: "$pop" } } } ] ) { "_id" : { "state" : "CO", "city" : "EDGEWATER" }, "pop" : 13154 } { "_id" : "MN", "avgCityPop" : 5335 } 71 MongoDB – Aggregation (a) Using aggregation to return largest and smallest cities by state db.zipcodes.aggregate( [ { $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } }, { $sort: { pop: 1 } }, { $group: { _id : "$_id.state", biggestCity: { $last: "$_id.city" }, { $last: "$pop" }, biggestPop: smallestCity: { $first: "$_id.city" }, smallestPop: { $first: "$pop" } } }, // the following $project is optional, and // modifies the output format. { $project: { _id: 0, state: "$_id", biggestCity: { name: "$biggestCity", pop: "$biggestPop" }, smallestCity: { name: "$smallestCity", pop: "$smallestPop" } } } ] ) 72 MongoDB Data Modelling Modelling principles ❑ Design the structure of documents and represent relationships between data as required by applications. ❑ Two tools allow applications to represent these relationships: • References • Embedded Documents 73 MongoDB Data Modelling - Reference ❑ References store the relationships between data by including links or references from one document to another ❑ Applications can resolve these references to access the related data. • Broadly, these are normalized data models. 74 MongoDB Data Modelling – Embedded Documents ❑ Embedded documents capture relationships between data by storing related data in a single document structure. ❑ MongoDB documents make it possible to embed document structures in a field or array within a document. ❑ These denormalized data models allow applications to retrieve and manipulate related data in a single database operation. 75 MongoDB Data Modelling – Embedded Documents ❑ One-to-one relationship between multiple documents → These documents can be emerged as one document, by embedding JSON objects { _id: "joe", name: "Joe Bookreader" { _id: "joe", name: "Joe Bookreader", address: { street: "123 Fake Street", city: "Faketon", state: "MA", zip: "12345" } } + { patron_id: "joe", street: "123 Fake Street", city: "Faketon", state: "MA", zip: "12345" } } 76 42 MongoDB Data Modelling – Embedded Documents ❑ One-to-many relationship between multiple documents → These documents can be emerged as one document, by embedding a JSON array { _id: "joe", name: "Joe Bookreader" } { { _id: "joe", name: "Joe Bookreader", addresses: [ { street: "123 Fake Street", city: "Faketon", state: "MA", zip: "12345" }, { street: "1 Some Other Street", city: "Boston", state: "MA", zip: "12345" } ] + patron_id: "joe", street: "123 Fake Street", city: "Faketon", state: "MA", zip: "12345" } { + patron_id: "joe", street: "1 Some Other Street", city: "Boston", state: "MA", zip: "12345" } } 77 MongoDB Data Modelling – Example Example1: Achieving one-to-many relationships by using references { title: "MongoDB: The Definitive Guide", author: [ "Kristina Chodorow", "Mike Dirolf" ], published_date: ISODate("2010-09-24"), pages: 216, language: "English", publisher: { name: "O'Reilly Media", founded: 1980, location: "CA" } } { title: "50 Tips and Tricks for MongoDB Developer", author: "Kristina Chodorow", published_date: ISODate("2011-05-06"), pages: 68, language: "English", publisher: { name: "O'Reilly Media", founded: 1980, location: "CA" } } 78 MongoDB Data Modelling – Example Example1: Achieving one-to-many relationships by using references { name: "O'Reilly Media", founded: 1980, location: "CA", books: [12346789, 234567890, ...] Factored out publisher } { } _id: 123456789, title: "MongoDB: The Definitive Guide", author: [ "Kristina Chodorow", "Mike Dirolf" ], published_date: ISODate("2010-09-24"), pages: 216, language: "English" { _id: 234567890, title: "50 Tips and Tricks for MongoDB Developer", author: "Kristina Chodorow", published_date: ISODate("2011-05-06"), pages: 68, language: "English" } 79 ONE-TO-MANY RELATIONSHIPS (3) (USING REFERENCES) { publisher_id: "oreilly", name: "O'Reilly Media", founded: 1980, location: "CA" } { _id: 123456789, title: "MongoDB: The Definitive Guide", author: [ "Kristina Chodorow", "Mike Dirolf" ], published_date: ISODate("2010-09-24"), pages: 216, language: "English", publisher_id: "oreilly" } { _id: 234567890, title: "50 Tips and Tricks for MongoDB Developer", author: "Kristina Chodorow", published_date: ISODate("2011-05-06"), pages: 68, language: "English", publisher_id: "oreilly" } 80 Review ❑NoSQL – non-relational databases, store huge amounts of unstructured data with high flexibility. ❑JSON – a text-based data interchange standard, store JSON objects (multiple datatypes) as text, human-readable and language independent ❑Use MongoDB platform ❑A cloud document DB (NoSQL), records data by documents and collections, easy to scale DB ❑Connect to MongDB server by mongo Shell and Robo 3T ❑Basic and Advanced MongoDB commands 81 The coming tutorials! P04, Fri. P02, Fri. P01, Next P05, Next P03, Next 9:00~11:00, in D0625 11:00~13:00, in D0627 Mon. 14:00~16:00, in D0626 Mon. 16:00~18:00, in D0627 Tue. 14:00~16:00, in D0627 Tutorial tasks: • • • • • Import some restaurant documents into your MongoDB cluster. Connect to your MongoDB via 2 ways Using basic commands to check database, collections, and documents in MongoDB. Create a collection and store JSON objects in MongoDB. Using advanced MongoDB commands to manage data base – documents, JOSN objects. 82 After-class Exercise (For your self-study) ❑ Refer to “Lab02_exercise.pdf” in OLE (by clicking Lab02 in the block of week 2). 83 Q&A 84

Lecture2 - NoSQL, JSON, MongoDB copy.pdf

Document Details

Tags

Related

Full Transcript

Upgrade to continue