This blog is the final part of a series of blogs where we\u2019ll demystify commonly confused concepts for developers learning how to query data using the SQL (core) API in Azure Cosmos DB. Today, we\u2019ll walk through tips and tricks for querying arrays.<\/p>\n
In case you missed them, here are the earlier two blogs in the series:<\/p>\n
Using an example, we\u2019ll show how to use these concepts when querying arrays:<\/p>\n
Example Scenario:<\/strong><\/span><\/p>\n Nick is a developer that created an app that stores shopping lists. His app is incredibly popular around the holidays and has soared to 1 million active users!<\/p>\n He has a Cosmos container that has the shopping lists modeled as JSON documents. Here\u2019s an example document:<\/p>\n The remainder of the blog will focus on ways to query arrays, starting with the simplest (and least expressive) and concluding with the more advanced concepts.<\/p>\n <\/p>\n Accessing a specific array position<\/strong><\/span><\/p>\n The simplest way to query an array is to specify a specific position in the array. For example, the below query finds all shopping lists that have a recipient named Andrew<\/strong> in the first position within the gifts<\/strong> array.<\/p>\n Query:<\/strong><\/p>\n This query is very simple to understand and inexpensive to run. However, filtering based on a specific array element isn\u2019t enough for many scenarios.<\/p>\n <\/p>\n Iterating over arrays<\/strong><\/span><\/p>\n Azure Cosmos DB provides support for iterating over arrays by using the IN keyword<\/a>\u00a0in the FROM<\/a> source. This is best illustrated with an example.<\/p>\n Query:<\/strong><\/p>\n This query will return the data from the gifts<\/strong> array for all items in the container. The t<\/strong> in t.gifts <\/strong>was arbitrary. We could have used any letter or word (except for c<\/strong>) to reference the item.<\/p>\n Results:<\/strong><\/p>\n This concept is especially useful if you need to filter on properties within the array.<\/p>\n For example, this query returns all gifts where the recipient<\/strong> is Andrew.<\/strong><\/p>\n Query:<\/strong><\/p>\n However, once you\u2019ve opted to iterate over an array by using the IN keyword<\/a>\u00a0in the FROM<\/a> source, it isn\u2019t possible to project or filter on any properties outside of that array. This limitation is solved by using JOINs.<\/p>\n <\/p>\n JOINs<\/strong><\/span><\/p>\n JOINs in Azure Cosmos DB are different from JOINs in relational databases. All JOINs in Cosmos DB are scoped within a single item. JOINs are a cross product between different sections of a single item.<\/p>\n For example, here\u2019s a query that JOINs the gifts <\/strong>array with the rest of the item.<\/p>\n Query:<\/strong><\/p>\n Results:<\/strong><\/p>\n In this case, the query returns all possible combinations for the id <\/strong>property and gifts <\/strong>array within each item. Using JOINs, you can construct more complex array queries, including queries that filter or project properties outside of the array.<\/p>\n Here\u2019s an example query that adds on a filter for city<\/strong>, a property outside of the gifts<\/strong> array.<\/p>\n Query:<\/strong><\/p>\n Subqueries<\/strong><\/span><\/p>\n A subquery<\/a> is a query nested within another query. Azure Cosmos DB supports correlated subqueries<\/a>. Correlated subqueries have the following uses when querying arrays:<\/p>\n Optimize JOIN expressions<\/strong><\/p>\n We can optimize most queries with JOINs and filters by rewriting them to include a subquery. This optimization is recommended if you use JOIN on a large array that later has a filter. By using a subquery, the filter is applied before the JOIN, instead of afterwards. For example, the query in the prior example could be rewritten.<\/p>\n Query:<\/strong><\/p>\n EXISTS expression<\/strong><\/p>\n The EXISTS expression<\/a>, takes a subquery and returns true if the subquery returns at least one result. Because EXISTS<\/strong> take a subquery, it is more expressive than using ARRAY_CONTAINS<\/a>, which is restricted to equality comparisons.<\/p>\n EXISTS<\/strong> stands out most from other array concepts because it can be used in the SELECT<\/strong> clause.<\/p>\n Query:<\/strong><\/p>\n The above query returns all shopping lists from Seattle. It projects both the id <\/strong>value and a value that indicates whether that shopping list contains a coffee maker<\/strong>.<\/p>\n ARRAY projection<\/strong><\/p>\n You can use the ARRAY<\/strong> expression<\/a> to project the results of a query as an array. Using the ARRAY<\/strong> expression, in combination with a JOIN, makes it easy construct arrays in the query\u2019s output. In this example, we\u2019ll create a ShoppingList<\/strong> array in the query projection.<\/p>\n Query:<\/strong><\/p>\n Results:<\/strong><\/p>\n Here are more resources to learn about querying arrays in Azure Cosmos DB:<\/p>\n This blog is the final part of a series of blogs where we\u2019ll demystify commonly confused concepts for developers learning how to query data using Azure Cosmos DB. Today, we\u2019ll walk through tips and tricks for querying arrays.<\/p>\n","protected":false},"author":12128,"featured_media":61,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,818],"tags":[],"class_list":["post-2306","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api","category-query"],"acf":[],"blog_post_summary":" This blog is the final part of a series of blogs where we\u2019ll demystify commonly confused concepts for developers learning how to query data using Azure Cosmos DB. Today, we\u2019ll walk through tips and tricks for querying arrays.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/2306","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/12128"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=2306"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/2306\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/61"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=2306"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=2306"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=2306"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}{\r\n \"id\": \"Tim\",\r\n \"city\": \"Seattle\",\r\n \"gifts\": [\r\n {\r\n \"recipient\": \"Andrew\",\r\n \"gift\": \"blanket\"\r\n },\r\n {\r\n \"recipient\": \"Deborah\",\r\n \"gift\": \"board game\"\r\n },\r\n {\r\n \"recipient\": \"Chris\",\r\n \"gift\": \"coffee maker\"\r\n }\r\n ]\r\n}<\/pre>\nSELECT *\r\nFROM c\r\nWHERE\u00a0c.gifts[0].recipient\u00a0=\u00a0\"Andrew\"<\/pre>\n
SELECT *\r\nFROM c\u00a0IN\u00a0t.gifts<\/pre>\n
[\r\n {\r\n \"recipient\": \"Andrew\",\r\n \"gift\": \"blanket\"\r\n },\r\n {\r\n \"recipient\": \"Deborah\",\r\n \"gift\": \"board game\"\r\n },\r\n {\r\n \"recipient\": \"Chris\",\r\n \"gift\": \"coffee maker\"\r\n },\r\n\u2026\r\n]<\/pre>\nSELECT *\r\nFROM c IN t.gifts\r\nWHERE\u00a0c.recipient\u00a0=\u00a0\"Andrew\"<\/pre>\n
SELECT c.id, t AS gift\r\nFROM c\r\nJOIN t IN c.gifts<\/pre>\n
[\r\n {\r\n \"id\": \"Tim\",\r\n \"gift\": {\r\n \"recipient\": \"Andrew\",\r\n \"gift\": \"blanket\"\r\n }\r\n },\r\n {\r\n \"id\": \"Tim\",\r\n \"gift\": {\r\n \"recipient\": \"Deborah\",\r\n \"gift\": \"board game\"\r\n }\r\n },\r\n\u2026<\/strong>\r\n]<\/strong><\/pre>\nSELECT c.id, t AS gifts\r\nFROM c\r\nJOIN t IN c.gifts\r\nWHERE\u00a0t.recipient\u00a0=\u00a0\"Andrew\"\u00a0AND\u00a0c.city\u00a0=\u00a0\"Seattle\"\r\n\r\n<\/pre>\n
SELECT c.id, gifts\r\nFROM c\r\nJOIN (SELECT VALUE t FROM t IN c.gifts WHERE t.recipient = 'Andrew') AS gifts\r\nWHERE c.city = \"Seattle\"<\/pre>\n
SELECT c.id, EXISTS(SELECT VALUE t FROM t IN c.gifts WHERE t.gift = \"Coffee Maker\") AS GivingCoffeeMaker\r\nFROM c\r\nWHERE\u00a0c.city\u00a0=\u00a0\"Seattle\"<\/pre>\n
[\r\n {\r\n \"id\": \"Tim\",\r\n \"GivingCoffeeMaker\": false\r\n },\r\n {\r\n \"id\": \"Thomas\",\r\n \"GivingCoffeeMaker\": true\r\n }\r\n\u2026\r\n]<\/pre>\nSELECT c.id, ARRAY(SELECT VALUE t.gift FROM t in c.gifts) as ShoppingList\r\nFROM c<\/pre>\n
[\r\n {\r\n \"id\": \"Tim\",\r\n \"ShoppingList\": [\r\n \"blanket\",\r\n \"board game\",\r\n \"coffee maker\"\r\n ]\r\n },\r\n {\r\n \"id\": \"Matt\",\r\n \"ShoppingList\": [\r\n \"pillow\",\r\n \"gift card\",\r\n \"computer keyboard\"\r\n ]\r\n }\r\n\u2026\r\n]<\/pre>\n\n