How can I get a hierarchial json from the same table in postgres

Question

{
      object_id: 1,
      text: [
         en: { name: xxx, desc: xxx },
         pl: { name: xxx, desc: xxx }
      ]
   }

my table looks like this

id, name, desc, object_id, language

and some of the data looks like this:

1, xxx, xxx, 1, en
2, xxx, xxx, 2, pl
3, xxx, xxx, 1, pl 
4, xxx, xxx, 2, de

I want to aggregate the name and desc of the those who have the same object_id in the text field in the response

I am working on postgres 9.3

klin · Answer 1 · 2015-07-07T20:09:14.827

The question contains inaccuracies: the expected result is not a valid json and the table is not properly defined.

Assume that the table definition is:

create table the_table
(
  id integer,
  some_name text,   -- do not use 'name' for column name
  descr text,       -- 'desc' is invalid column name
  object_id integer,
  lang text         -- do not use 'language' for column name
)

The query:

select json_agg(json_build_object('object_id', object_id, 'text', js))
from (
    select object_id, json_agg(js) js
    from (
        select 
            object_id, 
            lang, 
            json_build_object(
                lang, 
                json_object(array['name', some_name, 'desc', descr])
            ) js
        from the_table
        order by lang
        ) alias
    group by 1
    ) alias

produces this valid json.

Postgres 9.3 solution

Postgres 9.4 came with a powerful function json_build_object(variadic "any"). It is impossible to substitute it with your own function because plpgsql does not allow variadic "any" as an argument. For the purposes of Postgres 9.3 we will use three functions that operate on text type (rather than on json), and the simplest aggregate function string_agg (this solution is sufficient, although more elegant would be to define our own aggregate function).

create function js_clear(t text)
returns text language sql as $$
    select replace(replace(replace(replace(t,'"[','['), '"{','{'), '}"','}'), ']"',']');
$$;

create function js_array(t text)
returns text language sql as $$
    select format('[%s]', js_clear(t));
$$;

create function js_object(variadic args text[])
returns text language plpgsql as $$
declare
    res text = '';
begin
    for i in 1 .. array_length(args, 1) by 2 loop
        res:= format('%s"%s" : "%s", ', res, args[i], args[i+1]);
    end loop;
    return format('{%s}', rtrim(js_clear(res), ', '));
end $$;

The query in 9.3 version:

select js_array(string_agg(js_object('object_id', object_id::text, 'text', js), ', '))
from (
    select object_id, js_array(string_agg(js, ', ')) js
    from (
        select 
            object_id, 
            lang, 
            js_object(
                lang, 
                js_object('name', some_name, 'desc', descr)
            ) js
        from the_table
        order by lang
        ) alias
    group by 1
    ) alias

is json_object is from postgres 9.4? How can I do this with postgres 9.3? — , Jul 07 '15 at 11:12
`json_build_object()` and `json_object()` were introduced in 9.4. It is possible to substitute them in 9.3 but that is a bit complicated. — klin, Jul 07 '15 at 13:15
ohhh...could you please point me to some reference on that? that would be very helpful — , Jul 07 '15 at 13:16
@kiln the array of objects in the `"text"` fields seems a little over-structured; with `json_object_agg()` that could be `"text":{"de":{...},"pl":{...}}` (but that's also 9.4+ feature). — pozs, Jul 07 '15 at 15:25
@user494461 I'm afraid in 9.3, you have to build your `json` objects manually, like `concat('{"name":', to_json(some_name), ',"desc":', to_json(descr), '}')::json` (or use `row_to_json()` for that, but you'll need [aliasing](http://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins)) — pozs, Jul 07 '15 at 15:29
@pozs - Right, json might be a bit simpler, but luckily this structure can be easily implemented in 9.3. — klin, Jul 07 '15 at 20:10

How can I get a hierarchial json from the same table in postgres

1 Answers1