GROQ: Flatten nested array and extract specific property (usedTime) from tasks

10 replies

Last updated: Jun 29, 2021

I'm trying to use GROQ to save me writing some loops in the front end.
I have an array of tasks, in this array there is a property named "usedTime" indicating how long has been spent on a task, only some of the tasks have this value.

The result I'm after is one array (one dimentional) containing the usedTime data, I can handle null values in my front end but if sanity supports omitting them that would be awesome

Attached is the result of the query

*[_type == "internalCustomers" && '<mailto:myMail@mail.com|myMail@mail.com>' in users[]->email]{
  "totalHours": tasks[].tasks[], 
}

I know my naming work needs improving
😉
I've tried several methods

  "totalHours": tasks[].tasks->usedTime, 
  "totalHours": tasks[].tasks[]->usedTime, 
  "totalHours": tasks[].tasks[]->.usedTime, 
  "totalHours": tasks[].tasks.usedTime,

All my attempts return "null"
Any idea on how I can achive this?
🙂

AI Update

Based on your query structure, you're dealing with nested arrays and trying to flatten them while extracting a specific property. The key issue is that you need to properly flatten the nested tasks[].tasks[] structure and then project the usedTime property.

Here's how to achieve what you're after:

*[_type == "internalCustomers" && 'myMail@mail.com' in users[]->email]{
  "totalHours": tasks[].tasks[].usedTime
}

This syntax tasks[].tasks[].usedTime will:

Iterate through the outer tasks array
For each item, iterate through the inner tasks array
Extract the usedTime property from each task
Automatically flatten the result into a one-dimensional array

The double array projection ([][]) handles the flattening for you - GROQ automatically flattens nested arrays when you chain array projections like this, as explained in the pipeline components documentation.

Filtering out null values

If you want GROQ to omit null/undefined values (which it sounds like you do), you can add a filter using the defined() function:

*[_type == "internalCustomers" && 'myMail@mail.com' in users[]->email]{
  "totalHours": tasks[].tasks[defined(usedTime)].usedTime
}

This filters the inner tasks array to only include items where usedTime is defined before extracting the values.

Why your attempts returned null

Looking at your attempts:

tasks[].tasks->usedTime - The -> operator is for following references, not accessing properties
tasks[].tasks[]->usedTime - Same issue with ->
tasks[].tasks[]->.usedTime - Same reference operator issue
tasks[].tasks.usedTime - Missing the second [] to properly iterate through the nested array

The correct syntax for accessing properties in nested arrays is to use [] for each array level, then access the property directly with dot notation. The -> operator is specifically for dereferencing document references, not for property access.

Show original thread

10 replies

I’m wondering if there might be another array to include in your query, given the double-nesting after

totalHours

in your third array item.
Schema:

export default {
  name: 'andreas',
  type: 'document',
  fields: [
    {
      name: 'tasks',
      type: 'array',
      of: [
        {
          name: 'firstObj',
          type: 'object',
          fields: [
            {
              name: 'tasks',
              type: 'array',
              of: [
                {
                  name: 'secondObj',
                  type: 'object',
                  fields: [
                    {
                      name: 'tasks',
                      type: 'array',
                      of: [
                        {
                          name: 'thirdObj',
                          type: 'object',
                          fields: [
                            {
                              name: 'usedTime',
                              type: 'number'
                            },
                          ],
                        },
                      ],
                    },
                  ],
                },
              ]
            }
          ]
        }
      ]
    }
  ]
}

GROQ query:

*[_type == 'andreas'] {
  'totalHours': tasks[].tasks[].tasks[].usedTime
}

Returns:

"result": [
  0: {
    "totalHours": [
      0: 34
    ]
  }
]

As for removing null values, I believe you could try

[count(totalHours) > 0]

at the end of your query. In my example above, it came after the

at the end of the projection.

*[_type == 'andreas'] {
  'totalHours': tasks[].tasks[].tasks[].usedTime
}[count(totalHours) > 0]

Hey Geoff, yes it seems to be another arrayed hidden in here. Can't really see why
Your suggestion makes sense, but does not work. I can't see a name for the additional array either

Results of

*[_type == "internalCustomers" && '<mailto:andreas.jacobsen@inklud.no|andreas.jacobsen@inklud.no>' in users[]->email]{  
  'totalHours': tasks[].tasks[].tasks[]
}

is null

If I remove the last task-attribute I get this response

https://ad47v47a.api.sanity.io/v1/data/query/production?query=*%5B_type%20%3D%3D%20%22in[…]%20%20%27totalHours%27%3A%20tasks%5B%5D.tasks%5B%5D%0A%7D

Hey Geoff, yes it seems to be another arrayed hidden in here. Can't really see why
Your suggestion makes sense, but does not work. I can't see a name for the additional array either

Results of

*[_type == "internalCustomers" && '<mailto:andreas.jacobsen@inklud.no|andreas.jacobsen@inklud.no>' in users[]->email]{  
  'totalHours': tasks[].tasks[].tasks[]
}

you might want to use the new api version btw

Thanks, using v2021-03-25 in my front end, will change to that in studio as well. Same problem with v2021-03-25
Edit: Sorry my mistake, using the newer API I get the result I want
🙂

Also, this might be the thing you want?

*[_type == "internalCustomers" && '<mailto:andreas.jacobsen@inklud.no|andreas.jacobsen@inklud.no>' in users[]->email]{  
  'totalHours': tasks[tasks != null].tasks
}

GROQ is amazingI tried your not null example Knut, but weirdly I get one null value
But this is pretty easy to handle in the front end

Try

*[_type == "internalCustomers" && '<mailto:andreas.jacobsen@inklud.no|andreas.jacobsen@inklud.no>' in users[]->email]{  
  'totalHours': tasks[tasks != null].tasks[usedTime != null].usedTime
}[count(totalHours) > 0]

thanks! That worked, I'll remove count because I just remember that count and sum is not the same thing. GROQ just saved me a bunch of loops I'd have to maintain and try to understand again some time in the future 🙂 Should have thought of tasks being able to be null as well

Sanity – Build the way you think, not the way your CMS thinks

Sanity is the developer-first content operating system that gives you complete control. Schema-as-code, GROQ queries, and real-time APIs mean no more workarounds or waiting for deployments. Free to start, scale as you grow.

Get started for free Explore the demo

GROQ: Flatten nested array and extract specific property (usedTime) from tasks

Filtering out null values

Why your attempts returned null

Sanity – Build the way you think, not the way your CMS thinks

Was this answer helpful?