Question 282088 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – psql: How can I get a single json column in aggregation after a group by query

I am trying to get a single row for each group by clause in the query. Each SELECT output has a json in it. I am trying this:

SELECT MIN(C.json_column->'key') AS Key, A.field1, B.field2
    FROM json_table AS C
    LEFT JOIN another_table AS D ON D.id=C.id
    INNER JOIN another_table2 AS A ON A.id=D.col2
    INNER JOIN another_table3 AS B on B.id=D.col3
GROUP BY (A.field1, B.field2)

The joins do not matter here. The issue is that MIN(C.json_column->'key') is returning:

No function matches the given name and argument types. You might need to add explicit type casts.

Since I am grouping by 2 other fields, I had to aggregate the json field. But I only want the first (or any other) single json row. MIN seems to not work on json type. what can I use?

Tags:

2

Answers


  1. 0

    Operator -> return JSON element as JSON, Use ->> instead to return the JSON element as text then cast it to integer :

    SELECT MIN((C.json_column->>'key')::int) AS Key, A.field1, B.field2
    FROM json_table AS C
    LEFT JOIN another_table AS D ON D.id=C.id
    INNER JOIN another_table2 AS A ON A.id=D.col2
    INNER JOIN another_table3 AS B on B.id=D.col3
GROUP BY (A.field1, B.field2)
    Login or Signup to reply.
  2. 0

    You’re looking for a distinct on construct. Demo at db<>fiddle:

    SELECT DISTINCT ON(A.field1, B.field2) 
    C.json_column->'key' AS Key, A.field1, B.field2
    FROM json_table AS C
    LEFT JOIN another_table AS D ON D.id=C.id
    INNER JOIN another_table2 AS A ON A.id=D.col2
    INNER JOIN another_table3 AS B on B.id=D.col3
ORDER BY A.field1, B.field2

    This picks one json_column for each unique pair of (A.field1, B.field2), discarding the rest. For this case, min()/max() aggregate workaround would work the same, but in general distinct on is more versatile: with more of columns, aggregates quickly lose performance sorting each secondary column separately and they also produce non-existent value combinations instead of real samples from the group.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search