Nested query results - just dont get it?

Hi, now I know this must be simple and I have Googled and searched the forum but I am struggling the responses that I have come across, been looking at this too.

What I am trying to

MATCH (mig:Process)-[rel:SUB_PROCESS|SPECIAL_PROCESS]-(process:Process {name: "Source"})-[state:QUEUED]->(entity:Entity)

This is start of my query. I am getting all processes of a specific type 'Source' that have entities whose current state is QUEUED.

WITH process, collect(entity) as entities
RETURN {p:process, e:entities}

Now this gives me almost the result that I want in that I get my process as the root of result and all of the associated entities nested beneath it

{
{
"p": {
"identity": 520,
"labels": [
"Process"
],
"properties": {
"name": "Source"
}
},
"e": [
{
"identity": 418,
"labels": [
"EntityProcess",
"Entity"
],
"properties": {
"name": "IdentityVerificationCertificateAddress",
"id": "345"
}
},
etc, etc,
}

What I now need to do is to also return the 'state' in my results BUT nested under each individual entity so. I have tried this

RETURN {p:process, e:collect(entity), s:collect(state)}

But obviously this doesnt work as it just gives me a collection of state at the same level as entity.
There is probably some sort of sub query or something I am missing but I just cant get my head around it, can anyone help?

This should be as simple as

process
    - entity
          - state
          - state
          - state
    - entity
          - state

...

Thanks for clearly describing what you're after. There are a few concepts and Cypher features that will help.

You can perform multiple aggregations in your query at different points, so you can aggregate state per entity and then later aggregate entities per process.

Also, we have something called map projection which can let you project properties and nest custom properties in maps, which can help you get your nested structure.

Putting these together, maybe something like:

MATCH (mig:Process)-[rel:SUB_PROCESS|SPECIAL_PROCESS]-(process:Process {name: "Source"})-[state:QUEUED]->(entity:Entity)
WITH process, entity, collect(DISTINCT state) as states
WITH process, collect(entity {.*, states}) as entities
RETURN process, entities

Excellent, thank you very much for your assistance this has worked great for me.

Sorry, one additional question, how would I be able to get states and not just the specific one of 'QUEUED'? I have tried [state:*] but that errors?

That depends, do all possible relationship types between a :Process and an :Entity represent a state? If so, then you can leave off the type of the relationship and state will be matched to any type between the two nodes.

If only a subset of relationship types represent a state, then you will need to enumerate those in a WHERE clause:

...
WHERE type(state) IN ['QUEUED', 'ACTIVE', 'DISCARDED', ...]