1

I have a self referencing table named scope (in my user_account schema) that is hierarchal data - some example data (Postgres 9.4):

*****Edit:******

Made an SQL fiddle: http://sqlfiddle.com/#!15/43ff9/2/0 with a much more detailed example. Note the repeating nature of the bottom tier of the JSON output. I'll leave this information here for reference.

*****End Edit******

user_account.scope

id  name             parent_id
1   user             NULL
2   user:create      1
3   user:delete      1
4   user:modify      1
5   user:modify:all  4
6   user:modify:some 4
7   user:modify:ex   4

Obviously, top level scopes have no parent_id, and it can go multiple levels (arbitrarily) deep.

I am trying to write a SQL query that recursively returns the entire table as a nested JSON object. I was mostly successful with this:

WITH json_agg_scope_cte AS (
  WITH scope_cte AS (
    WITH RECURSIVE sub_scopes_cte AS (
      SELECT
        s.*,
        NULL :: JSON AS sub_scopes
      FROM
        user_account.scope s
      WHERE NOT EXISTS(
          SELECT 1
          FROM user_account.scope
          WHERE parent_id = s.id
      )
            AND s.deleted = FALSE

      UNION ALL

      SELECT
        s.*,
        row_to_json(ssc) AS sub_scopes
      FROM
        sub_scopes_cte ssc
        JOIN user_account.scope s
          ON s.id = ssc.parent_id
             AND s.deleted = FALSE
    )

    SELECT
      id,
      scope,
      json_agg(sub_scopes) AS sub_scopes
    FROM sub_scopes_cte ssc
    WHERE parent_id IS NULL
    GROUP BY 1, 2
  )

  SELECT
    s.*,
    sc.sub_scopes
  FROM user_account.scope s
    INNER JOIN scope_cte sc
      ON s.id = sc.id
)

SELECT json_agg(json_agg_scope_cte.*) AS scopes
FROM json_agg_scope_cte

The problem though, is that sub-scopes are listed as an array on the top level items (because of the json_agg(sub_scopes) part), but only listed as a simple NULL or a single object for anything nested (because of the row_to_json(ssc) part). It also repeats the parent inside the JSON, once for each of its sub_scopes.

What I'm getting:

"scopes":
[{
  "scope": "top_scope"
  "sub_scopes":
  [{
     "scope": "mid_scope"
     "sub_scopes": // NOT an array, needs to be
     {
        "scope": "bottom_scope_1"
        "sub_scopes": NULL // Should be an array with NULL in it, consistent with format of others
     }
  },
  {
    "scope": "mid_scope" // repeated, bad
    "sub_scopes":
    {
      "scope": "bottom_scope_2"
      "sub_scopes": NULL
    }
  }]
}]

What I want:

"scopes": 
[{
  "scope": "top_scope"
  "sub_scopes":
  [{
     "scope": "mid_scope"
     "sub_scopes": // array
     [{
        "scope": "bottom_scope_1"
        "sub_scopes": [NULL] // array as well!
     },
     {
        "scope": "bottom_scope_2"
        "sub_scopes": [NULL]
     }]
  }]
}]

I tried changing row_to_json to an json_agg, but you can't use an aggregate function inside a recursive call. Any suggestions on how to either A) fix this query, or B) a postgres way to "fix" the json to merge the values as I desire? Been playing with this forever, but not having any luck.

Thanks!

Chris Burrus
  • 347
  • 3
  • 11
  • create table with sample data on sqlfiddle please. then people will try to help – Vao Tsun Jun 03 '16 at 05:32
  • Good suggestion, thanks - I edited post to add one, but for reference, it is http://sqlfiddle.com/#!15/43ff9/2/0 – Chris Burrus Jun 03 '16 at 15:48
  • Why do you want to do this in your database? If this is the sort of structure you want to manage, why use a relational database as all (which are notoriously painful for working with hierarchical data). – Mike Brant Jun 03 '16 at 15:53
  • 98% of the time our interaction with our data doesn't work in this hierarchical state. This is just a rare exception. Specifically, we want JSON formatted data for interacting with an API for this case - and the whole tree (normally we just work with individual parts, so no need for the full hierarchy.) – Chris Burrus Jun 03 '16 at 15:58

0 Answers0