I have the following table MyTable
:
id │ value_two │ value_three │ value_four ────┼───────────┼─────────────┼──────────── 1 │ a │ A │ AA 2 │ a │ A2 │ AA2 3 │ b │ A3 │ AA3 4 │ a │ A4 │ AA4 5 │ b │ A5 │ AA5
I want to query an array of objects { value_three, value_four }
grouped by value_two
. value_two
should be present on its own in the result. The result should look like this:
value_two │ value_four ───────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── a │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}] b │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]
It does not matter whether it uses json_agg()
or array_agg()
.
However the best I can do is:
with MyCTE as ( select value_two, value_three, value_four from MyTable ) select value_two, json_agg(row_to_json(MyCTE)) value_four from MyCTE group by value_two;
Which returns:
value_two │ value_four ───────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── a │ [{"value_two":"a","value_three":"A","value_four":"AA"}, {"value_two":"a","value_three":"A2","value_four":"AA2"}, {"value_two":"a","value_three":"A4","value_four":"AA4"}] b │ [{"value_two":"b","value_three":"A3","value_four":"AA3"}, {"value_two":"b","value_three":"A5","value_four":"AA5"}]
With an extra value_two
key in the objects, which I would like to get rid of. Which SQL (Postgres) query should I use?
The simplest way to return JSON is with row_to_json() function. It accepts a row value and returns a JSON value. select row_to_json(words) from words; This will return a single column per row in the words table.
The PostgreSQL json_build_object() function creates and returns a JSON object from a variadic parameter list consisting of alternating keys and values. This function is similar to the jsonb_build_object() function.
jsonb[] is not an "extra" datatype, it's simply an array of JSONB values. Similar to text[] or integer[] . You can create arrays from every type.
json_build_object(VARIADIC "any") Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.
json_build_object()
in Postgres 9.4 or newerOr jsonb_build_object()
to return jsonb
.
SELECT value_two, json_agg(json_build_object('value_three', value_three , 'value_four' , value_four)) AS value_four FROM mytable GROUP BY value_two;
The manual:
Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.
row_to_json()
with a ROW
expression would do the trick:
SELECT value_two , json_agg(row_to_json((value_three, value_four))) AS value_four FROM mytable GROUP BY value_two;
But you lose original column names. A cast to a registered row type avoids that. (The row type of a temporary table serves for ad hoc queries, too.)
CREATE TYPE foo AS (value_three text, value_four text); -- once in the same session
SELECT value_two , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four FROM mytable GROUP BY value_two;
Or use a subselect instead of the ROW
expression. More verbose, but without type cast:
SELECT value_two , json_agg(row_to_json((SELECT t FROM (SELECT value_three, value_four) t))) AS value_four FROM mytable GROUP BY value_two;
More explanation in Craig's related answer:
db<>fiddle here
Old sqlfiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With