PostgreSQL 9.2 row_to_json() с вложенными объединениями

Я пытаюсь сопоставить результаты запроса с JSON с помощью функции row_to_json(), которая была добавлена в PostgreSQL 9.2.

У меня возникли проблемы с поиском наилучшего способа представления объединенных строк как вложенных объектов (отношения 1:1)

Вот что я пробовал (установочный код: таблицы, примеры данных, а затем запрос):

-- some test tables to start out with:
create table role_duties (
    id serial primary key,
    name varchar
);

create table user_roles (
    id serial primary key,
    name varchar,
    description varchar,
    duty_id int, foreign key (duty_id) references role_duties(id)
);

create table users (
    id serial primary key,
    name varchar,
    email varchar,
    user_role_id int, foreign key (user_role_id) references user_roles(id)
);

DO $$
DECLARE duty_id int;
DECLARE role_id int;
begin
insert into role_duties (name) values ('Script Execution') returning id into duty_id;
insert into user_roles (name, description, duty_id) values ('admin', 'Administrative duties in the system', duty_id) returning id into role_id;
insert into users (name, email, user_role_id) values ('Dan', '[email protected]', role_id);
END$$;

Сам запрос:

select row_to_json(row)
from (
    select u.*, ROW(ur.*::user_roles, ROW(d.*::role_duties)) as user_role 
    from users u
    inner join user_roles ur on ur.id = u.user_role_id
    inner join role_duties d on d.id = ur.duty_id
) row;

Я обнаружил, что если я использовал ROW(), я мог бы разделить полученные поля на дочерний объект, но он, по-видимому, ограничен одним уровнем. Я не могу вставлять больше инструкций AS XXX, так как я думаю, что мне нужно в этом случае.

Мне предоставлены имена столбцов, потому что я применил соответствующий тип записи, например, с ::user_roles, в случае этой таблицы.

Вот что возвращает этот запрос:

{
   "id":1,
   "name":"Dan",
   "email":"[email protected]",
   "user_role_id":1,
   "user_role":{
      "f1":{
         "id":1,
         "name":"admin",
         "description":"Administrative duties in the system",
         "duty_id":1
      },
      "f2":{
         "f1":{
            "id":1,
            "name":"Script Execution"
         }
      }
   }
}

То, что я хочу сделать, это генерировать JSON для объединений (опять 1:1 в порядке) таким образом, чтобы я мог добавлять объединения и представлять их как дочерние объекты родителей, к которым они присоединяются, т.е. следующим образом:

{
   "id":1,
   "name":"Dan",
   "email":"[email protected]",
   "user_role_id":1,
   "user_role":{
         "id":1,
         "name":"admin",
         "description":"Administrative duties in the system",
         "duty_id":1
         "duty":{
            "id":1,
            "name":"Script Execution"
         }
      }
   }
}

Любая помощь приветствуется. Спасибо за чтение.

Ответ 1

Обновление: в PostgreSQL 9.4 это улучшает много с введением to_json, json_build_object, json_object и json_build_array, хотя это подробное изложение для необходимости явно указывать все поля:

select
        json_build_object(
                'id', u.id,
                'name', u.name,
                'email', u.email,
                'user_role_id', u.user_role_id,
                'user_role', json_build_object(
                        'id', ur.id,
                        'name', ur.name,
                        'description', ur.description,
                        'duty_id', ur.duty_id,
                        'duty', json_build_object(
                                'id', d.id,
                                'name', d.name
                        )
                )
    )
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;

Для более старых версий, читайте дальше.

Это не ограничивается одной строкой, это немного больно. Вы не можете создавать сложные типы строк с помощью AS, поэтому для достижения эффекта вам нужно использовать выражение подзапроса с псевдонимом или CTE:

select row_to_json(row)
from (
    select u.*, urd AS user_role
    from users u
    inner join (
        select ur.*, d
        from user_roles ur
        inner join role_duties d on d.id = ur.duty_id
    ) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id
) row;

создает через http://jsonprettyprint.com/:

{
  "id": 1,
  "name": "Dan",
  "email": "[email protected]",
  "user_role_id": 1,
  "user_role": {
    "id": 1,
    "name": "admin",
    "description": "Administrative duties in the system",
    "duty_id": 1,
    "duty": {
      "id": 1,
      "name": "Script Execution"
    }
  }
}

Вы хотите использовать array_to_json(array_agg(...)), если у вас есть соотношение 1: много, btw.

Вышеприведенный запрос в идеале должен быть написан как:

select row_to_json(
    ROW(u.*, ROW(ur.*, d AS duty) AS user_role)
)
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;

... но конструктор PostgreSQL ROW не принимает псевдонимы столбцов AS. К сожалению.

К счастью, они оптимизируют то же самое. Сравните планы:

вложенная версия подзапроса; против
Последняя вложенная версия ROW конструктор с удаленными псевдонимами, поэтому она выполняет

Поскольку CTE являются оптимизационными заборами, перефразировать вложенную версию подзапроса для использования целых CTE (выражений WITH) может также не работать, и это не приведет к такому же плану. В этом случае вы как бы застряли в уродливых вложенных подзапросах, пока не получите некоторые улучшения в row_to_json, а также более просто переопределить имена столбцов в конструкторе ROW.

Во всяком случае, в принципе, принцип заключается в том, что если вы хотите создать json-объект со столбцами a, b, c, и вы хотите просто написать нелегальный синтаксис:

ROW(a, b, c) AS outername(name1, name2, name3)

вместо этого вы можете использовать скалярные подзапросы, возвращающие значения, типизированные для строки:

(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername

Или:

(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername

Кроме того, имейте в виду, что вы можете составить json значения без дополнительного цитирования, например. если вы поместите вывод json_agg внутри row_to_json, внутренний результат json_agg не будет указан как строка, он будет включен непосредственно как json.

например. в произвольном примере:

SELECT row_to_json(
        (SELECT x FROM (SELECT
                1 AS k1,
                2 AS k2,
                (SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) )
                 FROM generate_series(1,2) ) AS k3
        ) x),
        true
);

вывод:

{"k1":1,
 "k2":2,
 "k3":[{"a":1,"b":2}, 
 {"a":1,"b":2}]}

Обратите внимание, что продукт json_agg [{"a":1,"b":2}, {"a":1,"b":2}] не был снова экранирован, так как text был бы.

Это означает, что вы можете создавать операции json для построения строк, вам не всегда нужно создавать сложные сложные составные типы PostgreSQL, а затем вызывать row_to_json на выходе.

Ответ 2

Мое предложение для долговременной поддержки - использовать VIEW для построения грубой версии вашего запроса, а затем использовать функцию, как показано ниже:

CREATE OR REPLACE FUNCTION fnc_query_prominence_users( )
RETURNS json AS $$
DECLARE
    d_result            json;
BEGIN
    SELECT      ARRAY_TO_JSON(
                    ARRAY_AGG(
                        ROW_TO_JSON(
                            CAST(ROW(users.*) AS prominence.users)
                        )
                    )
                )
        INTO    d_result
        FROM    prominence.users;
    RETURN d_result;
END; $$
LANGUAGE plpgsql
SECURITY INVOKER;

В этом случае объект highlightance.users - это представление. Поскольку я выбрал пользователей. *, Мне не нужно будет обновлять эту функцию, если мне нужно обновить представление, чтобы добавить больше полей в пользовательскую запись.