Flatten nested arrays in cosmosdb sql

Question

I have the following cosmosdb document:

{
  id: "id",
  outer: [
    {
      "inner": [ "a", "b", "c" ]
    },
    {
      "inner": [ "d", "e", "f" ]
    }
  ]
}

And I need to create a SQL request which would return all of the combined values of the "inner" arrays, like this:

{
   "allInners": [ "a", "b", "c", "d", "e", "f" ]
}

I was able to unwind the first array level using the "IN" operator, but I am not sure how unwind it one more level and to handle double or even triple nested arrays. The following is my subquery to aggregate those items

SELECT
  ... other stuff.
  ARRAY(SELECT VALUE innerObj.inner FROM innerObj IN c.outer) AS allInners,
  ...
FROM c

MarengoHue · Accepted Answer · 2020-08-07 09:41:56Z

2

I found the following solution to my problem (using the nested "IN" and a subquery):

ARRAY(
   SELECT VALUE inner
   FROM inner IN (
      SELECT VALUE outers.inner
      FROM outers IN c.outer
  )
)

answered Aug 7, 2020 at 9:41

MarengoHue

1,85515 silver badges35 bronze badges

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

Comments

Steve Johnson · Accepted Answer · 2020-08-07 01:33:42Z

1

Please try something like this sql:

select ARRAY(SELECT VALUE e FROM c join d in c["outer"] join e in d["inner"]) AS allInners from c

Here is result of my test:

Hope this can help you.:)

answered Aug 7, 2020 at 1:33

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

1 Comment

Steve Johnson Over a year ago

get each item in inner array,and put them into a new array.You can run the sql:SELECT c,d,e FROM c join d in c["outer"] join e in d["inner"] to look at documents look like.More details, you can refer to arrays and objects and joins.

Collectives™ on Stack Overflow

Flatten nested arrays in cosmosdb sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related