How to solve error "Result: misuse of aggregate function json_group_array()"?

user_11277878 0
Viewed 246

I'm trying to generate a JSON with nested group arrays, however, I get the following error:

Result: misuse of aggregate function json_group_array()

I'm new to SQLite and I cannot see what is wrong with my query. When I remove the inner json_group_array and 'details' the query works.

select  json_object('a',
            json_group_array(
                json_object(
                    'b', rm.id, 
                    'c', '{}',
                    'd', l.code,
                    'details', 
                    json_group_array(
                        json_object('e', rmd.id, 'f', rmd.s_id, 'g', s.name)
                    )
                )
            )
        ) as json
from...
sqlite sql
1 Answers

You can't nest an aggregate functions like json_group_array() inside another.
Maybe you want to aggregate on the results of the query:

SELECT json_object('a', json_group_array(json_result)) json
FROM (
  SELECT json_object(
                      'b', rm.id, 
                      'c', '{}',
                      'd', l.code,
                      'details', 
                      json_group_array(
                          json_object('e', rmd.id, 'f', rmd.s_id, 'g', s.name)
                      )
                    ) json_result
  FROM ....
)
user_104988280
Related