我有9个表，它们在很大程度上相似(相同的4列(，但表之间的其余列不同：
CREATE TABLE my_namespace_attachment (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
bucket_id integer,
value_hash character varying(255)
);
CREATE TABLE my_namespace_boolean (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value boolean
);
CREATE TABLE my_namespace_decimal (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value numeric
);
CREATE TABLE my_namespace_integer (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value integer
);
CREATE TABLE my_namespace_object_binding (
object_organization_id bigint NOT NULL,
object_type_id integer NOT NULL,
object_id bigint NOT NULL,
object_property_id integer NOT NULL,
value_organization_id bigint NOT NULL,
value_type_id integer NOT NULL,
value_object_id bigint NOT NULL
);
CREATE TABLE my_namespace_property_binding (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value_organization_id bigint,
value_type_id integer,
value_object_id integer,
value_property_id integer
);
CREATE TABLE my_namespace_string (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value character varying(255)
);
CREATE TABLE my_namespace_text (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value text
);
CREATE TABLE my_namespace_timestamp (
object_organization_id bigint,
object_type_id integer,
object_id bigint,
object_property_id integer,
value timestamp without time zone
);
从所有9个表中选择匹配特定约束的所有记录并包括"；额外的"；每个表的属性？问题的第二部分是，如果可能的话，如何以嵌套的方式返回它(如下所述(。
我基本上希望查询像这样工作：
SELECT * FROM
my_namespace_attachment,
my_namespace_boolean,
my_namespace_decimal,
my_namespace_integer,
my_namespace_object_binding,
my_namespace_property_binding,
my_namespace_string,
my_namespace_text,
my_namespace_timestamp
WHERE object_organization_id = 1
AND object_type_id = 2
AND object_id = 3
AND object_property_id = 4
我想在JavaScript中使用这些作为JSON记录。我使用knex.js从Node.js进行查询，如果这很重要的话。
理想情况下，我会以嵌套的方式从SQL中取回分组的记录，如下所示：
{
[organization_id]: {
[object_type_id]: {
[object_id]: {
[object_property_id]: {
...remainingProperties
}
}
}
}
}
因此，对于object_binding表，它看起来像：
{
[organization_id]: {
[object_type_id]: {
[object_id]: {
[object_property_id]: {
value_organization_id: something,
value_type_id: something2,
value_object_id: something3,
}
}
}
}
}
或者用具体数据来证明：
{
17: {
20: {
102: {
193: {
value_organization_id: 14,
value_type_id: 12,
value_object_id: 100,
}
}
}
}
}
这样的事情有可能吗？理想情况下，我应该能够直接从SQL中获得最后一个嵌套结构(如果它有效的话(。如果它在SQL中效率不高或不可能，我可以在JavaScript的应用程序层取回记录数组并将其简化为这种结构。但主要的问题仍然是，如何查询所有9个表并返回它们各自唯一的数据列？现在我只是在JavaScript中进行9个查询和精简。这是最好的方法吗？即使是这样，我仍然想看看它是如何一次查询所有9的。
下面是创建类似内容的尝试。
没有视图
在具有条件的子查询上只有很多左联接。
SELECT json_build_object ( obj.object_organization_id, json_build_object ( obj.object_type_id, json_build_object ( obj.object_id, json_build_object ( 'props', json_agg(json_build_object ( 'property_id', obj.object_property_id , 'value_organization_id', obj.value_organization_id , 'value_type_id', obj.value_type_id , 'value_object_id', obj.value_object_id , 'value_property_id', obj.value_property_id , 'attachment_bucket_id', attach.bucket_id , 'attachment_hash', attach.value_hash , 'boolean', bool.value , 'decimal', dec.value , 'integer', inte.value , 'string', string.value , 'text', txt.value , 'timestamp', ts.value ) -- property ) -- agg1 ) -- object ) -- type ) -- org ) as json FROM ( SELECT DISTINCT obj.object_organization_id , obj.object_type_id , obj.object_id , prop.object_property_id , prop.value_organization_id , prop.value_type_id , prop.value_object_id , prop.value_property_id FROM my_namespace_object_binding obj INNER JOIN my_namespace_property_binding prop USING (object_organization_id, object_type_id, object_id, object_property_id) WHERE obj.object_organization_id = 1 AND obj.object_type_id = 2 AND obj.object_id = 3 AND prop.object_property_id = 4 ) AS obj LEFT JOIN my_namespace_attachment AS attach USING (object_organization_id, object_type_id, object_id, object_property_id) LEFT JOIN my_namespace_boolean AS bool USING (object_organization_id, object_type_id, object_id, object_property_id) LEFT JOIN my_namespace_decimal AS dec USING (object_organization_id, object_type_id, object_id, object_property_id) LEFT JOIN my_namespace_integer AS inte USING (object_organization_id, object_type_id, object_id, object_property_id) LEFT JOIN my_namespace_string AS string USING (object_organization_id, object_type_id, object_id, object_property_id) LEFT JOIN my_namespace_text AS txt USING (object_organization_id, object_type_id, object_id, object_property_id) LEFT JOIN my_namespace_timestamp AS ts USING (object_organization_id, object_type_id, object_id, object_property_id) GROUP BY obj.object_organization_id , obj.object_type_id , obj.object_id;
|json||：-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------||{"1"：{"2"：[{"属性id"：4，"值组织id"：null，"值类型id"：null，"值对象id"：null，"值属性id"：null，"附件_容器id"：null，"附件_hash"：null、"布尔"：null、"小数"：null"、"整数"：null字符串"：null，"文本"：null，"；时间戳"：null｝]｝}｝|
db<gt；小提琴这里
第一个问题：如何查询9个表
您可以创建一个视图，请参阅手册：
CREATE OR REPLACE VIEW my_view
( object_organization_id
, object_type_id
, object_id
, object_property_id
, bucket_id
, value_hash
, boolean_value
, decimal_value
, integer_value
, string_value
, text_value
, timestamp_value
, value_organization_id
, value_type_id
, value_object_id
, value_property_id
)
AS
SELECT a.object_organization_id
, a.object_type_id
, a.object_id
, a.object_property_id
, a.bucket_id
, a.value_hash
, b.boolean_value
, d.decimal_value
, i.integer_value
, s.string_value
, t.text_value
, ts.timestamp_value
, o.value_organization_id
, o.value_type_id
, o.value_object_id
, p.value_property_id
FROM my_namespace_attachment AS a
INNER JOIN my_namespace_boolean AS b USING (object_organization_id, object_type_id, object_id, object_property_id)
INNER JOIN my_namespace_decimal AS d USING (object_organization_id, object_type_id, object_id, object_property_id)
INNER JOIN my_namespace_integer AS i USING (object_organization_id, object_type_id, object_id, object_property_id)
INNER JOIN my_namespace_object_binding AS o USING (object_organization_id, object_type_id, object_id, object_property_id)
INNER JOIN my_namespace_property_binding AS p USING (object_organization_id, object_type_id, object_id, object_property_id, value_organization_id, value_type_id, value_object_id)
INNER JOIN my_namespace_string AS s USING (object_organization_id, object_type_id, object_id, object_property_id)
INNER JOIN my_namespace_text AS t USING (object_organization_id, object_type_id, object_id, object_property_id)
INNER JOIN my_namespace_timestamp AS ts USING (object_organization_id, object_type_id, object_id, object_property_id) ;
然后您可以直接查询视图：
SELECT *
FROM my_view
WHERE object_organization_id = 1
AND object_type_id = 2
AND object_id = 3
AND object_property_id = 4 ;
第二个问题：如何将列聚合为唯一的json数据
在您的示例中，缺少双引号，因此它不能是从postgres生成的json数据。postgres的输出可能是：
{
"17": {
"20": {
"102": {
"193": {
"value_organization_id": 14,
"value_type_id": 12,
"value_object_id": 100,
}
}
}
}
}
得到这种结果的查询可能是这样的：
SELECT jsonb_build_object( object_organization_id :: text ,
jsonb_build_object( object_type_id :: text ,
jsonb_build_object( object_id :: text ,
jsonb_build_object( object_property_id :: text ,
to_jsonb(row(value_organization_id, value_type_id, value_object_id))))))
FROM my_namespace_property_binding