Select count of array nested inside another array

SQL++
1

I have data in my bucket that is of this form:

{
  ...,
  "details": {
    "items": [
      {
        ...,
        "subitems": ["a", "b", "c"]
        ...,
      },
      {
        ...,
        "subitems": ["d", "e", "f", "g"]
        ...,
      }
    ]
  }
  ...,
}

I would like to select the count of all subitems in each document. How can I achieve this? What I tried was this:

SELECT COUNT(ARRAY_AGG(details.items[*].subitems)) AS count FROM bucket;

This does not work as expected. For example, the above example json has all subitems:

["a", "b", "c", "d", "e", "f", "g"]

And therefore should return 7.

2

See ARRAY_FLATTEN and ARRAY_COUNT.

3

Awesome, thanks! I don’t see ARRAY_FLATTEN in the docs. Also, is there a way to return 0 if there are no subitems?

I ended up using:

SELECT ARRAY_COUNT(ARRAY_FLATTEN(details.items[*].subitems)) AS count FROM Bucket ORDER BY count DESC;
4

I think I found the solution for what I want. Ended up using:

select array_count(array_flatten(array i.subitems for i in details.items end, 1)) as count from Bucket order by count desc;
1 Like
5

Hi @prasad, please verify that ARRAY_FLATTEN is in the docs.

6

its missing @geraldss. Will add it.