Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Return as array of JSON objects in SQL (Postgres)

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?

like image 942
ehmicky Avatar asked Oct 21 '14 12:10

ehmicky


People also ask

How do I return a JSON object in PostgreSQL?

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.

What is Json_build_object in PostgreSQL?

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.

Can Jsonb be an array?

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.

What is Json_build_object?

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.


1 Answers

json_build_object() in Postgres 9.4 or newer

Or 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.

For any version (incl. Postgres 9.3)

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:

  • PostgreSQL 9.2 row_to_json() with nested joins

db<>fiddle here
Old sqlfiddle

like image 55
Erwin Brandstetter Avatar answered Sep 26 '22 23:09

Erwin Brandstetter