Skip to content
Advertisement

Aggregate SQLite query across multiple tables using JSON1

I can’t get my head around the following problem. The other day I learned how to use the JSON1 family of functions, but this time it seems to be more of an SQL issue.

This is my database setup:

Based on this data I’d like to get the following output, which is all interests of all persons aggregated into a single JSON array:

Now the following is what I tried to do. Needless to say, this doesn’t give me what I want:

The undesired output is:

Any help is highly appreciated!

Advertisement

Answer

For using json_group_array you must group line , in your case by person , except you want only one row with all your results .

Example 1)

This first version , will give you 1 json object by person , so the result will be N rows for N persons :

Example 2)

This second version , will give return 1 big json array that contains all persons , but you fetch only one row .

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement