Question 224869 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Joining multiple junction tables and grouping results in a JSON-formatted array

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 for each clothing type, I have a junction table recording what items of that type each person owns:

+--------+---------+
| person |  shirt  |
+--------+---------+
|      1 |       3 |
|      1 |       4 |
|      2 |       2 |
...

I need a query that compiles these tables 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:

[
   {"type":"shirt","id":3},
   {"type":"shirt","id":4},
   {"type":"pant","id":2},
   {"type":"shoe","id":5}
]

How do I go about constructing this query in SQLITE? If needed, it’s easy for me to define my own custom aggregate function.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Figured it out:

    WITH cte AS (SELECT person, ('[' || GROUP_CONCAT(clothing,',') || ']') AS clothing
  FROM (SELECT person, json_object('type','shirt','id',shirts) AS clothing
    FROM junction_shirts
    UNION
    SELECT person, json_object('type','pant','id',pants) AS clothing
    FROM junction_pants
    UNION
    SELECT person, json_object('type','shoe','id',shoes) AS clothing
    FROM junction_shoes)
GROUP BY person)
SELECT p.id, p.name, c.clothing
FROM people p LEFT JOIN cte c
ON c.person = p.id;

    See dbfiddle for demo


  2. 0

    You can get you result by using this query. I t first json object then groups them into an array from each user id and name array:

     select 
t1.id
    ,t1."name"
    ,json_agg(new_object)
    from
(
select 
    se.id
    ,se."name"
    ,json_build_object('type',sa.type,'id',sa.id) as  new_object
from junction_table as  ss
inner join  shoe_table as sa on ss.shirt = sa.id
inner join people_table as se on ss.person = se.id 
)t1
group by t1.id,t1."name";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search