Lesson 4, Exercise 3: Using Visual Query Builder to query data

In this exercise, you will use Visual Query Builder to create a query that you will run against the welsh_pubs collection. The query limits the results to pubs that contain the term snooker in their name. The query also limits the number of fields included in the results.

To create the query

1. On the welsh_pubs collection tab, ensure that the collection data is displayed in Table View and click the Visual Query Builder link to open the query form.

2. In the Query section of the query form, click the Add icon (white-on-green plus sign) in the text box.

The form will expand to include options for defining a search condition.

3. The first drop-down list in the expanded section includes the collection’s fields.

From the list, select the name field.

4. The second drop-list list contains a list of operators for defining the search condition.

From the list, select the contains operator.

5. In the text box beneath the two drop-down lists, type snooker.

6. Click the Run button at the top right corner of the Query section.

The Result tab is updated to include only pubs whose name includes snooker, which totals 17 documents, as shown in the following figure.

7. On the Result tab, locate one of the Caerphilly values in the local_authority column and drag it to the second text box in the Query section of the query form (the text box that includes the text Drag field here or double-click).

8. In the operators drop-down list, select doesn’t equal.

This ensures that the final query results will not include any pubs that are governed by the Caerphilly local authority.

9. On the Result tab, change the display to Tree View and expand the address field in the first document.

10. Select the following four fields: fsa_id, name, city – which we’ll refer to later as address.city because it is a nested field of address – and local_authority.

11. Drag the selected fields to the text box in the Projection section (the text box that includes the text Drag and drop fields here or double-click).

12. On the Result tab, de-select the four fields and then drag the address.city field to the text box in the Sort section (the text box with the text Drag and drop fields here or double-click).

The query form should now look like the following figure.

The query bar at the top of the welsh_pubs collection tab is updated as you build your query, so it should now reflect the Query, Projection, and Sort sections as they’re defined in Visual Query Builder.

13. On the Result tab, display the documents in Table View.

14. Then click the Run query ()button at the right side of the query bar.

The Result tab should now display only 14 rows and include only the _id, fsa_id, name, address, and local_authority fields.

15. On the query bar, click the Hide Query Builder link to close the query form.

16. Open Studio 3T preferences and select the Show all embedded fields in Table View check box on the General tab.

When you select the check box, a message box appears, explaining how to apply your changes.

Click OK to close the message box, and then click OK to close the Preferences dialog box.

17. On the Result tab, right-click anywhere in the document area and click Restore Default View.

Studio 3T adds a column to the Result tab for the address.city field, making it easier to see all the data at a glance.

Leave the collection open for the next exercise.