Returning grouped junction table results in a JSON-formatted string-CodePudding

Say I have a table of people

 ---- ------ 
| id | name |
 ---- ------ 
|  1 | John |
|  2 | Mary |
|  3 | Jane |
 ---- ------

And various tables for clothing of various types, e.g. a table of shoes

 ---- ---------- -------------------- --------- 
| id |  brand   |        name        |  type   |
 ---- ---------- -------------------- --------- 
|  1 | Converse | High tops          | sneaker |
|  2 | Clarks   | Tilden cap Oxfords | dress   |
|  3 | Nike     | Air Zoom           | running |
 ---- ---------- -------------------- ---------

And then I have a junction table, where I’m storing all the clothing that each person has:

 -------- -------- ------- ------- 
| person | shirts | pants | shoes |
 -------- -------- ------- ------- 
|      1 |      3 |       |       |
|      1 |      4 |       |       |
|      1 |        |     3 |       |
|      1 |        |       |     5 |
|      2 |        |     2 |       |
|      2 |        |       |     2 |
|      2 |      3 |       |       |
...

I need a query that compiles this junction table into a return like so:

 ---- ------ -------------------- 
| id | name |   clothing items   |
 ---- ------ -------------------- 
|  1 | John | [JSON in a string] |
|  2 | Mary | [JSON in a string] |
|  3 | Jane | [JSON in a string] |
 ---- ------ --------------------

Where the [JSON in a string] for each row should look like this:

{
  "shirts":[3,4],
  "pants":[3],
  "shoes":[5]
}

How do I go about constructing this query in SQLITE?

CodePudding user response：

Use SQLite's JSON Functions to aggregate in the junction table and do a LEFT join of people to that resultset:

WITH cte AS (
  SELECT person, 
         json_object(
           'shirts', json('[' || GROUP_CONCAT(shirts) || ']'),
           'pants', json('[' || GROUP_CONCAT(pants) || ']'),
           'shoes', json('[' || GROUP_CONCAT(shoes) || ']')
         ) clothing_items
  FROM junction 
  GROUP BY person
)
SELECT p.id, p.name, c.clothing_items
FROM people p LEFT JOIN cte c
ON c.person = p.id;

I use GROUP_CONCAT() instead of json_group_array() to remove nulls.

See the demo.