중첩 조인이있는 PostgreSQL 9.2 row_to_json ()

중첩 조인이있는 PostgreSQL 9.2 row_to_json ()

---

row_to_json()PostgreSQL 9.2에 추가 된 함수를 사용하여 쿼리 결과를 JSON에 매핑하려고합니다 .

결합 된 행을 중첩 된 개체로 표현하는 가장 좋은 방법을 찾는 데 문제가 있습니다 (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', 'someemail@gmail.com', 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":"someemail@gmail.com",
   "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":"someemail@gmail.com",
   "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"
         }
      }
   }
}

도움을 주시면 감사하겠습니다. 읽어 주셔서 감사합니다.

---

업데이트 :에서 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": "someemail@gmail.com",
  "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"
    }
  }
}

You will want to use array_to_json(array_agg(...)) when you have a 1:many relationship, btw.

The above query should ideally be able to be written as:

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;

... but PostgreSQL's ROW constructor doesn't accept AS column aliases. Sadly.

Thankfully, they optimize out the same. Compare the plans:

• The nested subquery version; vs
• The latter nested ROW constructor version with the aliases removed so it executes

Because CTEs are optimisation fences, rephrasing the nested subquery version to use chained CTEs (WITH expressions) may not perform as well, and won't result in the same plan. In this case you're kind of stuck with ugly nested subqueries until we get some improvements to row_to_json or a way to override the column names in a ROW constructor more directly.

---

Anyway, in general, the principle is that where you want to create a json object with columns a, b, c, and you wish you could just write the illegal syntax:

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

you can instead use scalar subqueries returning row-typed values:

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

Or:

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

Additionally, keep in mind that you can compose json values without additional quoting, e.g. if you put the output of a json_agg within a row_to_json, the inner json_agg result won't get quoted as a string, it'll be incorporated directly as json.

e.g. in the arbitrary example:

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
);

the output is:

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

Note that the json_agg product, [{"a":1,"b":2}, {"a":1,"b":2}], hasn't been escaped again, as text would be.

This means you can compose json operations to construct rows, you don't always have to create hugely complex PostgreSQL composite types then call row_to_json on the output.

---

My suggestion for maintainability over the long term is to use a VIEW to build the coarse version of your query, and then use a function as below:

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;

In this case, the object prominence.users is a view. Since I selected users.*, I will not have to update this function if I need to update the view to include more fields in a user record.

참고URL : https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins