How to select all records from many different tables in Postgres, and return a nested tree of data?

I have 9 tables which are for the most part similar (same 4 columns), but have the remaining columns differ between tables:

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

What is the standard approach to select all records matching specific constraints, from all 9 tables, and include the "extra" properties for each table? Second part of the question is how to return it in a nested fashion, if possible (described below).

I basically would like the query to work like this:

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

I would like to use these in JavaScript as JSON records. I am using knex.js for making the queries from Node.js, if that matters.

Ideally, I would get the records back from SQL grouped in a nested fashion like this:

{
  [organization_id]: {
    [object_type_id]: {
      [object_id]: {
        [object_property_id]: {
          ...remainingProperties
        }
      }
    }
  }
}

So for the object_binding table, it would look like:

{
  [organization_id]: {
    [object_type_id]: {
      [object_id]: {
        [object_property_id]: {
          value_organization_id: something,
          value_type_id: something2,
          value_object_id: something3,
        }
      }
    }
  }
}

Or with specific data to demonstrate:

{
  17: {
    20: {
      102: {
        193: {
          value_organization_id: 14,
          value_type_id: 12,
          value_object_id: 100,
        }
      }
    }
  }
}

Is anything like this possible? Ideally I would have be able to get the last nested structure directly from SQL (if it is efficient). If it is not efficient or not possible in SQL, I can just get back the arrays of records and reduce it to this structure at the application layer in JavaScript. But then the main question still is, how can I query all 9 tables and return all their each unique columns of data? Right now I am just making the 9 queries and reducing in JavaScript. Is that the best approach? Even if it is, I would still like to see how it is done querying all 9 at once.

Here's an attempt at creating something similar.

No view.
Just a lot of left joins on a sub-query with the criteria.

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" : {"3" : {"props" : [{"property_id" : 4, "value_organization_id" : null, "value_type_id" : null, "value_object_id" : null, "value_property_id" : null, "attachment_bucket_id" : null, "attachment_hash" : null, "boolean" : null, "decimal" : null, "integer" : null, "string" : null, "text" : null, "timestamp" : null}]}}}} |

db<>fiddle here

First question : how to query 9 tables ?

You can create a view, see the manual :

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

Then you can query the view directly :

SELECT *
  FROM my_view
 WHERE object_organization_id = 1
   AND object_type_id = 2
   AND object_id = 3
   AND object_property_id = 4 ;

Second question : how to aggreate columns into a unique json data ?

In your example, double quotes are missing so that it can't be a json data generated from postgres. An output from postgres could be :

{
  "17": {
    "20": {
      "102": {
        "193": {
          "value_organization_id": 14,
          "value_type_id": 12,
          "value_object_id": 100,
        }
      }
    }
  }
}

The query to get this kind of result may be like this :

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

How to select all records from many different tables in Postgres, and return a nested tree of data?

Related

Recent Posts