PostgreSQL Nesting a Select and returning the nested data

Question

I have a database with 3 tables looking like this:

product_types
id         label        assignments
1          Shirt        [{"default":true, "assignment": 1}]

assignments
id         label              assignments
1          Shirt Options      [{"label":"Size", "option":8}]

options
id         label              choices
8          Sizing             [{"label":"Small"}, {"label":"Medium"}]

I'm trying to write a query to return an aggregate collection of the data like this. The 2 assignments tables are jsonb and the choices table is also jsonb

{
  label: "Shirt"
  assignments: [
    {
        default: true,
        assignment: 1
        values: [
            {
                label: "Size", 
                option: 8,
                choice_values: {
                   id: 8,
                   label: "Sizing",
                   choices: [{label: "Small", {label: "Medium"}]
                }
            }
        ]
    }
  ]
}

As you can see it contains both nested arrays and nested objects, this is the query I have so far.

SELECT *,
(SELECT json_agg(assignments.*) FROM assignments WHERE assignments.id=1) AS assigned,
(SELECT json_agg(options.*) FROM options WHERE options.id=8) AS options
FROM product_types
WHERE id=${req.body.id}

The problem is I can't figure out how to get the actual ID values based on the inner jsonb array data. As well as the above query doesn't nest the values but creates a separate array.

pumbo · Accepted Answer · 2017-11-28 09:59:22Z

1

Here it is: http://sqlfiddle.com/#!17/037b2b/1

SELECT jsonb_build_object(
  'label', product_types.label,
  'assignments', (SELECT jsonb_agg(jsonb_set(product_types_assignments, '{values}', a.a)) FROM (
    SELECT product_types_assignments, 
      (
        SELECT jsonb_agg(jsonb_set(assignments_assignments, '{choice_values}', to_jsonb(options.*)))
        FROM jsonb_array_elements(assignments.assignments) assignments_assignments
        JOIN options ON (assignments_assignments->>'option')::int = options.id
      ) as a
    FROM jsonb_array_elements (product_types.assignments) product_types_assignments
    JOIN assignments ON (product_types_assignments->>'assignment')::int = assignments.id
   ) a)
)
FROM product_types

answered Nov 28, 2017 at 9:59

pumbo

3,9162 gold badges29 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Nesting a Select and returning the nested data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related