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!