CODEWITHSUNDEEP
sqljsondatabasesqlite
Nico
Nico

Reputation: 157

Returning grouped junction table results in a JSON-formatted string

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?

Upvotes: 0

Views: 77

Answers (1)

forpas
forpas

Reputation: 164069

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.

Upvotes: 1

Related Questions