PostgreSQL 9.3 -> JSON from nested set -> jQuery listview

Question

I have this nested set:

id;parent;name;lft;rgt
----------------------
1;0;"Food";2;21
3;1;"Appetizer";3;8
8;3;"Nachos & salsa";4;5
9;3;"Kentucky chicken wings";6;7
4;1;"Soup";9;14
10;4;"Broth";10;11
11;4;"Tomato soup";12;13
5;1;"Pizza";15;20
12;5;"Americana";16;17
13;5;"Margherita";18;19
2;0;"Beverages";22;27
6;2;"Wines";23;24
7;2;"Soft drinks";25;26

I would like a JSON output that represents the full tree.
I want to build a Nested list like this from JSON:
http://demos.jquerymobile.com/1.2.1/docs/lists/lists-nested.html#&ui-page=2-4

Thanks for any help!

Answer 1

The first thing to do, is to query for the rows in such way that the tree may be retrieved. To do that, we can simple use a recursive query. Assuming your table is named food, the following query is a good example of a recursive query for it:

WITH RECURSIVE t AS (
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
    FROM food f
    WHERE f.parent = 0
    UNION ALL
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
    FROM food f JOIN t ON f.parent = t.id
)
SELECT repeat('|__', level)||t.name AS tree, level, path
FROM t
ORDER BY path;

Which will return in the following form:

             tree             | level |                   path                    
------------------------------+-------+-------------------------------------------
 Beverages                    |     0 | {Beverages}
 |__Soft drinks               |     1 | {Beverages,"Soft drinks"}
 |__Wines                     |     1 | {Beverages,Wines}
 Food                         |     0 | {Food}
 |__Appetizer                 |     1 | {Food,Appetizer}
 |__|__Kentucky chicken wings |     2 | {Food,Appetizer,"Kentucky chicken wings"}
 |__|__Nachos & salsa         |     2 | {Food,Appetizer,"Nachos & salsa"}
 |__Pizza                     |     1 | {Food,Pizza}
 |__|__Americana              |     2 | {Food,Pizza,Americana}
 |__|__Margherita             |     2 | {Food,Pizza,Margherita}
 |__Soup                      |     1 | {Food,Soup}
 |__|__Broth                  |     2 | {Food,Soup,Broth}
 |__|__Tomato soup            |     2 | {Food,Soup,"Tomato soup"}
(13 rows)

Basically, the path array will give us an element to sort the rows in a way that give us the tree (you can use other column than name if you want), and level (basically path length - 1) give us the level that the element is in. Having both information, we can use some tricks with the window function lead (with window ORDER BY path) to, at each row, take a look at the level of the next row and so create our json (check the comments in the query):

WITH RECURSIVE t AS (
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
    FROM food f
    WHERE f.parent = 0
    UNION ALL
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
    FROM food f JOIN t ON f.parent = t.id
)
SELECT (E'[\n'||string_agg(json, E'\n')||E'\n]')::json FROM (
SELECT
    /* Add some simple indentation (why not?) */
    repeat('    ', level)
    || '{"name":'||to_json(name)|| ', "items":['
    ||
    /* The expr bellow will return the level of next row, or -1 if it is last */
    CASE coalesce(lead(level) OVER(ORDER BY path), -1)
        /* Next row opens a new level, so let's add the items array */
        WHEN level+1 THEN ''
        /* WHEN level+1 THEN ', "items":[' */
        /* We are on the same level, so just close the current element */
        WHEN level THEN ']},'
        /* Last row, close the current element and all other levels still opened (no indentation here, sorry) */
        WHEN -1 THEN ']}' || repeat(']}', level)
        /* ELSE, the next one belongs to another parent, just close me and my parent */
        ELSE /* closes me: */ ']}' /* closes my parent: */ || E'\n'||repeat('    ', level-1)||']},'
    END AS json
FROM t
) s1;

Which will give us the following json:

[
{"name":"Beverages", "items":[
    {"name":"Soft drinks", "items":[]},
    {"name":"Wines", "items":[]}
]},
{"name":"Food", "items":[
    {"name":"Appetizer", "items":[
        {"name":"Kentucky chicken wings", "items":[]},
        {"name":"Nachos & salsa", "items":[]}
    ]},
    {"name":"Pizza", "items":[
        {"name":"Americana", "items":[]},
        {"name":"Margherita", "items":[]}
    ]},
    {"name":"Soup", "items":[
        {"name":"Broth", "items":[]},
        {"name":"Tomato soup", "items":[]}]}]}
]

It is somewhat a trick query, I hope the comments will help (and also hope it is correct for any test case).

Answer 2

Sorry, but I had some issues with your query, using the next structure

1,0,'Food',2,21
2,0,'Beverages',22,27
3,1,'Appetizer',3,8 
4,3,'Soup',9,14                   -- Parent soup is 3 instead 1
5,1,'Pizza',15,20
6,2,'Wines',23,24
7,2,'Soft drinks',25,26
8,3,'Nachos & salsa',4,5
9,3,'Kentucky chicken wings',6,7
10,4,'Broth',10,11
11,4,'Tomato soup',12,13
12,5,'Americana',16,17
13,5,'Margherita',18,19

resulting :

             tree             | level |                   path                    
------------------------------+-------+-------------------------------------------
 Beverages                    |     0 | {Beverages}
 |__Soft drinks               |     1 | {Beverages,"Soft drinks"}
 |__Wines                     |     1 | {Beverages,Wines}
 Food                         |     0 | {Food}
 |__Appetizer                 |     1 | {Food,Appetizer}
 |__|__Kentucky chicken wings |     2 | {Food,Appetizer,"Kentucky chicken wings"}
 |__|__Nachos & salsa         |     2 | {Food,Appetizer,"Nachos & salsa"}
 |__|__Soup                   |     2 | {Food,Appetizer,Soup}
 |__|__|__Broth               |     2 | {Food,Appetizer,Soup,Broth}
 |__|__|__Tomato soup         |     3 | {Food,Appetizer,Soup,"Tomato soup"}
 |__Pizza                     |     1 | {Food,Pizza}
 |__|__Americana              |     2 | {Food,Pizza,Americana}
 |__|__Margherita             |     2 | {Food,Pizza,Margherita}
(13 rows)

using your same logic, this could be better:

WITH RECURSIVE t AS (
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
    FROM food f
    WHERE f.parent = 0
    UNION ALL
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
    FROM food f JOIN t ON f.parent = t.id
) 
SELECT ( '[' || string_agg( json, '' ) || ']' ) :: json FROM (
select
    '{"name":'||to_json( name ) || 
    case lead( level, 1 ) OVER( ORDER BY path )
        when level then '},' --same lavel, no children, only close
        when level + 1 THEN ', "items":[' -- There's children, add item array
        else -- last child in group start to close
            '}' || --close actual element
            case
                when lead( level ) OVER( ORDER BY path ) < level THEN -- last children in group, close parents, until next level
                    repeat( ']}', level - lead( level ) OVER( ORDER BY path ) ) || ',' 
                else repeat( ']}', level ) -- last element in list, close parents all levels
            end
    end as json
from t
) s1;

indentation??? maybe later...

EDIT Adding fiddle http://sqlfiddle.com/#!15/187e5/1