sql从子查询中选择特定的行,并插入到行\u到\ujson中

xe55xuns  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(266)

我已经为一个视图创建了几个子查询,我试图使用-

row_to_json(err.*) as overall_totals

子查询-

left join 
     (      
    SELECT q.id, SUM(q.total) AS total,
       jsonb_agg(jsonb_build_object('count', q.total, 'type', q.name)) AS totals
        FROM (
        SELECT r.id AS id, e.name, COUNT(de.value_id) AS total
          FROM table_c de
          JOIN tests.error e
            ON e.id = de.value_id
          JOIN table_a p 
            ON de.process_id = p.id 
          Join table_b r on p.root = r.id
         GROUP BY e.name, r.id  ) q
         GROUP BY q.id
 ) err on err.id = rs.id

这可以正常工作并以json的形式返回,但是,我只想返回“err”子查询的total和totals,而不是q.id。我需要子查询中的q.id,这样我就可以将查询连接到视图的其余部分,但我不希望将其存储在总体目录中。我怎么才能避开这个?是否可以将某些值选择到第\u到\u行?

qcbq4gxm

qcbq4gxm1#

如果你愿意回来的话 jsonb 而不是 json ,则可以使用 - 操作员:

to_jsonb(err.*) - 'id' as overall_totals
nue99wik

nue99wik2#

您可以添加更多的子查询,类似这样

SELECT row_to_json(err2.*) as overall_totals
FROM
(
    SELECT err.total, err.totals
    FROM XXX_TABLE RS
    left join 
    (      
        SELECT q.id, SUM(q.total) AS total, jsonb_agg(jsonb_build_object('count', q.total, 'type', q.name)) AS totals
        FROM 
        (
            SELECT r.id AS id, e.name, COUNT(de.value_id) AS total
            FROM table_c de
            JOIN tests.error e
            ON e.id = de.value_id
            JOIN table_a p 
            ON de.process_id = p.id 
            Join table_b r on p.root = r.id
            GROUP BY e.name, r.id  
        ) q
        GROUP BY q.id
    ) err on err.id = rs.id
) err2

相关问题