# 4.2 Using the Query Service ## Objective * Find and explore datasets * Learn how to address Experience Data Models objects and attributes in your queries ## Context In this you will learn how to use PSQL to retrieve information about the available datasets, how to write a queries for Experience Data Model (XDM), and write your first simple reporting queries using the Query Service and Citi Signal datasets. ## 4.2.1 Basic Queries In this you will learn about the methods to retrieve information about the available datasets and how to properly retrieve data with a query from an XDM dataset. All the datasets hat we have explored via Adobe Experience Platform in the beginning of 1, are also available for access via a SQL interface as tables. To list those tables you can use the **show tables;** command. Execute **show tables;** in your **PSQL command-line interface**. (do not forget to end your command with a semicolon). Copy the command **show tables;** and paste it at the prompt: ![command-prompt-show-tables.png](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-bac6f771624bbbebb98d4c5d35b03215034cdfff%2Fcommand-prompt-show-tables.png?alt=media) You will see the following result: ``` aepenablementfy21:all=> show tables; name | dataSetId | dataSet | description | resolved ------------------------------------------------------------+--------------------------+----------------------------------------------------------------+-------------+---------- demo_system_event_dataset_for_call_center_global_v1_1 | 5fd1a9dea30603194baeea43 | Demo System - Event Dataset for Call Center (Global v1.1) | | false demo_system_event_dataset_for_mobile_app_global_v1_1 | 5fd1a9de250e4f194bec84cd | Demo System - Event Dataset for Mobile App (Global v1.1) | | false demo_system_event_dataset_for_voice_assistants_global_v1_1 | 5fd1a9de49ee76194b85f73c | Demo System - Event Dataset for Voice Assistants (Global v1.1) | | false demo_system_event_dataset_for_website_global_v1_1 | 5fd1a9dee3224d194cdfe786 | Demo System - Event Dataset for Website (Global v1.1) | | false demo_system_profile_dataset_for_loyalty_global_v1_1 | 5fd1a9de250e4f194bec84cc | Demo System - Profile Dataset for Loyalty (Global v1.1) | | false demo_system_profile_dataset_for_ml_predictions_global_v1_1 | 5fd1a9de241f58194b0cb117 | Demo System - Profile Dataset for ML Predictions (Global v1.1) | | false demo_system_profile_dataset_for_mobile_app_global_v1_1 | 5fd1a9deddf353194a2e00b7 | Demo System - Profile Dataset for Mobile App (Global v1.1) | | false demo_system_profile_dataset_for_website_global_v1_1 | 5fd1a9de42a61c194dd7b810 | Demo System - Profile Dataset for Website (Global v1.1) | | false journey_step_events | 5fd1a7f30268c5194bbb7e5e | Journey Step Events | | false ``` At the colon, press space bar to see the next page of the resultset, or enter `q` to revert to the command prompt. Every dataset in Platform has its corresponding Query Service table. You can find a dataset's table via the Datasets ui: ![ui-dataset-tablename.png](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-9f8414e77be97aa26ab95b10395a1edbc33a40cb%2Fui-dataset-tablename.png?alt=media) The `demo_system_event_dataset_for_website_global_v1_1` table is the Query Service table that corresponds with the `Demo System - Event Schema for Website (Global v1.1)` dataset. To query some information about where a product was viewed, we will select the **geo** information. Copy the statement below and paste it at the prompt in your **PSQL command-line interface** and hit enter: ```sql select placecontext.geo from demo_system_event_dataset_for_website_global_v1_1 where eventType = 'commerce.productViews' and placecontext.geo.countryCode <> '' limit 1; ``` In your query result, you will notice that columns in the Experience Data Model (XDM) can be complex types and not just scalar types. In the query above we would like to identify geo locations where a **commerce.productViews** did occur. To identify a **commerce.productViews** we have to navigate through the XDM model using the **.** (dot) notation. ``` aepenablementfy21:all=> select placecontext.geo aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1 aepenablementfy21:all-> where eventType = 'commerce.productViews' aepenablementfy21:all-> and placecontext.geo.countryCode <> '' aepenablementfy21:all-> limit 1; geo ---------------------------------------- ("(57.4694803,-3.1269422)",Tullich,GB) (1 row) ``` Notice the result is a flat object rather than a single value? The **placecontext.geo** object contains four attributes: schema, country and city. And when an object is declared as a column it will return the entire object as a string. The XDM schema may be more complex than what you are familiar with but it's very powerful and was architected to support many solutions, channels, and use cases. To select the individual properties of an object, you use the **.** (dot) notation. Copy the statement below and paste it at the prompt in your **PSQL command-line interface**: ```sql select placecontext.geo._schema.longitude ,placecontext.geo._schema.latitude ,placecontext.geo.city ,placecontext.geo.countryCode from demo_system_event_dataset_for_website_global_v1_1 where eventType = 'commerce.productViews' and placecontext.geo.countryCode <> '' limit 1; ``` The result of the above query should look like this. The result is now a set simple values: ``` aepenablementfy21:all=> select placecontext.geo._schema.longitude aepenablementfy21:all-> ,placecontext.geo._schema.latitude aepenablementfy21:all-> ,placecontext.geo.city aepenablementfy21:all-> ,placecontext.geo.countryCode aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1 aepenablementfy21:all-> where eventType = 'commerce.productViews' aepenablementfy21:all-> and placecontext.geo.countryCode <> '' aepenablementfy21:all-> limit 1; longitude | latitude | city | countrycode ------------+------------+---------+------------- -3.1269422 | 57.4694803 | Tullich | GB (1 row) ``` Don't worry, there is an easy way to obtain the path towards a specific property. In the following part you will learn how. You will need to edit a query, so let's first open an editor. **On Windows** Click the **search** icon in the windows toolbar, type **notepad** in the **search** field, click the **notepad** result: ![windows-start-notepad.png](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-2f1b98b04f7ce3b9ff5696421400470cdab53c18%2Fwindows-start-notepad.png?alt=media) **On Mac** Install [Brackets](https://github.com/adobe/brackets/releases/download/release-1.14/Brackets.Release.1.14.dmg) or use another Text Editor of choice if you don't have it installed and follow the instructions. After installation, search for **Brackets** via Mac's spotlight search and open it. Copy the following statement to notepad or brackets: ```sql select your_attribute_path_here from demo_system_event_dataset_for_website_global_v1_1 where eventType = 'commerce.productViews' and placecontext.geo.countryCode <> '' limit 1; ``` Go back to your Adobe Experience Platform UI (should be open in your browser) or navigate to . Select **Schemas**, enter `Demo System - Event Schema for Website (Global v1.1)` in the **search** field and select `Demo System - Event Schema for Website (Global v1.1) Schema` from the list. ![browse-schema.png](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-aa45311aeecd9ab2367b56891bbad4356de10c1a%2Fbrowse-schema.png?alt=media) Explore the XDM model for **Demo System - Event Schema for Website (Global v1.1)**, by clicking on an object. Expand the tree for **placecontext**, **geo** and **schema**. When you select the actual attribute **longitude**, you will see the complete path in the highlighted red box. To copy the attribute's path, click on the copy path icon. ![explore-schema-for-path.png](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-fe6359860dd69dc09ad01993e20048bf01e29036%2Fexplore-schema-for-path.png?alt=media) Switch to your notepad/brackets and remove **your\_attribute\_path\_here** from the first line. Position your cursor after **select** on the first line and paste (CTRL-V). Copy the modified statement from notepad/brackets and paste it at the prompt in your **PSQL command-line interface** and hit enter. The result should look like: ``` aepenablementfy21:all=> select placeContext.geo._schema.longitude aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1 aepenablementfy21:all-> where eventType = 'commerce.productViews' aepenablementfy21:all-> and placecontext.geo.countryCode <> '' aepenablementfy21:all-> limit 1; longitude ------------ -3.1269422 ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.adobesandbox.com/comprehensive-technical-tutorial-archive/module4/ex2.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.