SQL Subquery insert results into json

Question

`I'm trying to return the value of a subquery into a json_build_object, the subquery returns the right values but when the full select is ran the results are incorrect. It might be worthwhile noting that the full select is a left join of a view.

        SELECT r.id, sum(q.total) AS overall_total,
        jsonb_agg(json_build_object('count', q.total, 'type', der.name)) AS totals
        FROM test.table_a p
            JOIN test.table_b r
            ON p.root_id = r.id
                inner join (
                    select r.id,  de.value_id as error_type, count(de.value_id) as total
                    from test.table_c de
                    inner join tests.error dr on de.value_id = dr.id
                    inner join test.table_a p on de.process = p.id
                    inner join test.table_b r on p.root = r.id
                    group by r.id, de.value_id 
            ) q on q.id = r.id
         inner join test.table_c er on er.process = p.id
         inner join tests.error der on er.value_id = der.id
    GROUP BY r.id) er on er.id = rs.id

The subquery returns -

Since I'm trying to count the number of occurrences for value_id based on ID, I believe I have gotten the right information.

Full query results and desired output -

I want to return the total errors for that given ID and an array of json objects, inside each object is the type (value_id text representation) and a count for how many of that type there is.

Currently the query is returning the wrong overall_total and it seems to be duplicating the types within the array. Where did I go wrong?

Barbaros Özhan · Accepted Answer · 2020-05-20 20:04:32Z

1

I think you don't need table_b, try this query below :

SELECT q.id, SUM(q.total) AS overall_total,
       JSONB_AGG(JSONB_BUILD_OBJECT('count', q.total, 'type', q.name)) AS totals
  FROM (
        SELECT a.root_id AS id, e.name, COUNT(c.value_id) AS total
          FROM table_c c
          JOIN error e
            ON e.id = c.value_id
          JOIN table_a a 
            ON c.process = a.id  
         GROUP BY e.name, a.root_id  ) q
 GROUP BY q.id

Demo

edited May 20, 2020 at 20:04

answered May 20, 2020 at 18:33

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Over a year ago

This worked perfectly! As I said in the post this subquery is part of a larger view, I've given the query above the alias 'er' and tried to return it as json using "row_to_json(er.*)", this, of course, includes the q.id as well, is there a way to exclude it? I don't want it in the result but I need it in the subquery to join on the rest of the view. Thanks again.

Barbaros Özhan Over a year ago

Nice to help @Chris. Of course you can exclude it by just commenting out this part as --q.id, and keeping the rest. do you ask for this, or I'm misunderstanding ..?

Chris Over a year ago

Not quite I've updated the original post to show that the select itself is a subquery, I need the q.id so that I can join that select onto the rest of the view so er.id is needed but I don't want to include it in the row_to_json(er.*), since er.* will select everything returned.

Barbaros Özhan Over a year ago

just removing q.id, from the SELECT - list, which won't corrupt the query's working, is enough @Chris .

Collectives™ on Stack Overflow

SQL Subquery insert results into json

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related