Göster bir sorgunun sonuçlarını PostgreSQL 9.2 eklendi row_to_json() işlevini kullanarak JSON için çalışıyorum.

Sorun iç içe geçmiş nesneleri (1:1 ilişkiler) olarak katıldı satır temsil etmek için en iyi yolu bulmaktan yaşıyorum

(Kur kodu: tablolar, örnek veri, sorgu izledi) denedim:

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

Kendini sorgu:

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() sonuç alanları çocuk bir nesne içine ayrı olabilir, ama tek bir seviye için sınırlı görünüyor eğer ben buldum. Bu durumda gerek bence daha fazla AS XXX ınsert deyimleri, yapamam.

Bu tablonun sonuçlarının durumda ::user_roles ile örneğin uygun kayıt türüne atıyorum, çünkü sütun adları tanınan, duyuyorum.

Döndüren sorgu:

{
   "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"
         }
      }
   }
}

Ne yapmak istediğim JSON oluşturmak için katılır (tekrar 1:1 gayet iyi bir şekilde yapabiliyorum katılıyor ve onları temsil gibi alt nesneler, ebeveynler katılmak için, yani aşağıdaki gibi:

{
   "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"
         }
      }
   }
}

Herhangi bir yardım için teşekkür ederiz. Okuduğunuz için teşekkür ederim.

Güncelleme: PostgreSQL 9.4 bu çok ayrıntılı tüm alanları açıkça isme ihtiyacı nedeniyle olsa with the introduction of to_json, json_build_object, json_object and json_build_array,: iyileştirir

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;

Eski sürümleri için, okumaya devam edin.

Tek bir satır ile sınırlı değil, sadece biraz acı verici. Yumuşatılmış alt sorgu bir ifade kullanmak gerekir, böylece kompozit rowtypes AS, takma ad kullanarak ya da etkiyi elde etmek için 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/ üzerinden üretir:

{
  "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"
    }
  }
}

1:birçok ilişki, bir arada olduğunda array_to_json(array_agg(...)) kullanmak isteyeceksiniz.

Yukarıdaki sorgu ideal olarak yazılmış olması gerekir:

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;

... ama PostgreSQL ROW yapıcı AS sütun diğer adları kabul etmez. Ne yazık ki.

Neyse ki, aynı optimize. Planları karşılaştırın:

• nested subquery version; vs
• Diğer adları ile nested ROW constructor version ikincisi, çalıştırır kaldırıldı

CTEs optimizasyonu çitler çünkü, zincirleme CTEs (WITH ifadeler) kullanmak için iç içe sorgu sürümü betimleme olarak iyi performans olmayabilir, ve aynı planı neden olmaz. Bu durumda biraz row_to_json bazı iyileştirmeler veya ROW bir kurucu daha fazla sütun adları doğrudan geçersiz kılmak için bir yol bulana kadar çirkin iç içe geçmiş alt sorgular kaldın.

Her neyse, genel olarak, prensip sütun ile bir json nesnesi oluşturmak istediğiniz yere ** 25, ve sadece yasadışı sözdizimi yazmak isterdim

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

yerine skaler alt satır değerleri: dönen kullanabilirsiniz

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

Ya da:

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

Ayrıca, unutmayın, sen-ebilmek oluşturmak json değerler olmadan ek alıntı, örneğin koyarsan çıkışı json_agg row_to_json iç json_agg sonuç alamayacağım gibi alıntı bir dize, daha doğrudan dahil olarak json.

keyfi örnekte örneğin:

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

çıktı

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

json_agg ürün, [{"a":1,"b":2}, {"a":1,"b":2}], text gibi yine kaçtı, olmadı unutmayın.

Bu yapabilirsiniz anlamına geliroluştursatırları, her zaman son derece karmaşık PostgreSQL kompozit türleri oluşturmak zorunda değilsin oluşturmak için json işlemleri çıktı row_to_json Ara.