3

My query and my data set (copied from sqlfiddle):

CREATE TABLE one (
    id int4 primary key, 
    p_id int4, 
    k_id int4
);
CREATE TABLE two(
   id int4 primary key, 
   p_id int4, 
   k_id int4,
   t_id int4,
   pos int4
);

INSERT INTO one(id, p_id, k_id) VALUES 
    (1, 1, 1), 
    (2, 1, 2), 
    (3, 1, 3), 
    (4, 1, 4);
INSERT INTO two(id, p_id, k_id, t_id, pos) VALUES 
    (1, 1, 1, 1, 1), -- t_id = 1 and pos = 1
    (2, 1, 2, 1, 2),
    (3, 1, 3, 1, 1), -- t_id = 1 and pos = 1
    (4, 1, 4, 1, 3),
    (5, 1, 1, 2, 3), -- shares p_id and k_id with row 1
    (6, 1, 2, 2, 1),
    (7, 1, 3, 2, 5), -- shares p_id and k_id with row 3
    (8, 1, 4, 2, 6);

Table one is a join table, I've added it because in the future I might add more columns into it, and then it will be easier to modify this report.

What I want is to be able to filter my dataset like so:

  1. Get all the rows where pos = 1 for t_id = 1

Expected output:

p_id, k_id, stats
1,     1,    [{p_id: 1, k_id: 1, t_id: 1, pos: 1}, {p_id: 1, k_id: 1, t_id: 2, pos: 3}
1,     3,    [{p_id: 1, k_id: 3, t_id: 1, pos: 1}, {p_id: 1, k_id: 3, t_id: 2, pos: 5}

As you can see I want to append to my results the data for others t_ids where p_id and k_id are the with the found results.

Update:

What will be the best way of removing duplicates?

Adding (9, 1, 1, 1, 20) to my dataset will result in having the same type twice in a row;

One solution to get only uniq types and the smallest pos values is:

WITH uniqt AS (
  SELECT   p_id, k_id, t_id, min(pos) as pos
  FROM     two
  GROUP BY 1, 2, 3

)

Because I am selecting from table one 50 rows (limit 50) and then join a table with SELECT like in @Erwin Brandstetter example, having this CTE will slow down my query?

  • We prefer to have the basic table definition and the query you tried in the question. Also, the fiddle shows aggregated JSON in the result, which you don't mention`? – Erwin Brandstetter Jul 16 '15 at 12:41
  • Also you don't mention table one in your description at all ... – Erwin Brandstetter Jul 16 '15 at 12:52
3

You could use an inner join to filter for rows that share a (p_id, k_id) value with rows that have a (t_id, pos) of (1,1) (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
join    two as filter
on      filter.t_id = 1
        and filter.pos = 1
        and filter.p_id = base.p_id
        and filter.k_id = base.k_id
group by
        base.p_id
,       base.k_id;

Or an equivalent way using an exists subquery (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
where   exists
        (
        select  *
        from    two as filter
        where   filter.t_id = 1
                and filter.pos = 1
                and filter.p_id = base.p_id
                and filter.k_id = base.k_id
        )
group by
        base.p_id
,       base.k_id;

Updated to use json_agg from @ErwinBrandstetter's answer.

  • json_agg(two) includes the id column that's not supposed to be in the result. That's the second reason for the subquery in my answer (besides the ORDER BY). – Erwin Brandstetter Jul 16 '15 at 13:59
2

It's unclear how table one is relevant at all. So I am ignoring that table

To build the JSON value you seem to be after (according to the query in the fiddle):

SELECT p_id, k_id, json_agg(sub) AS result
FROM  (
   SELECT p_id, k_id, t_id, pos
   FROM   two t2
   WHERE  EXISTS (
      SELECT 1
      FROM   two
      WHERE  pos = 1
      AND    t_id = 1
      AND    p_id = t2.p_id
      AND    k_id = t2.k_id
      )
   ORDER BY 1,2,3,4
   ) sub
GROUP  BY p_id, k_id;

Produces the desired result exactly.

SQL Fiddle.

Explanation

  • The inner query is similar to what @Andomar already posted: An EXISTS semi-join being the key element.
  • ORDER BY relevant columns right away, that's cheaper than adding an ORDER BY clause to the aggregate function.
  • Use json_agg() for the final aggregate. You don't even need row_to_json(), just use the alias sub of the derived table in the subquery as argument to json_agg().

  • In Postgres 9.4 you could use json_build_object() to get key names as desired in your JSON document. See:
    Select columns inside json_agg

  • Wouldn't the query optimizer be free to drop the order by in the subquery? – Andomar Jul 16 '15 at 13:48
  • @Erwin Brandstetter Table one is a join table, I've added it because in the future I might add more columns into it, and then it will be easier to select lets say a p_id and new_column_id, and get the a report for that selection. – RockNinja Jul 16 '15 at 13:50
  • @Andomar: By strict standard SQL rules, yes. But it works in the Postgres implementation as is documented here: This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work. – Erwin Brandstetter Jul 16 '15 at 13:53
  • @ErwinBrandstetter: Looks like json_agg supports an order by clause, like json_agg(row order by row.col1)! That avoids the "will usually work" part. – Andomar Jul 16 '15 at 14:31
  • 1
    @Andomar: (Almost) all aggregate functions do (where it makes sense). The point is that per-row ORDER BY is more expensive than a single ORDER BY in the subquery. – Erwin Brandstetter Jul 16 '15 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.