Cosmos DB query syntax WHERE clause with array in array

Question

The following json represents two documents in a Cosmos DB container. How can I write a query that gets any document that has an item with an id of item_1 and value of bar.

I've looked into ARRAY_CONTAINS, but don't get this to work with array's in array's. Als I've tried somethings with any. Although I can't seem to find any documentation on how to use this, any seems to be a valid function, as I do get formatting highlights in the cosmos db explorer in Azure Portal. For the any function I tried things like SELECT * FROM c WHERE c.pages.any(p, p.items.any(i, i.id = "item_1" AND i.value = "bar")).

The id fields are unique so if it's easier to find any document that contains any object with the right id and value, that would be fine too.

[
  {
    "type": "form",
    "id": "form_a",
    "pages": [
      {
        "name": "Page 1",
        "id": "page_1",
        "items": [
          {
            "id": "item_1",
            "value": "foo"
          }
        ]
      }
    ]
  },
  {
    "type": "form",
    "id": "form_b",
    "pages": [
      {
        "name": "Page 1",
        "id": "page_1",
        "items": [
          {
            "id": "item_1",
            "value": "bar"
          }
        ]
      }
    ]
  }
]

Jay Gong · Accepted Answer · 2019-12-04 01:48:24Z

2

I think join could handle with WHERE clause with array in array.Please test below sql:

SELECT c.id FROM c
join pages in c.pages
where array_contains(pages.items,{"id": "item_1","value": "bar"},true)

Output:

answered Dec 4, 2019 at 1:48

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

markbeij Over a year ago

Thanks, this is helpful. Any chance to get (SELECT) the whole document bij doing something like SELECT c.* FROM c join pages in c.pages ...?

Jay Gong Over a year ago

@markbeij Well,you can't use c.* with join.However, you could use select c.A,c.B,c.C from ..... to cover all your columns. Maybe kind of trouble but it should work.

markbeij Over a year ago

Thanks. I’m trying to keep my solution as generic as possible.

Collectives™ on Stack Overflow

Cosmos DB query syntax WHERE clause with array in array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related